PostgreSQL中to_char日期格式化异常及psql脚本相关疑问

问题场景 #

在PostgreSQL 13.6的psql脚本中遇到以下问题：

• 使用to_char格式化now()时分钟部分错误，但格式化表中created_at字段看似正常
• 疑问1：是否需要改用extract函数处理日期格式化？
• 疑问2：如何消除echo语句的自动换行？
• 疑问3：将脚本输出定向到文件时，echo语句内容缺失

原脚本：

\pset format unaligned
\pset fieldsep ','
! echo
! echo
! echo 'Report Name: XXX-23-001-v01'
! echo 'Report content: Reminders'
! echo
! echo 'report generated at: ';
select now();
select to_char(now(), 'DD-MM-YYYY  HH24:MM');
! echo
! echo
! echo 'data current as at: ';
select to_char(created_at, 'DD-MM-YYYY  HH24:MM')  from customer_events order by created_at DESC limit 1 ;

原输出：

Output format is unaligned.
Field separator is ",".
Report Name: XXX-23-001-v01
Report content: Reminders
report generated at:
2023-09-11 07:28:21.716321+01
11-09-2023  07:09
data current as at:
08-09-2023  23:09

核心问题解决：日期格式化错误 #

to_char的格式符用错了：MM代表月份，分钟对应的格式符是MI。你看到created_at格式化后看似正常，只是巧合——该记录的月份和分钟数字恰好都是09，实际上格式是错误的。

修正后的格式化语句：

select to_char(now(), 'DD-MM-YYYY  HH24:MI');
select to_char(created_at, 'DD-MM-YYYY  HH24:MI') from customer_events order by created_at DESC limit 1 ;

修正后now()的输出会正确显示分钟，比如11-09-2023 07:28。

其他疑问解答 #

1. 是否需要改用extract函数？ #

不需要。extract仅用于提取日期时间的单个部分（比如extract(minute from now())），如果你需要拼接成完整的日期字符串，to_char更简洁高效，只要格式符正确即可。

2. 消除echo语句的换行 #

不要用shell的! echo，改用psql内置的\echo命令，加上-n参数可以取消自动换行：

\echo -n 'report generated at: ';

这样后续的SQL查询结果会直接跟在这句话的同一行。

3. 输出定向到文件时echo内容缺失 #

原因是! echo是调用系统shell执行命令，它的输出默认直接打印到终端，不会被psql的输出定向捕获。解决方法是全程使用psql内置的\echo代替! echo，这样所有输出都会统一被定向到文件。

修正后的完整脚本 #

\pset format unaligned
\pset fieldsep ','
\echo
\echo
\echo 'Report Name: XXX-23-001-v01'
\echo 'Report content: Reminders'
\echo
\echo -n 'report generated at: ';
select now();
\echo
select to_char(now(), 'DD-MM-YYYY  HH24:MI');
\echo
\echo
\echo -n 'data current as at: ';
select to_char(created_at, 'DD-MM-YYYY  HH24:MI') from customer_events order by created_at DESC limit 1 ;

修正后的输出示例 #

Output format is unaligned.
Field separator is ",".

Report Name: XXX-23-001-v01
Report content: Reminders

report generated at: 2023-09-11 07:28:21.716321+01
11-09-2023  07:28

data current as at: 08-09-2023  23:59

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