PostgreSQL可接收dd/MM/yyyy, HH:mm:ss格式的日期时间类型选择

1. 数据类型选择说明 #

PostgreSQL本身没有DATETIME、TIMEDATE这类数据类型，这是MySQL的语法，所以你执行加字段语句时报错属于正常情况。
存储完整日期时间信息，正确的类型选择为：

• 不需要存储时区信息选TIMESTAMP
• 跨时区场景推荐选TIMESTAMPTZ（带时区的TIMESTAMP）

正确的加字段SQL为：

-- 不带时区
ALTER TABLE user_info_2 ADD date_col1 TIMESTAMP;
-- 带时区
ALTER TABLE user_info_2 ADD date_col1 TIMESTAMPTZ;

2. 写入报错的核心原因 #

你遇到的写入报错不是数据类型的问题，是前端传入的时间格式不被PostgreSQL默认识别：new Date().toLocaleString("en-GB")输出的是DD/MM/YYYY, HH:MM:SS格式的字符串，PostgreSQL默认无法解析这种带逗号、日月年顺序的时间字符串。

3. 解决方案 #

方案一（优先推荐，根源规避格式问题） #

修改前端代码，直接输出ISO 8601标准格式的时间字符串，PostgreSQL可以直接识别该格式，无需额外处理，还能避免时区转换出错：

const dateTime = new Date().toISOString()
// 输出格式示例：2021-10-24T14:37:49.000Z

方案二（必须保留现有前端输出格式时使用） #

在写入数据库时做格式转换，有两种实现方式：

• 服务端先把DD/MM/YYYY, HH:MM:SS格式的字符串转成PostgreSQL支持的格式再写入
• 直接在SQL中用TO_TIMESTAMP函数做格式转换，示例：

INSERT INTO user_info_2 (date_col1) 
VALUES (TO_TIMESTAMP('24/10/2021, 14:37:49', 'DD/MM/YYYY, HH24:MI:SS'));

内容的提问来源于stack exchange，提问作者Brute