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

问题描述

我有以下情况：
- Postgres后端与字段

 没有时区的时间戳
  

• 在保存datetime值之前，它看起来像：2014-09-29 06 ：00：00 + 00：00


• 然后，我从db中加载相同的行，值为：2014-09-29 09:00:00

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

$ b $我不明白发生了什么事情。为什么保存的日期转换为本地？

谢谢。

修改 / p>

所以在@ univerio的回复之后，我尝试了一些事情：我从日期时间删除了tzinfo，做了

  .replace（tzinfo = None）
  

现在，日期保存正确 - 它不会调整到当地时间。我不太明白为什么我现在开始问题，以防有人有解释。

谢谢。

解决方案

我怀疑发生的是你正在正确地存储 datetime ，但是不能读回由于列是 WITHOUT TIME ZONE ，因此带有时区。每个PostgreSQL连接都有一个关联的时区，默认为系统的时区，因此当您检索到特定的 TIMESTAMP 时，它将作为一个天真的 datetime 在系统的时区。因此，我总是建议存储 TIMESTAMP WITH TIME ZONE 。

如果要更改时区的SQLAlchemy到UTC的连接，在创建引擎时执行以下操作：

  engine = create_engine（... ，connect_args = {options：-c timezone = utc}）
  

这应该让你在UTC中读取值为天真的 datetime 。

编辑：@Peter文档并不明显如何做到这一点我不得不阅读几个不同的文档并连接点：

1. SQLAlchemy文档关于 connect_args ，让您通过直接到DBAPI的参数 connect（）


2. psycopg2文档在 connect ，它告诉你可以传递给libpq的额外参数


3. libpq文档在 选项 参数，允许您在与libpq


4. 连接PostgreSQL文档时传递命令行选项关于 -c 命令行开关，允许您修改配置设置


5. ，关于 <$ c $的PostgreSQL客户端文档c>时区 您可以设置的客户端设置

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

timestamp without time zone

• Right before saving the datetime value, it looks like : 2014-09-29 06:00:00+00:00
• I then load the same row from the db and the value is : 2014-09-29 09:00:00

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 ?

Thanks.

Edit

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.

Thanks.

解决方案

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.

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"})

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

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

1. the SQLAlchemy documentation about connect_args that allows you to pass arguments directly to the DBAPI connect()
2. the psycopg2 documentation on connect, which tells you about the extra parameters you can pass to libpq
3. the libpq documentation on the options parameter that allows you to pass command-line options when connecting with libpq
4. the the PostgreSQL documentation about the -c command-line switch that allows you to modify config settings
5. finally, the PostgreSQL client documentation about the timezone client setting that you can set

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