stale_orbit

There's a class of bug reports that keeps coming back in the NocoBase community, especially in the Chinese-language forum: "all my times are off by 8 hours" or "dates show up as the day before." China is UTC+8, so the shift is 8 hours there. I run my instances at UTC+9, and sure enough — my shift is 9 hours. Whatever your offset is, that's the size of your shift.

That pattern is a strong hint that this isn't random corruption. It's a mechanism. I set up NocoBase 2.x against both PostgreSQL and MySQL and measured what actually gets stored and how it gets reinterpreted, until the mystery had a concrete answer.

Test setup: NocoBase 2.0.51 and 2.1.23 (official Docker images) × PostgreSQL 16 and MySQL 8.4. All data written and read through the REST API, with the server timezone controlled via the container's TZ environment variable. I'm deliberately ignoring the browser-side rendering here — this is about what the server stores and how it interprets it.

Background: 2.x has four datetime field types

NocoBase 2.x collections offer four datetime-ish field types (official list — though several of the per-type detail pages still say "To be added", which is exactly why I measured instead):

Type What it's for
Datetime (with time zone) Absolute instants — event start times, logs
Datetime (without time zone) Wall-clock times you want preserved as-is
Date only Birthdays, due dates, anniversaries
Unix timestamp System integration

Measurement 1: what each type actually stores

I imported "2026-07-12 09:00" via xlsx and looked at the raw values in each database (identical on 2.0.51 and 2.1.23):

Field type PostgreSQL MySQL
Datetime (with TZ) timestamptz → 2026-07-12 09:00:00+09 (an absolute instant, offset included) DATETIME → 2026-07-12 09:00:00 (wall clock only — no offset information)
Datetime (without TZ) timestamp → 09:00:00 DATETIME → 09:00:00
Date only date → 2026-07-12 date → 2026-07-12

The first row is the whole story. The same field type — "Datetime (with time zone)" — is stored as an absolute instant on PostgreSQL, but as a bare wall-clock value on MySQL. MySQL's DATETIME simply has nowhere to put the offset. Keep that in mind for the next experiment.

Measurement 2: change the server TZ, and your stored data changes meaning (MySQL)

On the MySQL setup, I wrote "2026-07-12 09:00" while the server ran with TZ=UTC. Then I changed the app container to TZ=Asia/Tokyo and restarted. Nothing else.

# Raw value in MySQL (not a single byte changed)
dt_tz: 2026-07-12 09:00:00

# What the API returns
with TZ=UTC        → "2026-07-12T09:00:00.000Z"   (= 18:00 at UTC+9)
with TZ=Asia/Tokyo → "2026-07-12T00:00:00.000Z"   (= 09:00 at UTC+9)

Enter fullscreen mode Exit fullscreen mode

The data on disk is byte-for-byte identical, but its meaning as an absolute instant moved by 9 hours. Because MySQL's DATETIME doesn't record which timezone's 09:00 this is, NocoBase has to interpret it through the server's TZ on every read and write. Change TZ after the fact, and every stored datetime in your database silently shifts meaning at once.

This mechanism explains most of the recurring "8-hour shift" reports: an instance starts life with TZ unset (= UTC), someone later sets it to local time — or staging and production simply disagree on TZ. No data was harmed; every interpretation was.

And PostgreSQL? I ran the same experiment and the values didn't move. timestamptz stored the offset, so there's nothing left to reinterpret.

Two rules fall straight out of this:

  1. Pick the server TZ on day one and never change it — especially on MySQL. Pin it explicitly in your compose file, and keep it identical across environments (dev, staging, prod).
  2. If you get to choose the database, PostgreSQL is structurally immune to this entire class of accident.

A separate thing: the import date bug (real, and fixed)

Not every shifted date is this mechanism. There was also a genuine bug in the 2.x era: on v2.0.44–2.0.51, CSV/Excel imports could store dates as one day earlier, even with database, app, and server timezones all aligned. It was confirmed as a defect and fixed in mid-May 2026 (forum threads t/12426, t/12494).

For what it's worth, I could not reproduce it on 2.0.51 importing xlsx/CSV through the API — both databases stored the dates correctly. The reports involved the UI path, so another route or environmental factor was likely involved. Either way, current versions have the fix.

The practical lesson: when dates shift, separate "is this the mechanism?" from "is this a known bug on my version?" Understanding measurement 1 and 2 covers the first question; a quick forum search (including the Chinese category — it's the most active by far) covers the second.

Checklist

  1. Match the field type to the meaning. Birthdays and due dates belong in "Date only". Absolute instants belong in "Datetime (with TZ)". Using "Datetime (with TZ)" for date-only data is the classic way to get dates that render one day off.
  2. Pin the server TZ in your compose file on day one and never touch it afterwards (MySQL especially). UTC or your local zone — either works; just pick one and use it everywhere.
  3. After any import, open one record and check the datetime. These shifts hit every row uniformly — one record tells you everything.
  4. If something is off: check your version, then search the forum — including the Chinese-language category, where most of the real-world reports live.

Takeaways

  • NocoBase 2.x "Datetime (with time zone)" is an absolute instant on PostgreSQL but a wall-clock value + TZ-based interpretation on MySQL (measured).
  • Which is why changing the server TZ later shifts the meaning of all stored datetimes on MySQL — the origin of the recurring "8-hour shift" (or 9, or whatever your offset is).
  • The import date-off-by-one bug was real but is fixed; distinguish mechanism from known bugs and the diagnosis gets fast.

(Measured on 2.0.51 / 2.1.23 × PostgreSQL 16 / MySQL 8.4. Behavior may change in future versions.)

References