PostgreSQL中CURRENT_TIMESTAMP与CURRENT_DATE日期差异及上海时区日期获取

上海当前时间为2024-03-19 00:43

show timezone
+----------+
| TimeZone |
|----------|
| GMT      |
+----------+

SELECT CURRENT_DATE  AT TIME ZONE 'Asia/Shanghai', CURRENT_TIME  AT TIME ZONE 'Asia/Shanghai', CURRENT_TIMESTAMP AT TIME ZONE 'Asia/Shanghai';
 
+---------------------+-----------------------+---------------------------+
| timezone            | timezone              | timezone                  |
|---------------------+-----------------------+---------------------------|
| 2024-03-18 08:00:00 | 00:43:52.916370+08:00 | 2024-03-19 00:43:52.91637 |
+---------------------+-----------------------+---------------------------+

1. 为何PostgreSQL中CURRENT_TIMESTAMP的日期与CURRENT_DATE不一致？ #

核心原因是两者的计算逻辑顺序不同：

• CURRENT_DATE直接返回数据库当前时区（GMT）的本地日期，执行查询时GMT时区的日期为2024-03-18，通过AT TIME ZONE 'Asia/Shanghai'转换后，得到的是GMT日期对应的上海时区起始时间（2024-03-18 08:00:00）。
• CURRENT_TIMESTAMP返回的是带时区的完整当前时间（等价于GMT/UTC时间），执行AT TIME ZONE 'Asia/Shanghai'时，是把GMT的当前时间（2024-03-18 16:43:52左右）转换为上海时区的时间（GMT+8），也就是2024-03-19 00:43:52，因此日期为2024-03-19。

简单来说，CURRENT_DATE是先取本地时区日期再转时区，CURRENT_TIMESTAMP是先取带时区完整时间再转时区，计算顺序差异导致了日期结果不同。

2. 当时区设置为GMT时，如何获取上海的当前日期并得到结果'2024-03-19'？ #

推荐两种可靠的实现方式：

• 方法一：先转换时区再提取日期（最稳妥，自动适配时区规则）

SELECT (CURRENT_TIMESTAMP AT TIME ZONE 'Asia/Shanghai')::DATE;

该语句先将GMT时区的当前完整时间转换为上海时区时间，再提取日期部分，得到上海当地的日期'2024-03-19'。

• 方法二：基于UTC时间偏移计算

SELECT (CURRENT_TIMESTAMP + INTERVAL '8 hours')::DATE;

上海时区为UTC+8，通过给当前UTC时间加8小时后提取日期，也能得到目标结果，但方法一更适配夏令时等特殊时区场景。

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