1. 先分清pg里的日期家族date、timestamp、timestamptz分别长什么样先说个场景。昨天运维群里有人发了一张截图select * from orders查出来的时间字段是2024-05-16 14:30:45.12345608而另一个库查出来是May 16, 2024 02:30:45 PM。俩人都说自己是PostgreSQL但显示结果完全不一样。有人开始怀疑驱动有问题有人怀疑配置错了。其实谁都没错——日期在pg里是一套类型系统 会话参数共同决定输出的只有在搞清楚这几个时间类型之后日期转字符串这件事才不会翻车。1.1 五种时间类型各自默认的长相不同PostgreSQL和MySQL一个很大的区别就是时间相关的类型分得很细不能拿MySQL的datetime、timestamp一套到底。pg里日常打交道的类型有这么几种类型存储内容默认输出示例date年-月-日2024-05-16time时:分:秒(带小数)14:30:45.123456timestamp年-月-日 时:分:秒无时区2024-05-16 14:30:45.123456timestamptz年-月-日 时:分:秒带时区语义2024-05-16 14:30:45.12345608interval时间间隔1 day 02:30:00注意timestamp和timestamptz的区别这是很多人栽跟头的地方。timestamp不考虑时区你存进去2024-05-16 14:30:45就是14点30分45秒而timestamptz在内部其实存的是UTC时刻只是在你查询时根据当前会话时区换算成对应的时间显示出来。这就直接导致了一个结果同样的数据你在不同时区的客户端看到的字符串不一样。还有一个隐藏细节pg的timestamp、timestamptz默认精度到微秒6位小数。这也是为什么直接select出来经常带一长串123456的原因。如果你想输出只到秒就必须做一层格式化转换。1.2 查看到的字符串是会话加工过的DateStyle和timezone在背后起作用很多初学者会问我select一个 date 字段它返回的不是本来就是字符串吗严格说不是。数据库返回的是二进制值只不过客户端驱动在拿到结果后调用了pg的输出函数把它渲染成了文本。渲染成什么样由两个会话参数决定DateStyle控制日期的显示格式常见值是ISO, MDY。ISO下显示2024-05-16SQL风格下可能显示05/16/2024German风格是2024.05.16。timezone控制带时区类型的时区换算。你SET timezone TO UTC之后同一行数据查到的时间立刻变了。你可以直接跑一下SHOW datestyle; SHOW timezone;所以当你的同事和你的查询结果长得不一样第一反应不要怀疑数据错了先看这两个会话参数。理解了这一点你就明白为什么日期转字符串不能靠select默认输出而必须用可控的转换函数。2. to_char()最正统的日期转字符串函数所有格式控制都在这把日期转成指定格式的字符串PostgreSQL官方给的标准答案就是to_char()。它负责把时间类型按你给的模板输出成字符串是今天整篇文章的主角。2.1 基本语法、返回类型和第一个例子这个函数的使用方式非常直观to_char(要转的日期或时间戳, 格式模板)返回类型是text。看两个最简单的例子SELECT to_char(current_date, YYYY-MM-DD); -- 结果2024-05-16 SELECT to_char(current_timestamp, YYYY-MM-DD HH24:MI:SS); -- 结果2024-05-16 14:30:45第一个是把 date 类型转成年-月-日字符串第二个是把 timestamp 转成年-月-日 时:分:秒字符串。HH24表示24小时制这是最常用的写法。如果你不写HH24而是写HH那就是12小时制下午2点会显示成02如果不配合AM/PM很容易被读成凌晨2点这个坑我下面专门说。2.2 高频模板元素对照表to_char的格式模板元素非常多但实际开发中反复用到的就下面这些。我把它们整理成一张表建议收藏模板元素含义示例输出YYYY四位数字年份2024YY两位数字年份24MM两位数字月份(01-12)05DD两位数字日(01-31)16DDD一年中的第几天137HH2424小时制小时(00-23)14HH12小时制小时(01-12)02MI分钟(00-59)30SS秒(00-59)45MS毫秒(000-999)123US微秒(000000-999999)123456AM/PM上午/下午标记PMMon英文月份缩写(首字母大写)MayMonth英文月份全称(首字母大写)MayTZ时区英文缩写CSTOF与UTC的数值偏移08DOW星期几(0-6周日为0)4ISODOW星期几(1-7周一为1)4WW一年中的第几周20Q季度2一个完整的组合示例SELECT to_char( current_timestamp, YYYY-MM-DD HH24:MI:SS.MS ); -- 结果2024-05-16 14:30:45.123这里.MS是毫秒如果需要一个6位的微秒尾巴就是.US。实际报表场景里到秒就够了微秒尾巴对人是数字噪音。2.3 修饰前缀FM和FX去掉空格、严格匹配to_char模板里还有两个修饰词知道的人不少用对的人不多FM和FX。FMFill Mode的作用是去掉输出结果里的前导空格和补零。比如不加FM时Month这种输出会把月份全称填充到固定宽度。5月是May因为只有3个字母pg会把剩下的宽度用空格补齐输出成May后面带6个空格。再加FM之后就干净了SELECT to_char(date 2024-05-16, Month DD, YYYY); -- 结果May 16, 2024 SELECT to_char(date 2024-05-16, FMMonth DD, YYYY); -- 结果May 16, 2024FXFormat eXact则通常用在to_date、to_timestamp这种字符串解析场景要求输入字符串和模板严格匹配不能多空格、不能多零。to_char里用得少但如果你的模板很严格也可以理解成关闭灵活匹配。提示FM也影响YYYY-MM-DD这类数字输出比如FMDD在日为5号时输出5而不是05。如果你要固定两位数的数字日期就别加FM否则月份、日期前面零会被去掉容易破坏文件命名这类场景的位数要求。3. 除了to_char还有三种隐式转换路线强转、拼接、JSON序列化to_char是受控转换但实际开发里还有三天天都在碰的自动转换它们不写to_char也能把日期变成字符串只是结果非常不可控。3.1 直接::text强转得到的是数据库默认长相PostgreSQL是强类型数据库但支持显式类型转换。把时间类型直接转成text是很多人第一个学会的做法SELECT current_timestamp::text; -- 结果2024-05-16 14:30:45.12345608还有一种等价写法是SELECT CAST(current_timestamp AS text)。cast(current_timestamp as varchar)也行。这种写法得到的字符串特点就是完全按默认输出渲染带微秒、带时区偏移。如果它只是给你自己调试用没问题但如果你想拿这个字符串去拼接口返回、拼报表、做文件命名就等着挨骂吧——前端拿到2024-05-16 14:30:45.12345608大概率要再处理才能展示。date类型强转倒是还好SELECT current_date::text; -- 结果2024-05-16因为 date 本身就只有年月日默认输出就是干净的ISO格式。绝大多数需要转换的麻烦都出在 timestamp 和 timestamptz 上。3.2 字符串拼接和||操作符里的自动转换pg里用||拼字符串或者调用concat()遇到时间类型会自动调用它的输出函数转成文本。这很省事但同样要承担微秒尾巴和时区偏移的风险SELECT 下单时间 || current_timestamp; -- 结果下单时间2024-05-16 14:30:45.12345608 SELECT concat(下单时间, current_timestamp); -- 结果下单时间2024-05-16 14:30:45.12345608很多人在写操作日志、审计记录时习惯直接拼日期结果日志文件里全是带微秒和时区偏移的时间戳。看多了眼睛真的疼。如果你只需要到秒写日志前先to_char(... , YYYY-MM-DD HH24:MI:SS)再拼接日志可就清爽多了。3.3 json_build_object和row_to_json接口开发最常用的隐形转换如果你用json_build_object构建JSON返回给前端日期字段也会被悄悄转成字符串SELECT json_build_object(create_time, current_timestamp); -- 结果{create_time : 2024-05-16 14:30:45.12345608}row_to_json同理。这里的转换规则也是走数据库默认输出所以也是一样的毛病带微秒、带时区。前端如果想展示2024-05-16 14:30:45还得自己切字符串。所以接口返回的时间字段我建议在SQL里就格式化好SELECT json_build_object( create_time, to_char(current_timestamp, YYYY-MM-DD HH24:MI:SS) ); -- 结果{create_time : 2024-05-16 14:30:45}这样前端拿来直接能用不需要二次格式化。这个习惯在涉及JSON函数时特别重要能帮你省掉无数个线上问题。4. 格式模板深水区大小写、文字转义、12小时制和时区跨天to_char语法很简单真正折磨人的是模板里的各种约定。我在生产环境排查过不少日期显示问题几乎都是下面这几类。4.1 MM和MI月份和分钟混用是最常见的错我见过太多人在to_char里写出HH24:MM:SS的模板自以为格式是时:分:秒。结果呢MM在pg的模板里是月份不是分钟。分钟是MI。SELECT to_char(current_timestamp, HH24:MM:SS); -- 错误示例MM是月份会输出类似 14:05:45 SELECT to_char(current_timestamp, HH24:MI:SS); -- 正确示例MI才是分钟输出 14:30:45这个坑尤其阴险因为14:05:45这种输出看起来像一个时间甚至不会报错很容易被误认为数据就是05分。虽然5月是5月但如果你在每年除5月以外的月份用它分钟位置就会显示奇怪的数字恰好是5月的时候看起来倒是对上了排查起来异常折磨人。区分方法很简单MM是Month的缩写记忆MI是Minutes的缩写记忆。模板里要分钟闭眼写MI。4.2 模板里的普通文字必须用双引号包起来to_char的模板是字母即模式。模板里的Y、M、D、H这些字母会被识别为格式元素。如果你希望输出中文说明文字比如2024年05月16日直接把汉字丢进模板pg对无法识别的字符处理会很不可控甚至直接报错。正确做法是用双引号把文字包起来-- 错误姿势至少是不推荐 SELECT to_char(current_date, YYYY年MM月DD日); -- 正确姿势 SELECT to_char(current_date, YYYY年MM月DD日); -- 结果2024年05月16日同样地如果你想输出第1季度这类文本模板里写成第Q季度才对。注意这里的双引号不是SQL里的字符串双引号而是模板内的转义符号。整个格式模板本身还要用单引号包裹因为它是一个SQL字符串字面量。这个细节在MySQL转过来的同学身上尤其容易出现MySQL的DATE_FORMAT对文字比较宽容但pg的to_char严格得多。4.3 timestamptz的时区坑同一个时刻两个时区显示不同日期timestamptz转字符串结果取决于当前会话的timezone参数而不是你存进去时自以为的那个本地时间。我举个例子SET timezone TO Asia/Shanghai; SELECT to_char(TIMESTAMPTZ 2024-05-16 00:30:0008, YYYY-MM-DD HH24:MI:SS); -- 结果2024-05-16 00:30:00 SET timezone TO UTC; SELECT to_char(TIMESTAMPTZ 2024-05-16 00:30:0008, YYYY-MM-DD HH24:MI:SS); -- 结果2024-05-15 16:30:00看到了吧同一个时刻在UTC时区下转换出来的字符串日期是前一天。这不是数据错了是会话时区变了。所以如果你用timestamptz存时间做报表分组时一定要先统一时区。最稳妥的办法是在连接建立后执行SET timezone TO Asia/Shanghai或者干脆在SQL里对时间做时区指定SELECT to_char( (TIMESTAMPTZ 2024-05-16 00:30:0008) AT TIME ZONE Asia/Shanghai, YYYY-MM-DD HH24:MI:SS );把timestamptz先转成指定时区的timestamp再交给to_char输出结果就不受会话参数干扰了。5. 实测输出对比同样一个时间五种写法的结果完全不同纸上谈兵半天不如直接跑一遍看结果。下面我用同一个2024-05-16 14:30:45的timestamptz分别用五种写法转成字符串对比一下它们的输出。5.1 一张表格看清差异写法输出结果适用场景to_char(now(), YYYY-MM-DD HH24:MI:SS)2024-05-16 14:30:45报表、日志、接口展示推荐now()::text2024-05-16 14:30:45.12345608临时调试别用于生产下单时间: || now()下单时间:2024-05-16 14:30:45.12345608日志拼接建议先格式化json_build_object(t, now()){t:2024-05-16 14:30:45.12345608}接口返回JSON建议先格式化to_char(now(), YYYYMMDD)20240516文件命名、日期维度键光看这一张表其实就能明白除了to_char其他写法都带着微秒和时区尾巴。to_char的价值不只是转换更重要的是裁剪——把不需要的精度、时区、默认渲染全部按你的需求去掉。5.2 别在WHERE条件里套to_char百万行查询的教训这个坑我曾经在一个千万级订单表上踩过。当时一个同事写了个统计SELECT * FROM orders WHERE to_char(created_at, YYYY-MM-DD) 2024-05-16;逻辑上没毛病但它犯了SQL性能调优的大忌在查询条件里对索引列套函数。created_at上明明建了索引但to_char(created_at, ...)导致索引失效pg只能全表扫应用直接超时。正确写法是范围比较SELECT * FROM orders WHERE created_at 2024-05-16::timestamp AND created_at 2024-05-17::timestamp;如果你的created_at是timestamptz建议配合明确时区WHERE created_at (TEXT 2024-05-16 00:00:0008)::timestamptz AND created_at (TEXT 2024-05-17 00:00:0008)::timestamptz这样SQL可以稳稳用到created_at上的普通B-tree索引。记住一句话日期转字符串是为了输出给人看不是为了让数据库去比大小。比大小直接用日期类型数据库才开心。5.3 什么时候才需要转字符串判断原则结合我这么多年的使用经验我给自己定了一个判断原则对外展示、拼接、导出、JSON返回用to_char主动控制格式。内部过滤、分组、范围查询保持日期类型本身别画蛇添足转字符串。仅仅为了肉眼调试怎么方便怎么来::text没问题。还有一个容易被忽略的场景日期字段转字符串后做ORDER BY。字符串排序和日期排序在某些场景下结果完全不同。比如2024-9和2024-10按字符串排2024-9会排在2024-10后面因为字符9比1大。所以千万不要把日期转成字符串再排序。6. 真实业务场景报表分组、CSV导出和接口返回值上面讲的都是怎么做下面具体看看几个每天都在遇到的业务场景该怎么落地。6.1 按天统计的分组key用to_char最稳做报表的时候最常见的需求就是按天统计订单量、销售额。很多人会用date_trunc(day, created_at)做分组这没问题但输出的分组key还是带时间的 timestamp比如2024-05-16 00:00:00在图表里不但不好看还可能被前端当成当天零点处理。我更推荐直接生成日期字符串SELECT to_char(created_at, YYYY-MM-DD) AS day, count(*) AS order_cnt, sum(amount) AS total_amount FROM orders WHERE created_at 2024-05-01::timestamp AND created_at 2024-06-01::timestamp GROUP BY to_char(created_at, YYYY-MM-DD) ORDER BY day;这里有几个细节GROUP BY里要写完整表达式to_char(created_at, YYYY-MM-DD)不能直接写别名GROUP BY dayPG在这里不接受别名。ORDER BY day是可以的因为排序发生在分组之后能识别SELECT别名。分组key是YYYY-MM-DD固定10位字典序和日期序完全一致所以ORDER BY day按字符串排也不会错。如果你要按小时统计同理用to_char(created_at, YYYY-MM-DD HH24:00)作为key比date_trunc(hour, ...)可读性好太多而且对前端特别友好。6.2 导出CSV/Excel时怎么统一日期格式用COPY导出数据到CSV是PostgreSQL很常见的高效方式。但如果直接COPY orders TO ...时间字段会按默认格式输出又带微秒又带时区。Excel打开之后那一列长着一串2024-05-16 14:30:45.12345608十个人看到十个人想骂。解决办法是在COPY前先把字段格式化好COPY ( SELECT order_no, to_char(created_at, YYYY-MM-DD HH24:MI:SS) AS create_time, amount FROM orders WHERE created_at 2024-05-01::timestamp ) TO /tmp/orders_202405.csv WITH (FORMAT CSV, HEADER true);这里我用了一个子查询把to_char的结果作为create_time字段导出。CSV里这一列就是干净的2024-05-16 14:30:45Excel可以直接当文本读不会再莫名变成一串数字或者科技英文时间。提示COPY ... TO的路径是服务端能访问的路径不是客户端路径。很多新手在这里卡半天以为文件没生成其实是写到数据库服务器上去了。6.3 接口返回给前端用JSON函数自动转别手动拼如果你的后端语言直接在SQL里拼JSON字符串我强烈建议改成json_build_object或者row_to_json。这不仅安全而且日期格式化可以顺带做掉SELECT json_build_object( order_no, order_no, create_time, to_char(created_at, YYYY-MM-DD HH24:MI:SS), date, to_char(created_at, YYYY-MM-DD) ) FROM orders WHERE created_at 2024-05-01::timestamp LIMIT 2;输出大概是{order_no: 20240501001, create_time: 2024-05-01 10:30:45, date: 2024-05-01}前端拿到create_time之后无论是要展示还是要做时间选择器都能直接消费。如果你再配合FM修饰去掉可能出现的补零输出会更紧凑。7. 报错排查和MySQL迁移对照最后聊点实操中一定会碰到的报错和习惯迁移问题。从MySQL过来的同学在pg里做日期字符串转换时总有几个适应点。7.1 常见报错信息识别报错一字符串转日期失败invalid input syntax for type date: 2024/05/16 LINE 1: SELECT 2024/05/16::date;这个错通常是因为输入的字符串格式和当前DateStyle不匹配。默认ISO, MDY下2024/05/16是合法输入吗其实斜杠风格在部分DateStyle下可以被接受但在严格ISO模式下就会报错。处理方式是指定解析模板SELECT to_date(2024/05/16, YYYY/MM/DD);报错二月份范围越界date/time field value out of range: 2024-13-01输入了13月或者2月30号。这种就是数据质量问题检查源头就好。报错三to_char模板里有无法识别的字符multiple I at or near I这类报错一般和模板里混入了I开头的模式有关或者写错了模板元素。解决方案就是检查模板里的字母普通文字用双引号包住。7.2 输出不符合预期时的三层排查顺序如果你发现日期字符串输出跟预想不一样别着急怀疑函数没用对按下面顺序查查DateStyleSHOW datestyle;如果它不是ISO, MDY所有默认输出都和你直觉不同。查timezoneSHOW timezone;如果会话时区不是你预期时区timestamptz转出来的时间会整体偏移甚至跨天。查模板上面两层都没问题再盯to_char的模板。重点关注MM和MI有没有混用HH24有没有写成HH文字有没有用双引号转义。按照这个顺序排查我踩过的蹊跷案例基本30秒内能定位。7.3 MySQL用户迁移需要改的三个习惯第一DATE_FORMAT对应的是to_char不是别的什么format函数。语法上MySQL是内容在前、格式在后DATE_FORMAT(now(), %Y-%m-%d)pg反过来格式串在后to_char(now(), YYYY-MM-DD)而且格式符不是%Y这种%开头写法而是直接写YYYY。第二MySQL里STR_TO_DATE是字符串解析成日期pg里对应的是to_date和to_timestamp。区别在于to_timestamp返回的是带时区的 timestampto_date返回的是 date。-- MySQL SELECT STR_TO_DATE(2024-05-16, %Y-%m-%d); -- pg SELECT to_date(2024-05-16, YYYY-MM-DD); SELECT to_timestamp(2024-05-16 14:30:45, YYYY-MM-DD HH24:MI:SS);第三MySQL的字符串和日期经常隐式转换比如用字符串2024-05-16和 date 列比较会自己转pg相对严格不会随便把一个看起来像日期的字符串当成日期去比较。所以从MySQL迁移过来遇到类型不匹配的报错不要慌显式::date或to_date转一下就好。最后分享一个我实际使用中的小习惯我始终把to_char(current_timestamp, YYYY-MM-DD HH24:MI:SS)当作默认的给人看的时间格式业务接口里如果需要日期字符串也统一用这个模板避免不同接口出现两种格式。只有在涉及文件名、系统编号这类需要紧凑格式的时候才用YYYYMMDD。另外每次排查完问题我都会顺手SHOW datestyle;看一眼防止是会话参数在哪一步被改掉。毕竟日期转字符串本身不难难的是搞清楚数据库在什么上下文下帮你做了输出以及你想要的到底是哪一种字符串。这几件事想透了pg的日期转换就不会再成为你的坑。