Comparison of timezone aware datetime fields.

[jymchng]

First Check

• I added a very descriptive title here.
• I used the GitHub search to find a similar question 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

from typing import Optional
from app.utils.uuid6 import uuid7, UUID
from sqlmodel import SQLModel, Field, Relationship
from sqlalchemy import Column, TIMESTAMP, text
from datetime import datetime


class BaseUUIDModel(SQLModel):
    id: UUID = Field(
        default_factory=uuid7,
        primary_key=True,
        index=True,
        nullable=False,
    )
    created_datetime: Optional[datetime] = Field(sa_column=Column(
        TIMESTAMP(timezone=True),
        nullable=False,
        server_default=text("CURRENT_TIMESTAMP"),
    ))
    updated_datetime: Optional[datetime] = Field(sa_column=Column(
        TIMESTAMP(timezone=True),
        nullable=False,
        server_default=text("CURRENT_TIMESTAMP"),
        onupdate=text("CURRENT_TIMESTAMP"),
    ))

Description

I have a simple model as shown in the codes above.

I am trying to get the past 24 hours records of this BaseUUIDModel.

dt_now = datetime.now(timezone.utc)
dt_past = dt_now - timedelta(hours=past_hours)
logger.info(f"{(dt_now - timedelta(hours=past_hours))}")
query = select(BaseUUIDModel).where(
      and_( # ALWAYS COMPARE USING TIMESTAMPS BECAUSE OF UTC-AWARE VS NOT AWARE DIFFERENCE, it is `timestamp()` function not `timestamp` attr
            BaseUUIDModel.created_datetime.timestamp() >= dt_past.timestamp(),
            BaseUUIDModel.created_datetime.timestamp() <= dt_now.timestamp(),
        )
    )

It gives me this error:

AttributeError: Neither 'InstrumentedAttribute' object nor 'Comparator' object associated with Project.created_datetime has an attribute 'timestamp'

Operating System

Windows

Operating System Details

No response

SQLModel Version

0.0.6

Python Version

Python 3.10.6

Additional Context

No response

[drorata]

Not really an answer, but rather a comment. The part BaseUUIDModel.created_datetime.timestamp() is confusing on its own, isn't it? The part BaseUUIDModel is the class which doesn't have the attribute created_datetime. The latter is only available/accessible when there's an instantiation of an object of type BaseUUIDModel.

If you run:

foo = BaseUUIDModel(created_datetime="2022-12-31 21:45:59")
foo.created_datetime.timestamp()  # This will return 1672519559.0
BaseUUIDModel.created_datetime.timestamp()  # This will fail with the error: AttributeError: type object 'BaseUUIDModel' has no attribute 'created_datetime'