Making Timezone-Aware Datetimes in Peewee ORM

For the unfamiliar, Peewee is an easy-to-use ORM for Python. It's my go-to for small- to medium-sized web application projects. I used to use Python's sqlite module directly, and it is fine for small projects, but I've found that mapping the tables to objects and having it generate the schema for me really is valuable.

Nonetheless, I've found the default datetime field to be lacking in a key area: it does not support timezones. I would say that most of the time, you want a timezone-aware datetime, especially if you're dealing with user-generated content from the web. Sure, you can normalize all of it to UTC, but often times that timezone really is valuable information and there's no reason not to store it.

Thankfully this is achieavable with about 10 lines of Python:

from datetime import datetime
from peewee import *


class TimestampTzField(Field):
    """
    A timestamp field that supports a timezone by serializing the value
    with isoformat.
    """

    field_type = 'TEXT'  # This is how the field appears in Sqlite

    def db_value(self, value: datetime) -> str:
        if value:
            return value.isoformat()

    def python_value(self, value: str) -> str:
        if value:
            return datetime.fromisoformat(value)

Here we define our custom field. The field_type attribute is used to set how the field will be stored in Sqlite internally. Since Sqlite doesn't support timezone-aware datetimes, or even datetimes at all, we just store it as text. Then we use Python's built in datetime.isformat and datetime.fromisoformat to do the serialization and deserialization.

Semi-important note: Python's fromisoformat isn't guaranteed to support every kind of ISO8601 string. If you're writing to this database from another application, you'll have to be careful that you keep the formatting right. More information is here.

Then using the field is simple:

db = SqliteDatabase(None)


class BaseModel(Model):
    """
    A base model that just sets the database equal to db above.
    """

    class Meta:
        database = db


class SomeModel(BaseModel):
    """
    Testing our TimestampTzField!
    """

    summary = TextField()
    timestamp = TimestampTzField()


def some_application_code():
    """
    Lets use our model in some queries and insert statements.
    """

    timezone = pytz.timezone("US/Pacific")

    records = SomeModel.select()

    for record in records:
        print(record.timestamp)  # It's a datetime object!

    # You can pass in a datetime directly into create!
    SomeModel.create(
        summary="Timestamps are GR8",
        timestamp=timezone.localize(
            datetime(year=2019, month=11, day=24, hour=13, minute=56)
        )
    )

And that's it! Enjoy that sweet sweet unambiguity!