aminalaee
Datetime with timezone edited incorrectly
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
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
Sorry for the late reply. Thank you for the workaround, it fixed the issue for me. I'm wondering if this should be sqladmin's default behavior?
Guys, I still don't understand how the simple replace(tzinfo=timezone.utc) can resolve the widget problem?
We still can't assume the LOCAL tz of the user browser. So the datetime is still double converted.
I assume WTForm widget uses something like <input type="datetime-local"> on the edit form so the value is shown in the user local tz (even if we comply the best practice and store it in UTC awared in DB). So when we save "the changes" (actually, if the datetime field wasn't changed) the rendered value is converted again into UTC as the str value provided has no local tz shift info.
This is the solution I've implemented that seems to work well for handling timezone-aware datetimes with the native browser datetime-local input.
The timezone-aware datetime is stored in the database, but it's displayed in the browser using the native datetime-local input field, rendered in the specified timezone (Europe/Kyiv in the example). Any edits made by the user are then converted back to a timezone-aware datetime before being stored in the database.
from datetime import datetime
from zoneinfo import ZoneInfo
from markupsafe import Markup
from wtforms.fields import Field
from wtforms.widgets import DateTimeInput
TIMEZONE = ZoneInfo('Europe/Kyiv')
UTC = ZoneInfo('UTC')
LOCAL_FORMAT = '%Y-%m-%dT%H:%M'
def utc_to_local(date: datetime) -> str:
return date.astimezone(TIMEZONE).strftime(LOCAL_FORMAT)
def local_to_utc(date: str) -> datetime:
naive_dt = datetime.strptime(date, LOCAL_FORMAT)
local_dt = naive_dt.replace(tzinfo=TIMEZONE)
return local_dt.astimezone(UTC)
class DateTimeWidget(DateTimeInput):
def __call__(self, field, **kwargs):
return Markup(f"""
<input
type="datetime-local"
id="{field.id}"
name="{field.name}"
{self.html_params(**kwargs)}
value="{utc_to_local(field.data) if field.data else ''}"
/>
""")
class DateTimeField(Field):
widget = DateTimeWidget()
def process_formdata(self, valuelist: list[str]) -> None:
self.data = local_to_utc(valuelist[0]) if valuelist and valuelist[0] else None
And then, in the admin model, you would simply use the field override:
form_overrides = {
'date_field_name': DateTimeField,
}
The problem is here:
TIMEZONE = ZoneInfo('Europe/Kyiv')
You can't handle different user's timezones.