SQLAlchemy 日期时间时区 [英] SQLAlchemy DateTime timezone

问题描述

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

请注意 datetime.datetime.utcnow 如何与 timezone=True 一起使用:

import sqlalchemy as sa从 sqlalchemy.sql 导入选择导入日期时间metadata = 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()引擎 = 元数据.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))

row[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() 是 None

...

<块引用>

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.

这篇关于SQLAlchemy 日期时间时区的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！