Skip to content

Dose there any better way to write timezone aware datetime field without using the SQLAlchemy ? #539

Closed
Closed
Dose there any better way to write timezone aware datetime field without using the SQLAlchemy ?#539
Labels
questionFurther information is requested

Description

@azataiot
azataiot
opened on Jan 26, 2023

First Check

  • I added a very descriptive title to this issue.
  • I used the GitHub search to find a similar issue and didn't find it.
  • I searched the SQLModel documentation, with the integrated search.
  • I already searched in Google "How to X in SQLModel" and didn't find any information.
  • I already read and followed all the tutorial in the docs and didn't find an answer.
  • I already checked if it is not related to SQLModel but to Pydantic.
  • I already checked if it is not related to SQLModel but to SQLAlchemy.

Commit to Help

  • I commit to help with one of those options 👆

Example Code

class UserBase(SQLModel):
    username: str = Field(index=True, unique=True)
    email: EmailStr = Field(unique=True, index=True)  # this field should be unique for table and this field is required
    fullname: str | None = None
    created_at: datetime = Field(default_factory=datetime.utcnow)
    updated_at: datetime = Field(default_factory=datetime.utcnow)

Description

I write my created_at and updated_at fields like this, however, this did not work, because of the time awareness,

SQLModel user: fullname='string' created_at=datetime.datetime(2023, 1, 26, 18, 19, 32, 961000, tzinfo=datetime.timezone.utc) updated_at=datetime.datetime(2023, 1, 26, 18, 19, 32, 961000, tzinfo=datetime.timezone.utc) id=None is_staff=False is_admin=False username='string' email='user@example.com' password='string'
(sqlalchemy.dialects.postgresql.asyncpg.Error) <class 'asyncpg.exceptions.DataError'>: invalid input for query argument $4: datetime.datetime(2023, 1, 26, 18, 19, 3... (can't subtract offset-naive and offset-aware datetimes)

After checking Github, i found this solution:

class AuthUser(sqlmodel.SQLModel, table=True):
  __tablename__ = 'auth_user'
  id: Optional[int] = sqlmodel.Field(default=None, primary_key=True)
  password: str = sqlmodel.Field(max_length=128)
  last_login: datetime.datetime = Field(sa_column=sa.Column(sa.DateTime(timezone=True), nullable=False))

It is written with mixing SQLModel stuff and the SALAlchemy, I know SQLModel is SQLAlchemy under the hood but this feels strange, cause i want to face SQLModel ONLY.

Is there any better way of handling this?

Let's say when SQLModel create tables, it will check the payding field created_at, if it is timezone aware datetime then it will set it as sa_column=sa.Column(sa.DateTime(timezone=True) so that we do not need to mix them both,

Operating System

macOS

Operating System Details

No response

SQLModel Version

0.0.8

Python Version

3.10.2

Additional Context

No response

Activity

  1. azataiot
    added
    questionFurther information is requested
    on Jan 26, 2023
  2. azataiot
    changed the title [-]Isn't there any better way to write timezone aware datetime field without using the SQLAlchemy ?[/-] [+]Dose there any better way to write timezone aware datetime field without using the SQLAlchemy ?[/+] on Jan 26, 2023

  3. antont commented on Feb 18, 2023

    @antont
    antont
    on Feb 18, 2023

    I know SQLModel is SQLAlchemy under the hood but this feels strange, cause i want to face SQLModel ONLY.

    Generally you can't do this kind of things with SQLModel without using SA api directly, so I'd just drop that want.

  4. enchance commented on Oct 31, 2023

    @enchance
    enchance
    on Oct 31, 2023

    Coming from Tortoise ORM moving to SQLModel it would be great to have if the timezone would be saved without having to use SQLAlchemy directly. The project is new so I hope updates could be made soon.

  5. antont commented on Oct 31, 2023

    @antont
    antont
    on Oct 31, 2023

    New release has

    Add support for passing a custom SQLAlchemy type to Field() with sa_type. PR #505 by @maru0123-2004.

    so maybe this could be extended somehow to allow arguments for the sa Column type too.

  6. Jufik commented on Oct 27, 2024

    @Jufik
    Jufik
    on Oct 27, 2024

    That's how I sort this out now:

    from typing import Annotated
from datetime import datetime, timezone
import pytest
from pydantic import types as pydantic_types, ValidationError
from sqlmodel import Field, SQLModel, create_engine, Session, DateTime, Column

class DummySchema(SQLModel):
    timestamp: pydantic_types.AwareDatetime = Field(sa_type=DateTime(timezone=True))


class DummyModel(DummySchema, table=True):
    id: int | None = Field(default=None, primary_key=True)

    Indeed @antont , I believe adding sa_type_kwargs would be really helpful.
    For such a widespread issue (eg: TZ Management): handle pydantic_types casting seems reasonnable.

    That will throw an error:

    class DummySchema(SQLModel):
    timestamp: pydantic_types.AwareDatetime = Field()

    while that works:

    class DummySchema(SQLModel):
    timestamp:  Annotated[datetime, pydantic_types.AwareDatetime] = Field()

    Just considering all the whatever_at kind of fields you'd get that seems like a reasonable thing to have.
    In the end, most of Pydantic Data Types are around date/datetime, and we could have:

    PastDate = Annotated[date, pydantic_types.PastDate]
FutureDate = Annotated[date, pydantic_types.FutureDate]
PastDatetime = Annotated[datetime, pydantic_types.PastDatetime]
FutureDatetime = Annotated[datetime, pydantic_types.FutureDatetime]
AwareDatetime = Annotated[datetime, pydantic_types.AwareDatetime]
NaiveDatetime = Annotated[datetime, pydantic_types.NaiveDatetime]

    as well as an updated get_sqlalchemy_type, JSON/JSONValue would be left to tackle but YAGNI I guess.

    Taking a step back, I believe the this kind of challenges quite common, and making type-mapping used in get_sqlalchemy_type part of the public API would help a LOT.

    In the end one could: get_sqlalchemy_type.register(annotation_type: Any,sa_type:Union[Type[TypeEngine],TypeEngine]) and let SQLModel map that transparently.
    Obviously the Mapping should be Immutable, there are couple of guard rails to be put here and there but nothing impossible.
    That can make SQLModel feel a bit like "magic" if mapping are added randomly on a code base yet that'd be really helpful.

    I can dig into contributing to this, yet I'd like to know if there any kind of interest for such a contrib'?

  7. ekalosak commented on Feb 26, 2025

    @ekalosak
    ekalosak
    on Feb 26, 2025

    @Jufik yes there's interest - your solution works, if you can make the ergonomics more natural, this would be an improvement. Congruity with Pydantic directly supports the first three project objectives:

    Intuitive to write: Great editor support. Completion everywhere. Less time debugging. Designed to be easy to use and learn. Less time reading docs.
Easy to use: It has sensible defaults and does a lot of work underneath to simplify the code you write.
Compatible: It is designed to be compatible with FastAPI, Pydantic, and SQLAlchemy.

    ^ excerpted from: https://sqlmodel.tiangolo.com/

    My 2¢ on the issue: using pydantic.AwareDatetime should "just work":

    class MyThing(SqlModel):
    created_at: AwareDatetime = Field(default_factory=lambda: datetime.now(timezone.utc))
    other_stuff: ...

  8. navneet commented on Jun 9, 2025

    @navneet
    navneet
    on Jun 9, 2025

    I scoured the internet to find this post! For anyone else struggling with timezone aware datetime columns, I found this to be most effective.

    created_at: datetime = Field(sa_type=DateTime(timezone=True))

    OR

    created_at: datetime = Field( default_factory=lambda: datetime.now(timezone.utc), sa_type=DateTime(timezone=True), nullable=False, )

  9. enchance commented on Jun 14, 2025

    @enchance
    enchance
    on Jun 14, 2025

    I've been using these for a while now. Here the timezone is preserved and any latency is excluded since the value is generated by the db and not the app.

    If you prefer to add the field manually for every table:

    from sqlmodel import DateTime, text

...
my_date: datetime = Field(sa_column=Column(DateTime(timezone=True), server_default=text("NULL"), default=None))

    Your dates will be saved as 2025-06-13 10:45:43.129058 +00:00 with the timezone.

    If you prefer to extend them which is what I recommend:

    from sqlmodel import DateTime, func
from sqlalchemy.orm import declared_attr


class UpdatedAtMixin:
    @declared_attr
    def updated_at(self):
        return Column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now(), nullable=True)


class CreatedAtMixin:
    @declared_attr
    def created_at(self):
        return Column(DateTime(timezone=True), server_default=func.now(), nullable=True)


class DeletedAtMixin:
    @declared_attr
    def deleted_at(self):
        return Column(DateTime(timezone=True), server_default=text("NULL"), default=None, index=True)

    The need for @declared_attr is necessary here since they are meant to be used as mixins.

    Use it like so:

    class Account(UpdatedAtMixin, CreatedAtMixin, SQLModel, table=True):
    ...

    Personally, I created this instead to make things easier:

    class DTMixin(UpdatedAtMixin, CreatedAtMixin):
    pass

    then use it as

    class Account(DTMixin, SQLModel, table=True):
    ...

  10. antoniorodr commented on Jul 16, 2025

    @antoniorodr
    antoniorodr
    on Jul 16, 2025

    I've been using these for a while now. Here the timezone is preserved and any latency is excluded since the value is generated by the db and not the app.

    If you prefer to add the field manually for every table:

    from sqlmodel import DateTime, text

    ...
    my_date: datetime = Field(sa_column=Column(DateTime(timezone=True), server_default=text("NULL"), default=None))
    Your dates will be saved as 2025-06-13 10:45:43.129058 +00:00 with the timezone.

    If you prefer to extend them which is what I recommend:

    from sqlmodel import DateTime, func
    from sqlalchemy.orm import declared_attr

    class UpdatedAtMixin:
    @declared_attr
    def updated_at(self):
    return Column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now(), nullable=True)

    class CreatedAtMixin:
    @declared_attr
    def created_at(self):
    return Column(DateTime(timezone=True), server_default=func.now(), nullable=True)

    class DeletedAtMixin:
    @declared_attr
    def deleted_at(self):
    return Column(DateTime(timezone=True), server_default=text("NULL"), default=None, index=True)
    The need for @declared_attr is necessary here since they are meant to be used as mixins.

    Use it like so:

    class Account(UpdatedAtMixin, CreatedAtMixin, SQLModel, table=True):
    ...
    Personally, I created this instead to make things easier:

    class DTMixin(UpdatedAtMixin, CreatedAtMixin):
    pass
    then use it as

    class Account(DTMixin, SQLModel, table=True):
    ...

    I will try to implement your second implementation. But I've a question. How do you define the column name?

    I want to have a "date" column in one of my tables. I don't really care about the time, only the date itself.

    Thanks!

  11. enchance commented on Jul 16, 2025

    @enchance
    enchance
    on Jul 16, 2025

    I've been using these for a while now. Here the timezone is preserved and any latency is excluded since the value is generated by the db and not the app.
    If you prefer to add the field manually for every table:
    from sqlmodel import DateTime, text
    ...
    my_date: datetime = Field(sa_column=Column(DateTime(timezone=True), server_default=text("NULL"), default=None))
    Your dates will be saved as 2025-06-13 10:45:43.129058 +00:00 with the timezone.
    If you prefer to extend them which is what I recommend:
    from sqlmodel import DateTime, func
    from sqlalchemy.orm import declared_attr
    class UpdatedAtMixin:
    @declared_attr
    def updated_at(self):
    return Column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now(), nullable=True)
    class CreatedAtMixin:
    @declared_attr
    def created_at(self):
    return Column(DateTime(timezone=True), server_default=func.now(), nullable=True)
    class DeletedAtMixin:
    @declared_attr
    def deleted_at(self):
    return Column(DateTime(timezone=True), server_default=text("NULL"), default=None, index=True)
    The need for @declared_attr is necessary here since they are meant to be used as mixins.
    Use it like so:
    class Account(UpdatedAtMixin, CreatedAtMixin, SQLModel, table=True):
    ...
    Personally, I created this instead to make things easier:
    class DTMixin(UpdatedAtMixin, CreatedAtMixin):
    pass
    then use it as
    class Account(DTMixin, SQLModel, table=True):
    ...

    I will try to implement your second implementation. But I've a question. How do you define the column name?

    I want to have a "date" column in one of my tables. I don't really care about the time, only the date itself.

    Thanks!

    The column name would be the name of the method you choose.

    # Column name is `updated_at`
class UpdatedAtMixin:
    @declared_attr
    def updated_at(self):
        return Column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now(), nullable=True)

# Column name is `xyz`
class AbcMixin:
    @declared_attr
    def xyz(self):
        return Column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now(), nullable=True)

  12. antoniorodr commented on Jul 16, 2025

    @antoniorodr
    antoniorodr
    on Jul 16, 2025

    Thank you so much @enchance !

  13. iloveitaly commented on Aug 29, 2025

    @iloveitaly
    iloveitaly
    on Aug 29, 2025

    I have a mixin here that adds creatd_at and updated_at: https://github.com/iloveitaly/activemodel/blob/master/activemodel/mixins/timestamps.py

  14. xXMrNidaXx commented on Feb 25, 2026

    @xXMrNidaXx
    xXMrNidaXx
    on Feb 25, 2026

    Timezone-aware datetime without raw SQLAlchemy is doable!

    Option 1 - Field with sa_column:

    from sqlmodel import SQLModel, Field
from sqlalchemy import Column, DateTime
from datetime import datetime, timezone

class Event(SQLModel, table=True):
    id: int = Field(default=None, primary_key=True)
    created_at: datetime = Field(
        sa_column=Column(DateTime(timezone=True)),
        default_factory=lambda: datetime.now(timezone.utc)
    )

    Option 2 - Custom type:

    from sqlmodel import SQLModel, Field
from datetime import datetime, timezone
from typing import Annotated

TZDateTime = Annotated[
    datetime,
    Field(sa_column=Column(DateTime(timezone=True)))
]

class Event(SQLModel, table=True):
    created_at: TZDateTime = Field(default_factory=lambda: datetime.now(timezone.utc))

    Option 3 - Pydantic validator:

    from pydantic import field_validator

class Event(SQLModel, table=True):
    created_at: datetime
    
    @field_validator('created_at', mode='before')
    def ensure_tz(cls, v):
        if v.tzinfo is None:
            return v.replace(tzinfo=timezone.utc)
        return v

    The key is using DateTime(timezone=True) in the sa_column.

  15. fastapi
    locked and limited conversation to collaborators on Feb 25, 2026
  16. svlandeg
    converted this issue into a discussion #1784 on Feb 25, 2026
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    questionFurther information is requested

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions