FastAPI+SQLAlchemy连接PostgreSQL时区转换异常问题排查

技术栈：FastAPI+SQLAlchemy+PostgreSQL 12

模型定义：

class MyModel(...):
    id = sa.Column(sa.Integer, primary_key=True)
    creator_id = sa.Column(sa.String, nullable=True)
    created_at = sa.Column(sa.DateTime(timezone=True), default=utcnow)
    updated_at = sa.Column(sa.DateTime(timezone=True), default=utcnow, onupdate=utcnow)

数据库中存储带时区的数据，预期逻辑为：数据以UTC存储，会话中按会话或服务器时区返回。使用psql连接时，时间按服务器时区（Etc/GMT+3）返回，符合预期；但用create_async_engine连接SQLAlchemy时，获取到的始终是UTC时间，未设置额外时区参数。

测试代码：

server_timezone = await db.execute("SELECT current_setting('TIMEZONE');")
logger.info(f"server TZ {server_timezone.scalars().first()}")
value = await db.execute("select created_at from my_table limit 1;")
logger.info(value.scalars().first())
import time
logger.info(f"My TZ {time.tzname}")

测试输出：

server TZ Etc/GMT+3
2022-07-26 08:42:49.045520+00:00
My TZ ('+05', '+05')

已尝试修改PostgreSQL时区设置及查找SQLAlchemy连接选项但无效，请问问题原因可能在哪里？是否受环境变量影响？

问题原因及解决思路 #

核心原因 #

SQLAlchemy和psql的时间处理逻辑本质不同：

• psql会直接根据数据库会话时区，把存储的UTC时间转换为对应时区的字符串返回给客户端。
• 而SQLAlchemy的DateTime(timezone=True)类型处理器，会直接读取PostgreSQL底层存储的UTC时间戳，封装为带UTC时区信息的Python datetime对象，不会自动应用数据库会话时区做转换。

具体影响因素 #

• SQLAlchemy类型解析逻辑：不管数据库会话时区是什么，SQLAlchemy都会将带时区的字段解析为带tzinfo的datetime对象，且始终保留UTC时区——因为PostgreSQL内部所有带时区的时间最终都是以UTC时间戳存储的，会话时区只是用于客户端显示转换。
• 环境变量的影响：你测试输出里的My TZ ('+05', '+05')是Python进程的本地时区，这只会影响Python自身的时间处理（比如datetime.now()），但不会改变SQLAlchemy从数据库读取时间的解析逻辑，和当前问题无关。
• PostgreSQL会话时区的局限性：你查询到的server TZ Etc/GMT+3是数据库会话的时区设置，但这个设置只对psql这类直接返回字符串的客户端生效，SQLAlchemy的驱动会绕过这个转换，直接获取原始的UTC时间数据。

解决方法 #

1. 手动转换时区（推荐）
   从数据库拿到UTC时间对象后，手动转换为目标时区：

# Python 3.9+ 使用标准库zoneinfo
from zoneinfo import ZoneInfo

utc_time = value.scalars().first()
# 转换为服务器时区Etc/GMT+3
target_time = utc_time.astimezone(ZoneInfo("Etc/GMT+3"))
logger.info(target_time)

如果是Python 3.8及以下，可以使用pytz库替代zoneinfo。

2. 通过连接参数强制数据库会话时区
   在create_async_engine的连接URL中添加时区参数，让数据库返回指定时区的时间，SQLAlchemy会解析为带对应时区的datetime对象：

from sqlalchemy.ext.asyncio import create_async_engine

engine = create_async_engine(
    "postgresql+asyncpg://user:password@host:port/dbname?options=-c timezone=Etc/GMT+3"
)

3. SQL查询时直接转换
   在SQL语句中使用PostgreSQL的AT TIME ZONE函数，直接返回转换后的时间：

SELECT created_at AT TIME ZONE 'Etc/GMT+3' FROM my_table LIMIT 1;

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