SQLAlchemy DateTime timezone

Question

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

Just to note that `default=datetime.datetime.now(tz=pytz.timezone('UTC'))` is almost certainly not what you want as it will make the default date the _constant_ datetime when this code was loaded, not the datetime when you insert a new row into the database / create a new SQLAlchemy ORM object instance. — Tom, Feb 02 '22 at 11:10

score 32 · Accepted Answer · answered Jan 20 '09 at 16:24

32

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.

answered Jan 20 '09 at 16:24

iny

7,339
3
31
36

1

is the " timezone configuration parameter " the timezone info in the datetime_with_timzone in stored database ? – Ciasto piekarz Jan 28 '18 at 06:08
3

@Ciastopiekarz No. The `timezone` configuration parameter is a client connection parameter. The `datetime with time zone`, whose value is stored in UTC gets converted to the timezone specified in this `timezone` parameter before displaying or returning it to you. Use `SHOW timezone` to check what value it has for your current connection. Think about what you really need: do you need to store the time when an event occurred, or do you also need to store info in which time zone the stored datetime value actually originated? If it is the latter, you need to store this information separately. – compostus Feb 23 '18 at 12:30
if I only know the time when it occurred but in UTC, thats probably current connection, then without timezone info I won't ever know when exactly the correct time of user some event took place. – Ciasto piekarz Feb 23 '18 at 13:08
2

To be fair, this is an SQL thing, not just PostgreSQL. – beldaz Aug 10 '18 at 08:19

Caner · Answer 2 · 2020-01-27T15:06:07.313

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 sqlalchemy example is dependant on the postgres driver library used and won't work for `asyncpg` for example. — Wereii, Jul 21 '23 at 18:11

score 10 · Answer 3 · edited May 23 '17 at 11:46

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.

if we either way loose the timezone information but still able to save the date/time in UTC then why not save date time with timezone into one column defined of the type `string` instead of `timestamptz` ? — Ciasto piekarz, Aug 22 '17 at 15:45

score 4 · Answer 4 · answered Jun 23 '20 at 15:32

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)

Since Python 3.2, UTC is available as dt.datetime.timezone.utc without importing pytz. — Jérôme, May 21 '21 at 14:53
There is also now first class ZoneInfo from the zoneinfo package — Mardoxx, May 30 '23 at 18:33

SQLAlchemy DateTime timezone

4 Answers4

Linked