Can SQLAlchemy DateTime Objects Only Be Naive?

Question

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?

Denis Otkidach · Accepted Answer · 2020-12-11T10:20:47.593

45

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).

edited Dec 11 '20 at 10:20

answered Mar 27 '10 at 07:01

Denis Otkidach

32,032
8
79
100

This doesn't work as written, it needs a minor tweak in `process_bind_param`: `def process_bind_param(self, value, engine):\n if value is not None:\n if value.tzinfo is None:\n return value.replace(tzinfo=tzutc())\n else:\n return value.astimezone(tzutc())` – Jesse Dhillon Aug 01 '10 at 08:03
11

@Jesse Dhillon: it's intentional to prevent accidental storage of naive datetime objects, while your recipe incorrectly inteprets any datetime with any timezone as being UTC. – Denis Otkidach Aug 04 '10 at 03:35
6

@Dennis, so using your type, one is expected to create and store only timezone-aware `datetime` objects? Now that I understand that, I like your solution more because it requires the user to be explicit about what they are saving. – Jesse Dhillon Aug 04 '10 at 05:37
4

Would I be correct in thinking that the last bit, where it creates the datetime, is equivalent to `value.replace(tzinfo=tzutc())` as seen here: http://mindlace.net/2012/11/13/make-datetime-naive-for-mysql-in-sqlalchemy/ ? Or does one do something slightly different? I'm scared of getting something wrong here! – OrganicPanda Jan 18 '13 at 11:49
@DenisOtkidach Very elegant solution. Time to update some orm code to use UTCDateTime :) – Derek Litz Apr 05 '13 at 22:22
1

@OrganicPanda Yes, it's possible to simplify it as you described. Both expressions evaluate to the same value. You can test it yourself by substituting `value` for any `datetime` object. – Mr. Deathless Feb 25 '16 at 12:16
Wouldn't this conversion prevent using database index on date column when selecting based on date as in `q = query.filter(my_table.c.start_date >= some_date)`? – Piotr Dobrogost Oct 17 '16 at 10:53
1

The SQLAlchemy docs has a recipe: https://docs.sqlalchemy.org/en/14/core/custom_types.html#store-timezone-aware-timestamps-as-timezone-naive-utc – codeape Dec 09 '20 at 09:45
I'm having trouble using this recipe when combining with `from_statement`, created a question here: https://stackoverflow.com/questions/69181651/using-custom-type-with-sqlalchemy-why-are-the-results-of-process-result-value-n does anyone know of a workaround for this? Thanks! – Elias Dorneles Sep 14 '21 at 16:49

score 2 · Answer 2 · edited Jun 08 '20 at 23:35

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)

score 1 · Answer 3 · answered Dec 09 '20 at 09:35

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

Can SQLAlchemy DateTime Objects Only Be Naive?

3 Answers3

Linked