Naive MariaDB DATETIME Made My API Times Wrong
Commit db470da adds _utc_aware at the repository so article timestamps from a naive MariaDB DATETIME are sent by Pydantic with a Z (UTC), not zoneless.
On the last deploy I opened an article detail page and looked at the publish-time column: 2026-08-29T14:30:00. No Z at the end. The server runs on UTC, and I really did publish that article at 14.30 UTC. The problem only showed up when a reader in WIB (UTC+7) said the time looked "7 hours ahead".
My first guess: a formatting bug in the frontend. But when I opened the raw API response, the timestamp had no offset from the start. So it wasn't the formatter — the bug was one layer deeper.
The problem is in reading from the database
The published_at column in MariaDB is type DATETIME. Unlike TIMESTAMP, DATETIME carries no timezone information at all. The MySQL docs state it plainly: TIMESTAMP is converted from the session timezone to UTC on storage and back to the session timezone on read, "but this does not occur for other types such as DATETIME"[1]. The convention in this repo is to store everything in UTC, so the value is genuinely UTC — it just has no label.
When SQLAlchemy or SQLModel reads that row, what comes back is a naive datetime (tzinfo=None). Pydantic then serializes a naive datetime without an offset, producing a bare 2026-08-29T14:30:00. In the browser, new Date() treats a zoneless string as the visitor's local time:
// string without zone -> treated as the viewer's local time
new Date("2026-08-29T14:30:00");
// a reader in WIB (UTC+7) reads this as 14.30 WIB
// but the real value is 14.30 UTC -> a 7-hour gapShort version: the API sends UTC time but never says "this is UTC", and JavaScript happily assumes it's local time. Any reader outside the server's zone sees the wrong clock.
Fix it at the repository boundary, not in the model
The fix is small but placed where it belongs: when mapping the DB row to the domain entity, in the _utc_aware helper. Commit db470da added this at the repository boundary:
def _utc_aware(dt: datetime | None) -> datetime | None:
"""Stamp naive DB datetimes as UTC."""
if dt is None:
return None
return dt.replace(tzinfo=timezone.utc) if dt.tzinfo is None else dt.astimezone(timezone.utc)
# used when mapping row -> domain entity
published_at=_utc_aware(row.published_at),
created_at=_utc_aware(row.created_at),
updated_at=_utc_aware(row.updated_at),Immediately Pydantic sees an aware-UTC datetime and serializes it as 2026-08-29T14:30:00Z. That Z matters: now the browser knows it's UTC and can convert to the visitor's zone correctly. I placed it at the repository boundary rather than inside the Pydantic model because that's where the contract "what comes from the DB is UTC" has to be enforced. The domain entity becomes timezone-correct, and MariaDB's naivety never leaks upward. I wrote about a similar serialization-boundary leak before, on TypeScript types that don't reach JSON.
Why I kept DATETIME instead of switching to TIMESTAMP
I deliberately kept DATETIME plus the aware-UTC tag, rather than moving to TIMESTAMP. Reason: TIMESTAMP automatically converts to and from the session timezone[1], which means "which zone is used" goes back to depending on the session setting — exactly the ambiguity I wanted to remove. Also TIMESTAMP has a range limit: MariaDB stores it as seconds since the epoch (1970-01-01 UTC)[2], so very old or very far-future dates can overflow. Storing UTC explicitly in DATETIME is clearer and avoids the 2038 footgun.
The companion frontend fix just pins timeZone: "Asia/Jakarta" in Intl.DateTimeFormat[3] so SSR and every visitor see WIB. But that only works correctly because the API now sends a real Z, not because the frontend guesses.
Source
[1] https://dev.mysql.com/doc/en/datetime.html
[2] https://mariadb.com/docs/server/reference/data-types/date-and-time-data-types/timestamp