Aurora PostgreSQL中如何从时间戳提取日期？

问题现象 #

在普通PostgreSQL中正常的时间戳转日期操作，在Aurora PostgreSQL中出现异常：

• 使用to_char提取日期时，结果与原时间戳的日期不符：

  SELECT to_char(datetime, 'YYYY-MM-DD') as date, datetime from call_schedule
  

  返回结果：

  "date": "2022-10-07",
  "datetime": "2022-10-06T20:00:00.000Z"
  

• 使用date()函数时，返回结果仍带时间信息，且日期偏移：

  SELECT date(datetime) as date, datetime from call_schedule
  

  返回结果：

  "date": "2022-10-03T18:30:00.000Z",
  "datetime": "2022-10-04T07:35:00.000Z"
  

问题原因 #

核心是时区不匹配：Aurora PostgreSQL的会话时区或数据库时区与预期时区（如UTC）不一致，导致存储的UTC时间戳在转换时被自动做了时区偏移计算。date()函数返回的带时区日期对象，序列化后会保留时间部分。

解决方法 #

1. 转换时明确指定时区 #

强制使用UTC时区处理，确保日期计算准确：

• to_char指定时区写法：

  SELECT to_char(datetime AT TIME ZONE 'UTC', 'YYYY-MM-DD') as date, datetime from call_schedule
  

• 转纯DATE类型写法：

  SELECT (datetime AT TIME ZONE 'UTC')::DATE as date, datetime from call_schedule
  

  此写法返回的date字段为纯日期类型，无时间信息。

2. 修改会话时区 #

临时调整当前会话时区为UTC，后续查询自动基于UTC计算：

SET TIME ZONE 'UTC';

执行后再运行原查询即可得到预期结果。

3. 调整数据库默认时区（需权限） #

若需全局生效，可修改Aurora数据库的默认时区：

ALTER DATABASE your_database_name SET TIME ZONE 'UTC';

修改后需重新连接会话生效。

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