SQLAlchemy DateTime时区 [英] SQLAlchemy DateTime timezone
问题描述
SQLAlchemy的 DateTime
类型允许一个 timezone = True
参数将非天真的datetime对象保存到datbase ,并将其返回。有没有办法修改SQLAlchemy传入的 tzinfo
的时区,可能是例如UTC?我意识到我可以使用 default = datetime.datetime.utcnow
;然而,这是一个天真的时间,很高兴地接受有人传递一个天真的本地时间的datetime,即使我使用 timezone = True
,因为它使本地或UTC时间非天真,没有基地时区正常。我已尝试(使用 pytz )使datetime对象不天真,但是当我将其保存到db请注意,datetime.datetime.utcnow不适用于timezone = True:
从sqlalchemy.sql导入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()
pre>
(1,dat etime.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' )))
)
row [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#它甚至没有保存时区db这个时候
http://www.postgresql.org/docs/8.3/interactive/datatype-datetime.html#DATATYPE-TIMEZONES
所有时区aw日期和时间在UTC内部存储。在时区配置参数指定的区域中,将它们转换为本地时间,然后再显示给客户端。
使用postgresql存储它的唯一方法是单独存储。
SQLAlchemy's DateTime
type allows for a timezone=True
argument to save a non-naive datetime object to the datbase, 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 normailze 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 DateTime时区的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!