CODEWITHSUNDEEP
pythondatetimesqlalchemy
Brighid McDonnell
Brighid McDonnell

Reputation: 4343

Can SQLAlchemy DateTime Objects Only Be Naive?

I am working with SQLAlchemy, and I'm not yet sure which database I'll use under it, so I want to remain as DB-agnostic as possible. How can I store a timezone-aware datetime object in the DB without tying myself to a specific database? Right now, I'm making sure that times are UTC before I store them in the DB, and converting to localized at display-time, but that feels inelegant and brittle. Is there a DB-agnostic way to get a timezone-aware datetime out of SQLAlchemy instead of getting naive datatime objects out of the DB?

Upvotes: 29

Views: 14467

Answers (3)

Denis Otkidach
Denis Otkidach

Reputation: 33200

There is a timezone parameter to DateTime column time, so there is no problem with storing timezone-aware datetime objects. However I found convenient to convert stored datetime to UTC automatically with simple type decorator:

from sqlalchemy import types
from datetime import datetime, timezone

class UTCDateTime(types.TypeDecorator):

    impl = types.DateTime

    def process_bind_param(self, value, engine):
        if value is None:
            return
        if value.utcoffset() is None:
            raise ValueError(
                'Got naive datetime while timezone-aware is expected'
            )
        return value.astimezone(timezone.utc)

    def process_result_value(self, value, engine):
        if value is not None:
            return value.replace(tzinfo=timezone.utc)

Note, that is behaves nicely when you use naive datetime by accident (meaning it will raise a ValueError).

Upvotes: 46

codeape
codeape

Reputation: 100786

The SQLAlchemy documentation has a recipe for this:

import datetime

class TZDateTime(TypeDecorator):
    impl = DateTime

    def process_bind_param(self, value, dialect):
        if value is not None:
            if not value.tzinfo:
                raise TypeError("tzinfo is required")
            value = value.astimezone(datetime.timezone.utc).replace(
                tzinfo=None
            )
        return value

    def process_result_value(self, value, dialect):
        if value is not None:
            value = value.replace(tzinfo=datetime.timezone.utc)
        return value

Upvotes: 1

rpontual
rpontual

Reputation: 95

I am addressing the desire to have datetime-aware in my code by using UTC in all internal instances. The only issue I came up with was when reading the database. Despite writing datetime-aware to the database, when retrieving the format is naive. My fix was:

import pytz

dt = mydb.query.filter_by(name='test').first().last_update.replace(tzinfo=pytz.utc)
  • dt is the variable that will store the last_update retrieved in datetime format
  • mydb is the name of my db table
  • name is one of the columns in the table
  • last_update is a column that is stored in the format datetime

The trick is replace(tzinfo=pytz.utc)

Upvotes: 3

Related Questions