SQLAlchemy UTC Datetime Type

0.14.0 · active · verified Thu Apr 16

SQLAlchemy-UTC provides a `UtcDateTime` type for SQLAlchemy that reliably stores and retrieves timezone-aware `datetime` objects in UTC. It acts as a robust replacement for SQLAlchemy's built-in `DateTime(timezone=True)`, particularly for databases like SQLite and MySQL that lack native `timestamptz` support by converting values to and from UTC. The library also includes a `utcnow()` helper function for server-side default generation. The current version is 0.14.0, released in September 2021.

Common errors

```
AttributeError: 'datetime.datetime' object has no attribute 'tzinfo'
```
cause Attempting to assign a naive `datetime` object to a `UtcDateTime` column or performing an operation that assumes `tzinfo` on a naive object returned from a non-UtcDateTime column.

fix Ensure all `datetime` objects interacting with `UtcDateTime` columns are timezone-aware, e.g., `datetime.datetime.now(datetime.timezone.utc)`.
```
SQLAlchemy's DateTime(timezone=True) loses timezone information when fetching from SQLite/MySQL.
```
cause The underlying database (SQLite/MySQL) does not natively support `TIMESTAMP WITH TIME ZONE`, causing SQLAlchemy's default `DateTime(timezone=True)` to often store/retrieve naive datetimes or local times without proper conversion.

fix Replace `DateTime(timezone=True)` with `UtcDateTime` from `sqlalchemy-utc`. This library explicitly handles the conversions to ensure UTC storage and timezone-aware retrieval on these databases.
```
Timestamps retrieved from PostgreSQL are in local time instead of UTC, even if stored as UTC.
```
cause PostgreSQL connections have an associated timezone, which defaults to the system's timezone. If not explicitly set to UTC, `TIMESTAMP WITHOUT TIME ZONE` values (which is how `UtcDateTime` might map on some systems) will be interpreted as local time on retrieval, or `TIMESTAMP WITH TIME ZONE` might be converted.

fix Configure the SQLAlchemy engine to ensure connections use UTC, e.g., `create_engine('postgresql://user:pass@host/db', connect_args={'options': '-c timezone=utc'})` or verify that PostgreSQL's `TimeZone` setting is 'UTC'.

Warnings

gotcha UtcDateTime strictly requires and returns timezone-aware `datetime` objects. Passing naive `datetime` objects (those without `tzinfo`) will lead to unexpected behavior or errors, as `UtcDateTime` will convert them to UTC assuming they are local time.
Fix: Always pass `datetime.datetime.now(datetime.timezone.utc)` or equivalent timezone-aware objects. Convert local times to UTC explicitly before passing to UtcDateTime columns if necessary.
gotcha When using `sqlalchemy_utc.utcnow` with MySQL, it cannot be used with `server_default` for columns. MySQL does not support functions in `server_default` values for SQLAlchemy.
Fix: For MySQL, use `default=utcnow` instead of `server_default=utcnow` when defining `UtcDateTime` columns. The `default` parameter handles client-side defaults.
breaking Python's built-in `datetime.datetime.utcnow()` is deprecated starting in Python 3.13 and is scheduled for removal. Using it will issue `DeprecationWarning`s.
Fix: Prefer `datetime.datetime.now(datetime.timezone.utc)` for standard library UTC timestamps, or specifically use `sqlalchemy_utc.utcnow` for column defaults as intended by the library.

Install

pip install sqlalchemy-utc Install with pip

Imports

UtcDateTime
```
from sqlalchemy_utc import UtcDateTime
```
utcnow
wrong:
```
from datetime import datetime; datetime.utcnow()
```
correct:
```
from sqlalchemy_utc import utcnow
```
The standard `datetime.utcnow()` is deprecated in Python 3.13+ and does not return timezone-aware objects. `sqlalchemy_utc.utcnow` provides a dialect-aware UTC function.

Quickstart

This quickstart demonstrates defining a SQLAlchemy model with `UtcDateTime` columns, using `utcnow()` for default and on-update values. It then creates, retrieves, and updates an event, verifying that the `datetime` objects returned are always timezone-aware and in UTC.

import datetime
from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.orm import sessionmaker, declarative_base
from sqlalchemy_utc import UtcDateTime, utcnow

# Setup database (using SQLite for simplicity)
engine = create_engine('sqlite:///./test.db')
Base = declarative_base()

class Event(Base):
    __tablename__ = 'events'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    created_at = Column(UtcDateTime, default=utcnow)
    updated_at = Column(UtcDateTime, default=utcnow, onupdate=utcnow)

    def __repr__(self):
        return f"<Event(id={self.id}, name='{self.name}', created_at={self.created_at}, updated_at={self.updated_at})>"

# Create tables
Base.metadata.create_all(engine)

Session = sessionmaker(bind=engine)
session = Session()

# Create a new event
new_event = Event(name='Meeting Start')
session.add(new_event)
session.commit()
print(f"Created event: {new_event}")

# Retrieve and verify timezone
retrieved_event = session.query(Event).filter_by(name='Meeting Start').first()
print(f"Retrieved event: {retrieved_event}")
assert retrieved_event.created_at.tzinfo == datetime.timezone.utc
print("Created_at is UTC aware.")

# Update an event
retrieved_event.name = 'Meeting Concluded'
session.add(retrieved_event)
session.commit()
print(f"Updated event: {retrieved_event}")
assert retrieved_event.updated_at.tzinfo == datetime.timezone.utc
print("Updated_at is UTC aware.")

session.close()

view raw JSON →