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

[azataiot]

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

[antont]

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.

[enchance]

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.

[antont]

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.

[Jufik]

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'?

[ekalosak]

@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: ...

[navneet]

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

[enchance]

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

[antoniorodr]

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!

[enchance]

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)

[antoniorodr]

Thank you so much @enchance !

[iloveitaly]

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

[xXMrNidaXx]

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.

[RanaUniverse]

i was trying to solve this problem of storing the datetime in the postgrest with the sqlmodel.
Example i am currently in India: i found even i use the option 1, ie the image below code,

But still i am getting confused as this is giving my ist time even i try to store the utc time.
There was some issue, and then i got to know the idea of 'posix time' so in this case i did somethings like this and as a result i though storing the current time as int or float and then convert to anytime stamp of our wishes, we can get to solve this problem easily, as a result i did this code and found this can help easily to store and manipulate this datetime

Now what i want to know, is this a prefear way to store datetime realted thigns by specefying the

def get_current_unix_time() -> int:
    """
    This will return the Epoch timestamp or posix time
    The time is from the 1st January 1970 utc
    """
    current_time = time.time()
    return int(current_time)

    updated_at: int = Field(
        default_factory=get_current_unix_time,
    )

I want to know is this will a right way to manage datetime in the database using sqlmodel. Or should i try to use the complicated created_time:datetime.datetime liket his way as this place i got confused about the timezone.
i want to know the right way if you can help me some please help me.