Datetime with timezone edited incorrectly

[Azkae]

Checklist

• [X] The bug is reproducible against the latest release or master.
• [X] There are no similar issues or pull requests to fix it yet.

Describe the bug

Datetime with timezone are edited incorrectly. In this example I have a table with a name and a created_at column:

class User(Base):
    __tablename__ = "users"

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str]
    created_at: Mapped[dt.datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now()
    )

If I change the name of a row, it will also edit the created_at column, most likely because the timezone is missing from the edit field:

You can see that the created_at column gets updated (from 9:55 to 7:55)

Steps to reproduce the bug

Here is the complete program used in the example:

from contextlib import asynccontextmanager
import datetime as dt
from sqlalchemy import DateTime, func, text
from sqlalchemy.ext.asyncio import async_sessionmaker, create_async_engine
from sqlalchemy.orm import Mapped, declarative_base, mapped_column

from fastapi import FastAPI
from sqladmin import Admin, ModelView

Base = declarative_base()
engine = create_async_engine(
    "postgresql+asyncpg://wave:@localhost/sqladmin-testing",
)

class User(Base):
    __tablename__ = "users"

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str]
    created_at: Mapped[dt.datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now()
    )

Session = async_sessionmaker(bind=engine)

@asynccontextmanager
async def lifespan(app: FastAPI):
    async with engine.begin() as conn:
        await conn.run_sync(Base.metadata.create_all)

    async with Session() as conn:
        await conn.execute(text("INSERT INTO users (name) values ('foo'), ('bar')"))
        await conn.commit()
    yield

app = FastAPI(lifespan=lifespan)
admin = Admin(app, engine)

class UserAdmin(ModelView, model=User):
    column_list = [User.id, User.name, User.created_at]

admin.add_view(UserAdmin)

The issue doesn't happen with SQLite, only with PostgreSQL

Expected behavior

I expect the created_at column to not be modified.

Actual behavior

The created_at column gets edited incorrectly (from 9:55 to 7:55)

Debugging material

No response

Environment

sqladmin version: 0.18.0

Additional context

No response

[peterschutt]

I think you are looking at doing something like the following.

This tells the backend to parse the datetime field considering the timezone offset:

class UserAdmin(ModelView, model=User):
    column_list = [User.id, User.name, User.created_at]
    form_args = {"created_at": {"format": "%Y-%m-%dT%H:%M:%S.%f%z"}}

And in your project root directory create /templates/sqladmin/layout.html - this tells the front-end to include the offset in the representation sent to the server.

... copy content of sqladmin/templates/sqladmin/layout.html and append the following:

{% block tail %}
<script>
    document.addEventListener('DOMContentLoaded', function () {
        const dateTimeConfig = {
            dateFormat: "Z",  // This will output ISO 8601 format
        };
        flatpickr(".flatpickr-input", dateTimeConfig);
    });
</script>
{% endblock %}

Would get more complicated if you were using offsets other than UTC I suppose.

EDIT

Actually, there is a problem with the above..

Given the format "%Y-%m-%dT%H:%M:%S.%f%z", strptime will parse a date/time like "2024-08-15T05:17:00.000000Z" as UTC, but strftime will format that same date/time as "2024-08-15T05:17:17:00.000000+0000". Flatpickr wants the Z, not +0000 and therefore doesn't recognize the date/time as UTC when it renders, assumes local time and applies the local UTC offset to the date/time again. In my case, I'm in +1000 and if the server sends "2024-08-15T05:17:00.000000+0000" it gets rendered to the form as "2024-08-14T19:17:00.000Z".

TBH, it feels easier to handle this all server side and leave flatpickr config out of it. Here's something similar I've done to handle the DateTimeUTC types from the Advanced-Alchemy library:

from __future__ import annotations

from datetime import timezone
from typing import Any

from sqladmin import ModelView
from sqladmin.forms import ModelConverter, converts
from wtforms import DateTimeField

class DateTimeUTCField(DateTimeField):
    def process_formdata(self, valuelist: list[Any]) -> None:
        super().process_formdata(valuelist)

        if self.data is None:
            return

        self.data = self.data.replace(tzinfo=timezone.utc)

class DateTimeUTCConverter(ModelConverter):
    # mypy: error: Untyped decorator makes function "convert_date_time_utc" untyped  [misc]
    @converts("DateTimeUTC")  # type: ignore[misc]
    def convert_date_time_utc(self, *, kwargs: dict[str, Any], **_: Any) -> DateTimeUTCField:  # noqa: PLR6301
        return DateTimeUTCField(**kwargs)

class AuditModelView(ModelView):
    form_converter = DateTimeUTCConverter