Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Datetime with timezone edited incorrectly #796

Open
2 tasks done
Azkae opened this issue Jul 23, 2024 · 2 comments
Open
2 tasks done

Datetime with timezone edited incorrectly #796

Azkae opened this issue Jul 23, 2024 · 2 comments

Comments

@Azkae
Copy link

Azkae commented Jul 23, 2024

Checklist

  • The bug is reproducible against the latest release or master.
  • 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:

Screenshot 2024-07-23 at 11 55 18 Screenshot 2024-07-23 at 11 55 30 Screenshot 2024-07-23 at 11 55 35

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
Copy link

peterschutt commented Aug 15, 2024

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

@Azkae
Copy link
Author

Azkae commented Oct 8, 2024

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?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

2 participants