SQLAlchemy DateTime 时区

问题描述

SQLAlchemy 的 DateTime 类型允许使用 timezone=True 参数将非原始日期时间对象保存到数据库中，并将其原样返回.有没有办法修改 SQLAlchemy 传入的 tzinfo 的时区，例如它可能是 UTC?我意识到我可以使用 default=datetime.datetime.utcnow;然而，这是一个天真的时间，即使我使用了 timezone=True ，它也会很乐意接受传递一个天真的基于本地时间的日期时间的人，因为它使本地或 UTC 时间变得不天真而没有一个基本时区来规范它.我已经尝试(使用 pytz)使日期时间对象不幼稚，但是当我将其保存到数据库时它回来时很天真.

请注意 datetime.datetime.utcnow 如何与 timezone=True 配合得这么好:

将 sqlalchemy 导入为 sa从 sqlalchemy.sql 导入选择导入日期时间元数据 = sa.MetaData('postgres://user:pass@machine/db')data_table = sa.Table('数据', 元数据,sa.Column('id', sa.types.Integer, primary_key=True),sa.Column('date', sa.types.DateTime(timezone=True), default=datetime.datetime.utcnow))metadata.create_all()引擎 = metadata.bindconn = engine.connect()结果 = conn.execute(data_table.insert().values(id=1))s = 选择([数据表])结果 = conn.execute(s)行 = result.fetchone()

<块引用>

(1, datetime.datetime(2009, 1, 6, 0, 9, 36, 891887))

行[1].utcoffset()

<块引用>

datetime.timedelta(-1, 64800) # 这是我的本地时间偏移！！

datetime.datetime.now(tz=pytz.timezone("US/Central"))

<块引用>

datetime.timedelta(-1, 64800)

datetime.datetime.now(tz=pytz.timezone("UTC"))

<块引用>

datetime.timedelta(0) #UTC

即使我将其更改为明确使用 UTC:

...

data_table = sa.Table('data', metadata,sa.Column('id', sa.types.Integer, primary_key=True),sa.Column('date', sa.types.DateTime(timezone=True), default=datetime.datetime.now(tz=pytz.timezone('UTC'))))行[1].utcoffset()

...

<块引用>

datetime.timedelta(-1, 64800) # 它没有使用我明确添加的时区

或者如果我放弃 timezone=True:

...

data_table = sa.Table('data', metadata,sa.Column('id', sa.types.Integer, primary_key=True),sa.Column('date', sa.types.DateTime(), default=datetime.datetime.now(tz=pytz.timezone('UTC'))))row[1].utcoffset() 为无

...

<块引用>

True # 这次它甚至没有将时区保存到数据库

解决方案

http://www.postgresql.org/docs/8.3/interactive/datatype-datetime.html#DATATYPE-TIMEZONES

<块引用>

所有可识别时区的日期和时间都以 UTC 格式在内部存储.在显示给客户端之前，它们会转换为 timezone 配置参数指定的区域中的本地时间.

用 postgresql 存储它的唯一方法是单独存储它.

SQLAlchemy's DateTime type allows for a timezone=True argument to save a non-naive datetime object to the database, and to return it as such. Is there any way to modify the timezone of the tzinfo that SQLAlchemy passes in so it could be, for instance, UTC? I realize that I could just use default=datetime.datetime.utcnow; however, this is a naive time that would happily accept someone passing in a naive localtime-based datetime, even if I used timezone=True with it, because it makes local or UTC time non-naive without having a base timezone to normalize it with. I have tried (using pytz) to make the datetime object non-naive, but when I save this to the DB it comes back as naive.

Note how datetime.datetime.utcnow does not work with timezone=True so well:

import sqlalchemy as sa
from sqlalchemy.sql import select
import datetime

metadata = sa.MetaData('postgres://user:pass@machine/db')

data_table = sa.Table('data', metadata,
    sa.Column('id',   sa.types.Integer, primary_key=True),
    sa.Column('date', sa.types.DateTime(timezone=True), default=datetime.datetime.utcnow)
)

metadata.create_all()

engine = metadata.bind
conn = engine.connect()
result = conn.execute(data_table.insert().values(id=1))

s = select([data_table])
result = conn.execute(s)
row = result.fetchone()

(1, datetime.datetime(2009, 1, 6, 0, 9, 36, 891887))

row[1].utcoffset()

datetime.timedelta(-1, 64800) # that's my localtime offset!!

datetime.datetime.now(tz=pytz.timezone("US/Central"))

datetime.timedelta(-1, 64800)

datetime.datetime.now(tz=pytz.timezone("UTC"))

datetime.timedelta(0) #UTC

Even if I change it to explicitly use UTC:

...

data_table = sa.Table('data', metadata,
    sa.Column('id',   sa.types.Integer, primary_key=True),
    sa.Column('date', sa.types.DateTime(timezone=True), default=datetime.datetime.now(tz=pytz.timezone('UTC')))
)

row[1].utcoffset()

...

datetime.timedelta(-1, 64800) # it did not use the timezone I explicitly added

Or if I drop the timezone=True:

...

data_table = sa.Table('data', metadata,
    sa.Column('id',   sa.types.Integer, primary_key=True),
    sa.Column('date', sa.types.DateTime(), default=datetime.datetime.now(tz=pytz.timezone('UTC')))
)

row[1].utcoffset() is None

...

True # it didn't even save a timezone to the db this time

解决方案

http://www.postgresql.org/docs/8.3/interactive/datatype-datetime.html#DATATYPE-TIMEZONES

All timezone-aware dates and times are stored internally in UTC. They are converted to local time in the zone specified by the timezone configuration parameter before being displayed to the client.

The only way to store it with postgresql is to store it separately.