SqlAlchemy 在保存前将 UTC DateTime 转换为本地时间 [英] SqlAlchemy converting UTC DateTime to local time before saving

问题描述

我有以下情况:- 带有字段的 Postgres 后端

I have the following situation: - Postgres backend with a field

timestamp without time zone

• 就在保存日期时间值之前，它看起来像:2014-09-29 06:00:00+00:00
• 然后我从数据库加载同一行，值为:2014-09-29 09:00:00

所以在数据库中存储的日期不再是 6AM .. 而是 9AM - 它在我的本地时区中转换.

So in the database the date stored is no longer 6AM .. but 9AM - it's converted in my local timezone.

我不明白发生了什么.为什么保存的日期会转换为本地的?

I don't understand what's happening. Why is the saved date converted to local ?

谢谢.

编辑

所以在@univerio 的回复之后，我尝试了一些东西:我通过执行

So after @univerio's reply I tried something: I removed the tzinfo from the date time by doing

.replace(tzinfo = None) 

现在日期已正确保存 - 它不会将其调整为当地时间.我不太明白为什么，所以我暂时将问题悬而未决，以防有人有解释.

And now the date is saved correctly - it doesn't adjust it to the local time. I don't quite understand why so I'll leave the question open for now in case someone has an explanation.

谢谢.

推荐答案

我怀疑正在发生的事情是您正确地存储了可感知的 datetime，但没有使用时区读取它，因为该列是 WITHOUT TIME ZONE.每个 PostgreSQL 连接都有一个关联的时区，默认为系统的时区，因此当您检索特定的 TIMESTAMP 时，它会作为系统时区中的一个简单的 datetime 返回.出于这个原因，我总是建议改为存储 TIMESTAMP WITH TIME ZONE.

What I suspect is happening is that you are storing aware datetimes correctly, but are not reading it back with a time zone because the column is WITHOUT TIME ZONE. Each PostgreSQL connection has an associated time zone that defaults to the system's time zone, so when you retrieve a particular TIMESTAMP it gets returned as a naïve datetime in the system's time zone. For this reason, I always recommend storing TIMESTAMP WITH TIME ZONE instead.

如果要将 SQLAlchemy 中连接的时区更改为 UTC，请在创建引擎时执行以下操作:

If you want to change the time zone of the connection in SQLAlchemy to UTC, do the following when you create the engine:

engine = create_engine("...", connect_args={"options": "-c timezone=utc"})

这应该让您以 UTC 格式将值读回为简单的 datetime.

This should make you read the value back as a naïve datetime in UTC.

@Peter 文档没有明确说明如何执行此操作；我不得不阅读几个不同的文档并连接点:

@Peter The documentation does not make it obvious how to do this; I had to read several different docs and connect the dots:

1. 有关 connect_args 的 SQLAlchemy 文档 允许您将参数直接传递给 DBAPI connect()
2. 关于 connect 的 psycopg2 文档，它告诉你可以传递给 libpq 的额外参数
3. 关于 的 libpq 文档options 允许你在连接 libpq 时传递命令行选项的参数
4. 有关 的 PostgreSQL 文档-c 命令行开关，允许您修改配置设置
5. 最后，关于 的 PostgreSQL 客户端文档timezone 可以设置的客户端设置

这篇关于SqlAlchemy 在保存前将 UTC DateTime 转换为本地时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！