如何在PostgreSQL中按DD/MM/YYYY HH24:MI:SS:MS格式存储timestamp？

核心原理 #

PostgreSQL 的 timestamp（或 timestamptz）类型不以字符串格式存储日期时间，而是将其转换为二进制的时间戳数值（本质是从某个时间纪元开始的微秒数）。你看到的 Oct 15 22:42:43 只是客户端工具（如 psql、pgAdmin）默认的显示格式，和实际存储内容完全无关。

解决步骤 #

1. 确认列类型（时区适配） #

如果你的业务需要保证时区一致性，建议使用 timestamptz（带时区的时间戳）而非普通的 timestamp。创建表时可按如下方式定义：

CREATE TABLE your_table (
    id SERIAL PRIMARY KEY,
    event_time TIMESTAMPTZ NOT NULL
);

timestamptz 会自动将时间转换为 UTC 存储，并根据客户端时区自动适配显示，避免跨时区场景下的时间混乱。

2. 控制查询时的显示格式 #

如果希望查询结果固定显示为 DD/MM/YYYY HH24:MI:SS:MS 格式，可按以下方式处理：

方式一：会话级临时设置

在当前数据库会话中执行命令，临时修改显示规则：

SET datestyle = 'DMY, ISO';

该设置仅对当前会话有效，关闭连接后失效。如果需要显示毫秒，最灵活的方式是用 to_char 格式化输出（虽返回字符串，但仅用于显示场景）：

SELECT to_char(event_time, 'DD/MM/YYYY HH24:MI:SS.MS') AS formatted_time FROM your_table;

注：标准时间格式中毫秒用 . 分隔，若输入习惯用 :，可在格式化时替换，但存储时无需特殊处理。

方式二：全局永久设置

修改 PostgreSQL 配置文件 postgresql.conf，找到 datestyle 参数并设置：

datestyle = 'DMY, ISO'

保存后重启数据库服务，所有新会话都会默认使用该格式显示日期时间。

方式三：客户端工具自定义设置

• psql：在用户目录下创建 .psqlrc 文件，添加 SET datestyle = 'DMY, ISO';，每次启动 psql 都会自动加载该配置。
• pgAdmin：在工具「选项」-「显示」中找到日期时间格式设置，直接自定义为 DD/MM/YYYY HH24:MI:SS.MS。

3. 插入数据时的格式处理 #

插入 15/10/2022 22:42:43:682 这类格式的字符串时，建议用 to_timestamp 明确转换，避免因系统默认格式导致的解析错误：

INSERT INTO your_table (event_time) 
VALUES (to_timestamp('15/10/2022 22:42:43.682', 'DD/MM/YYYY HH24:MI:SS.MS'));

这里将输入的 : 替换为 .，符合 PostgreSQL 的时间戳解析规则，确保毫秒数被正确识别并存储。

总结 #

无需纠结“以特定格式存储日期时间”，timestamp/timestamptz 的存储是二进制的，只需做好两点：

• 插入时用 to_timestamp 确保格式解析正确
• 查询时通过会话设置、全局配置或 to_char 控制显示格式

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