SQLAlchemy DateTime timezone

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.

One way to solve this issue is to always use timezone-aware fields in the database. But note that the same time can be expressed differently depending on the timezone, and even though this is not a problem for computers it is very inconvenient for us:

2003-04-12 23:05:06 +01:00
2003-04-13 00:05:06 +02:00 # This is the same time as above!

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

Instead, I recommend to use UTC timestamps both throughout the app and timezone-naive dates and times in the database, and only convert them to users local timezone before user sees them.

This strategy lets you have the cleanest code, avoiding any timezone conversions and confusions, and makes your database and app work consistently independent of the "local timezone" differences. For example, you might have your development machine and production server running on cloud in different timezones.

To achieve this, tell Postgresql that you want to see timezones in UTC before initializing the engine.

In SqlAlchemy you do it like this:

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

And if you are using tornado-sqlalchemy you can use:

factory = make_session_factory(..., connect_args={"options": "-c timezone=utc"})

Since we use all UTC timezones everywhere, we simply use timezone-naive dates and times in the model:

created_at = Column(DateTime, default=datetime.utcnow)
updated_at = Column(DateTime)

And the same in case if you are using alembic:

sa.Column('created_at', sa.DateTime()),
sa.Column('updated_at', sa.DateTime()),

And in the code use UTC time:

from datetime import datetime
...
model_object.updated_at = datetime.now(timezone.utc)

a solution is given in this question’s answer:

you can circumvent that by storing all (date)time objects in your database in UTC, and converting the resulting naïve datetime objects to aware ones on retrieval.

the only drawback is that you lose timezone information, but it’s probably a good idea to store your datetime objects in utc, anyway.

if you care about the timezone information, i’d store it separately, and only convert the utc to local time in the last possible instance (e.g. right before displaying)

or maybe you don’t need to care after all, and can use local timezone information from the machine you run your progam on, or the user’s browser if it’s a webapp.

The following structure is recommended to store UTC date and time data in the database as well as to prevent data storage that does not have such location information.

import datetime
from sqlalchemy import DateTime
from sqlalchemy.types import TypeDecorator

    
class TZDateTime(TypeDecorator):
    """
    A DateTime type which can only store tz-aware DateTimes.
    """
    impl = DateTime(timezone=True)

    def process_bind_param(self, value, dialect):
        if isinstance(value, datetime.datetime) and value.tzinfo is None:
            raise ValueError('{!r} must be TZ-aware'.format(value))
        return value

    def __repr__(self):
        return 'TZDateTime()'

The values stored in the database should be defined as follows:

import datetime

import pytz


def tzware_datetime():
    """
    Return a timezone aware datetime.

    :return: Datetime
    """
    return datetime.datetime.now(pytz.utc)