SQLAlchemy DateTime timezone

Question

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.

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.

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)

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)