PostgreSQL/Django如何不修改时间值仅为timestamp加指定时区偏移

核心原理 #

你要实现的效果等价于Python的datetime.replace(tzinfo=目标时区)，PostgreSQL中有直接对应的操作逻辑，只是你之前对AT TIME ZONE的语义和timestamptz的显示规则有误解：

• PostgreSQL中对**无时区的timestamp（naive时间）**执行AT TIME ZONE '目标时区'，作用就是将这个naive时间视作目标时区的本地时间，转换为对应的timestamptz类型，完全符合你的需求。
• 你之前看到输出为2021-07-26 04:00:00+00，只是因为你的Django配置TIME_ZONE为UTC，数据库会话默认以UTC时区显示timestamptz值，实际存储的时间点完全正确。

具体实现方案 #

1. 原生PostgreSQL写法 #

修改你的SQL逻辑，在计算出naive timestamp后，追加AT TIME ZONE绑定目标时区即可：

TO_TIMESTAMP(
    (FLOOR((EXTRACT(EPOCH FROM "{gmdts_table}"."date_key" AT TIME ZONE %s) + %s) / %s)::INTEGER * %s) - %s
)::TIMESTAMP AT TIME ZONE %s

params参数中额外追加你要绑定的calculated_time_zone即可，得到的timestamptz就是正确的时间点。

如果你需要直接在数据库侧输出为2021-07-26 00:00:00-04:00格式的字符串，可以调整SQL为：

TO_CHAR(
    (TO_TIMESTAMP(/* 你的计算逻辑 */)::TIMESTAMP AT TIME ZONE %s) AT TIME ZONE %s,
    'YYYY-MM-DD HH24:MI:SS TZH:TZM'
)

params中传入两次目标时区即可。

2. Django适配写法 #

方式1：直接修改RawSQL

调整你现有的annotate逻辑，补充时区绑定参数：

return queryset.annotate(
    server_time=RawSQL(
        f'''
        SELECT TO_TIMESTAMP((FLOOR((EXTRACT(EPOCH FROM "{gmdts_table}"."date_key" AT TIME ZONE %s) + %s) / %s)::INTEGER * %s) - %s)::TIMESTAMP AT TIME ZONE %s
        ''',
        params=[
            str(requested_time_zone), 
            bucket_offset, 
            time_interval, 
            time_interval, 
            bucket_offset,
            str(calculated_time_zone) # 你要绑定的目标时区
        ]
    )
)

方式2：用Django Func封装（避免RawSQL）

Django 3.2+可以通过自定义Func表达式实现更优雅的写法：

from django.db.models import Func, DateTimeField

class BindTimezone(Func):
    function = 'AT TIME ZONE'
    template = "%(expressions)s %(function)s '%(timezone)s'"
    output_field = DateTimeField()

# 用法
queryset.annotate(
    # 先计算得到naive的时间戳
    naive_time=RawSQL(/* 你原有的计算逻辑，最后返回::TIMESTAMP */),
    # 绑定目标时区
    server_time=BindTimezone('naive_time', timezone=str(calculated_time_zone))
)

结果验证 #

你从Django ORM中拿到server_time后，只需调用时区转换方法即可得到目标显示格式：

from django.utils.timezone import localtime
import pytz

target_tz = pytz.timezone('America/New_York')
for obj in queryset:
    # 转换为目标时区的datetime对象，输出即为2021-07-26 00:00:00-04:00
    target_time = localtime(obj.server_time, target_tz)

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