Storing Every Date In UTC

Storing every date in UTC

Shipped as #1446, following issue #126

FreeITSM stores every moment in UTC and converts it when it draws it. That is the rule, and it was very nearly true: 570 statements said UTC_TIMESTAMP() in as many words.

The exception was everything nobody wrote down.

---

1. The leak

When a statement does not name a column, the database fills it in from that column's own default. Almost every datetime column in the schema is declared like this:

`created_datetime` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,

CURRENT_TIMESTAMP is not UTC. MySQL evaluates it — along with NOW() and CURDATE() — in the connection's session time zone, which defaults to SYSTEM: the database server's own clock. So every statement that forgot a column wrote a local wall clock into a field the rest of the product reads as a UTC instant.

That is what issue #126 turned out to be. Sweeping the schema against every INSERT in the codebase found it was not one place:

220 tables carry 272 auto-stamped datetime columns, and 302 statements let one of them fire.

On a server whose clock is UTC, every one of those is accidentally correct — which is most installations, every sensible container image, and the reason this survived for years.

---

2. The fix is one line, in eleven places

function dbConnectionOptions(): array
{
    return [
        PDO::MYSQL_ATTR_INIT_COMMAND => "SET time_zone = '+00:00'",
    ];
}

Pinned to UTC, CURRENT_TIMESTAMP and UTC_TIMESTAMP() are the same instant, so a column nobody remembered to fill in can no longer be wrong. All 302 are corrected at once, and so is every statement written from now on.

Why INIT_COMMAND and not SET after connecting

An INIT_COMMAND re-runs when the client silently reopens the socket. A SET issued once by hand is lost on reconnect, and nothing announces that it has been lost — you would get a connection quietly back on the server's clock with no way to tell from the outside.

Why eleven

connectToDatabase() is the front door, but ten other places build a PDO themselves: the two password-reset endpoints, three OAuth callbacks, the analyst sign-in page and five debug tools. A fix that only covered the front door would have left the sign-in page and the password reset writing local time, which is precisely the sort of gap the original bug was.

---

3. ⚠️ The half that made this more than a one-liner

Not every date in FreeITSM is a moment in time, and the ones that are not must never be compared against one. There are three kinds, and the timezone reference sets them out:

Kind	Examples	"Now" is
An instant	created, closed, notes, audit, sync metadata	UTC_TIMESTAMP()
A naive wall clock	change windows, scheduled work, calendar events, PIR actuals	the local clock
A bare date	contract end, warranty expiry, licence renewal, task due date, article review date	the local calendar date

A change window stored as 14:00 means two o'clock, for everybody, wherever they are. Ask whether it is open by comparing it to a UTC instant and every window is judged the server's offset early. Likewise "is this contract expiring within 30 days" is a question about a calendar, and a calendar that rolls over at UTC midnight is an hour ahead of the one those dates were typed against — so "due today" would have changed its answer for an hour every night, which is exactly the sort of thing that gets reported as a ghost and never reproduced.

Those comparisons had been using bare NOW() and CURDATE(). They were right only by accident — MySQL happened to evaluate them in the server's zone, which nothing declared and nothing documented. Pinning the connection removed the accident, so they were made explicit first:

naive_now()        // 'Y-m-d H:i:s' in the application's configured zone
naive_today_sql()  // the same day as a quoted SQL literal, '2026-09-01'

36 comparisons across Watchtower, the calendar and schedule feeds, the calendar-sync backfill, the four REST resources and the scheduled contract and warranty reminders now say which clock they mean.

The one that returns a literal rather than a placeholder

naive_today_sql() hands back '2026-09-01', quotes included, to be interpolated straight into SQL. That is a deliberate exception to the parameter-binding rule and it is worth saying why: every caller builds its query by assembling fragments into a $where[] array and collecting bound parameters somewhere else. Threading an extra parameter through each one means getting its position right in a list built elsewhere — a much better way to introduce a bug than the one it prevents.

The value cannot come from a request. It is produced by PHP's own date() and the format is asserted before it is returned, so what comes back is always 'YYYY-MM-DD'; if the assertion could ever fail it falls back to CURDATE(), which is wrong by an hour rather than wrong by being a syntax error on somebody's dashboard.

The one CURDATE() deliberately left alone

The Intune dashboard's 90-day enrolment count compares a UTC column, so the UTC date is the right boundary and an hour is noise at that width. It is commented in place, so the next person sweeping for CURDATE() finds an answer rather than a puzzle.

---

4. 📁 The files

🔌 connection · 🕐 helper · 📖 read/compare · ✏️ write · 🧪 test

🎨	File	What changed
🔌	includes/db.php	dbConnectionOptions() — the pin, and the explanation everything else points at. It lived in config.php at first, which broke every install (#129) — that file is the operator's, and upgrading left the definition behind.
🔌	includes/functions.php	connectToDatabase() passes it
🔌	auth/login.php · auth/oauth_callback.php · auth/google_oauth_callback.php	the three sign-in paths
🔌	api/auth/request_password_reset.php · api/auth/reset_password.php	password reset
🔌	api/system/debug-tools/D001…D012	five debug tools
🕐	includes/timezone.php	naive_now() and naive_today_sql(), with the three kinds spelled out
📖	includes/watchtower_queries.php	change windows, calendar, contracts, warranties, due dates, review dates
📖	api/tickets/schedule_feed.php · api/calendar/feed.php · api/tickets/calendar_enrolment.php	the feeds and the sync backfill
📖	api/v1/resources/assets.php · api/v1/resources/contracts.php · api/v1/resources/software.php · api/v1/resources/tasks.php	nine date filters
📖	includes/workflow_scheduled.php	contract.expiring and asset.warranty_expiring — the reminder windows
✏️	includes/calendar_sync/push.php · includes/calendar_sync/pull.php · api/system/calendar_sync.php	NOW() → UTC_TIMESTAMP() on sync metadata and audit
✏️	includes/search/*.php · four others	the same, on index and token timestamps
🧪	tests/utc-connection.php	new — eleven assertions

---

5. How it was verified

The suite, and the control that makes it mean anything

1. The connection opens in UTC
  PASS session time_zone is +00:00
  PASS NOW() == UTC_TIMESTAMP()
  PASS CURRENT_TIMESTAMP == UTC_TIMESTAMP()

2. Positive control: an unpinned connection is NOT the same clock
  PASS unpinned connection differs from UTC     offset -60 min

3. A column filled in by DEFAULT CURRENT_TIMESTAMP now stores UTC
  PASS a row that names no date column lands on UTC

4. The wall-clock helpers still answer in local time
  PASS naive_now() is the app zone, not UTC     +60 min, expected +60 for Europe/London
  PASS naive_today_sql() is a quoted literal date
  PASS naive_today_sql() follows the LOCAL date  Auckland 2026-09-02 vs CURDATE() 2026-09-01

5. The pin survives a reconnect
6. A naive window inside the offset hour is judged by the wall clock
  PASS the wall clock sees it as started
  PASS UTC does NOT see it as started           the distinction is real

Section 2 is the one that matters. Every other assertion would also pass on a server that happens to run UTC with the fix removed — which is how the original bug hid. So the test opens a second, unpinned connection and proves the two clocks disagree; where they do not, it prints a SKIP saying the run cannot distinguish the fix from the bug, rather than reporting a false green.

Section 6 exists for the same reason. Comparing live Watchtower counts before and after gave identical on all twelve queries — but only because no row happened to sit in the shifted hour that evening. That is a coincidence, not a proof, and it would have passed just as happily on a build where the naive handling had been dropped entirely. So a change window is created that opened half an offset ago: the wall clock must call it in progress and UTC must call it not yet started. If those two ever agree, the distinction the whole change rests on is not being made.

Beside that

• Twelve live queries run in both forms — the old text on an unpinned connection, the new text on the pinned one — against real data. All twelve identical.
• Nine REST date filters exercised through the real API with a throwaway read-only key, deleted by id afterwards. All returned rows without error.
• Seven pages driven with a signed-in session, checking the response body and not the status code, because a PHP fatal is served as HTTP 200.
• Ten existing test files re-run, 311 assertions, all green — including the calendar-sync suite, which is the one that exercises the naive columns.

---

6. What this does not do

• Existing rows are not corrected. Nothing records which of the 302 routes wrote a given row, and the tables hold rows from both, so a migration would have to guess — and every wrong guess would move a row that was already right. The same call as #116 and #126.
• PHP's own clock is untouched. config.php still pins date_default_timezone_set('Europe/London'), and that is what the wall-clock helpers deliberately read. It is also what produced the email template fault in the same report, where a value was parsed and formatted in the same zone and so came out converted for nobody.
• The naive comparisons use the installation's zone, not the viewer's. An analyst in Vienna reads a change window as "14:00" — because naive values are shown unconverted — while the dashboard decides whether that is in progress using the installation's clock. Making it per-viewer would mean two analysts seeing different counts on a shared dashboard, which is its own problem. Left as it was rather than changed on the way past, and flagged in includes/timezone.php so the question is on the record.
• Setting the server's clock to UTC is still worth doing. It is what the rest of the product assumes, and it makes all of this moot.

---

7. Related

• Notes were stamped with the server's clock — the report this came out of
• Time logged from the right-click menu was two hours out — the same reporter, a different cause
• Timezones and time handling — the three kinds of stored date