Issue 126 Notes Stamped With The Servers Clock

Notes were stamped with the server's clock, not UTC (issue #126)

Reported by mbsouth · Fixed in #1444

A note added to a ticket displayed a time ahead of the moment it was written — two hours ahead on a system set to Vienna. Everything else on the same ticket was right.

This is the third timezone report from the same person, and the previous two are worth reading beside it: time logged from the right-click menu and the portal dashboard. All three have the same shape and none of them shares a cause. That is the useful thing about this set.

---

1. What you saw

Write a note at 16:00. The note appears in the ticket timestamped 18:00.

The time entries directly above it, the emails, the audit trail and the ticket's own dates were all correct. Only notes were wrong, and they were wrong in the direction that makes a ticket read out of order: a note written before a reply can appear after it.

---

2. The contrast was the diagnosis

FreeITSM stores every datetime in UTC and converts on display. If a value is wrong on screen, either it was written wrong or it is being read wrong, and the fact that only notes were affected rules out the reader immediately — the same formatDateTime() renders the time entries sitting a few pixels away.

So the note was being written wrong. Here is the whole fault:

// before
INSERT INTO ticket_notes (ticket_id, analyst_id, note_text, is_internal)
VALUES (?, ?, ?, ?)

The date column is simply not in the list. When an INSERT does not name a column, the column's own default fills it in, and this one is declared:

`created_datetime` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,

CURRENT_TIMESTAMP is not UTC. MySQL evaluates it in the connection's session time zone, which is SYSTEM — the server's own clock. So the note was written as a local wall clock, and then read back by code that correctly treats stored datetimes as UTC instants. The value gains the server's offset on the way to the screen.

Why it is "two hours" for one person and nothing for another

The error is the server's own UTC offset, not a fixed amount:

Server timezone	Note written at	Stored	Displayed	Out by
Europe/Vienna (CEST)	16:00	16:00	18:00	+2h
Europe/London (BST)	16:00	16:00	17:00	+1h
Europe/London (GMT)	16:00	16:00	16:00	none
UTC	16:00	16:00	16:00	none

An installation whose server runs UTC — which is most of them, and every sensible container image — cannot see this bug at all. In the UK it appears in March and vanishes in October.

---

3. The answer was already two lines below the fault

This is the part worth keeping. The method that writes the note continues:

$conn->prepare("INSERT INTO ticket_notes (ticket_id, analyst_id, note_text, is_internal) …");
$noteId = (int)$conn->lastInsertId();
$conn->prepare("UPDATE tickets SET updated_datetime = UTC_TIMESTAMP() WHERE id = ?")->execute([$ticketId]);

UTC_TIMESTAMP() — in the same method, in the same transaction, two lines further down. Nobody misunderstood the invariant. The INSERT just did not list the column, and a missing column produces no error, no warning and a perfectly plausible value.

You can see both halves in a single row of the live database. Note 107 below was written by the broken path; the ticket update beside it was written by the line quoted above, microseconds later:

note #107  created=2026-09-01 23:14:26   ticket.updated=2026-09-01 22:14:26

One hour apart, from the same request. Nothing else can explain that gap, which makes it about as clean a reproduction as a bug ever offers.

---

4. It was one route out of five

The convention here is to sweep before writing anything up. ticket_notes is written from five places, and four of them were already right:

Route
includes/services/tickets.php — an analyst typing a note	❌ the bug
api/tickets/save_merge_summary.php — the summary written when tickets are merged	✅ UTC_TIMESTAMP()
api/messaging/ai_summary.php — an AI summary saved to the ticket	✅ UTC_TIMESTAMP()
includes/integrations/integrations.php — a comment arriving from a linked tracker	✅ UTC_TIMESTAMP()
api/integrations/send_attachment.php — the note recording a sent attachment	✅ UTC_TIMESTAMP()

The one that was wrong is the one every analyst uses a hundred times a day; the four that were right are the occasional ones. That is not bad luck. The four were all written later, by somebody adding a row to a table they did not design, who therefore looked at the column list and filled it in. The original insert was written alongside the table, when the default felt like the point of having one.

---

5. The fix

INSERT INTO ticket_notes (ticket_id, analyst_id, note_text, is_internal, created_datetime)
VALUES (?, ?, ?, ?, UTC_TIMESTAMP())

The column is now named, so the default never fires.

Existing notes are not corrected. The table holds rows from all five routes and nothing records which route wrote which, so a migration would have to guess — and every guess it got wrong would move a note that was already correct. The same decision was taken for issue #116, for the same reason. Notes written from now on are right; older ones keep whatever they have.

---

6. 📁 The files involved

File	What changed
includes/services/tickets.php	createNote() names created_datetime and stamps UTC_TIMESTAMP()

🗄 Deliberately unchanged

database/freeitsm.sql	The DEFAULT CURRENT_TIMESTAMP stays. MySQL has no way to declare a column default in UTC, so removing it would only turn a wrong value into a null one. The column belongs in the INSERT.
assets/js/inbox.js	formatDateTime() was converting correctly the whole time.
The other four note routes	Already stamped UTC_TIMESTAMP().

---

7. How it was verified

A live write through the real endpoint, not a unit test — the fault lives in the gap between an INSERT statement and a column default, and nothing that mocks the database can see it.

A note posted to api/tickets/save_note.php on a server whose clock is one hour ahead of UTC:

note 108 created_datetime : 2026-09-01 22:21:10
UTC_TIMESTAMP()           : 2026-09-01 22:21:37
NOW()  (server local)     : 2026-09-01 23:21:37
saved - UTC               : -00:00:27      <- the 27s the test itself took
saved - local             : -01:00:27

And the control, which is the half that proves it. A note written seven minutes earlier, before the fix, on the same server, read back the same way:

note 107 created_datetime : 2026-09-01 23:14:26
saved - UTC               : +00:52:49      <- an hour out, as reported

Rendered for a reader in Vienna, the new note lands 27 seconds from the true Vienna clock and the old one lands 3,169 seconds away. Without that second measurement the first proves nothing: a green result on the fixed path is equally consistent with the bug never having existed.

The probe note was deleted afterwards, by id, after re-reading its text to confirm it was the right row.

---

8. ⚠️ What this find implied — now fixed, in #1446

The interesting question is not "why was this note wrong" but "where else is a datetime column filled in by its own default?" — because every one of those is written in the server's zone.

A sweep of the schema against every INSERT in the codebase says: 220 tables carry 272 auto-stamped datetime columns, and 302 INSERT statements let one of them fire.

That number is much less alarming than it looks, and it is worth being precise about why:

• Most of those columns are never shown to anybody. ticket_time_entries is in the list, but only its created_datetime and updated_datetime are — the entry_datetime that the screen actually displays is stamped explicitly with gmdate(), which is why time entries read correctly. A column that is wrong and never displayed and never compared is a latent fault, not a live one.
• On a server running UTC every one of them is correct, which is the normal deployment.

But some of them will be doing what notes were doing.

The structural answer was taken, as #1446. Every connection now opens with its session time zone pinned to +00:00, so CURRENT_TIMESTAMP and UTC_TIMESTAMP() are the same instant and a forgotten column cannot be wrong. That corrects all 302 at once and every one written from here on.

It is not a one-line change, because a handful of dates in FreeITSM are deliberately not instants — and those had to be given a wall clock explicitly before the pin could go in, or the fix would have introduced its own bug. That story is worth its own page: Storing every date in UTC.

---

9. What this means for you

• If your server's clock is UTC, you were never affected, and nothing about your installation changes.
• If it is not, notes written from update #1444 onwards are correct. Older ones are out by whatever your server's offset was when they were written, and are left alone rather than guessed at.
• Setting the server's own clock to UTC is worth doing regardless. It is what the rest of the product assumes.

---

10. Related

• Time logged from the right-click menu was two hours out — the same reporter, the previous one, a completely different cause
• The portal dashboard showed the wrong time — a reading fault, where this is a writing one
• Timezones and time handling — the three kinds of stored date, and which helper reads each