Table of Contents
#192 — Activity timestamps are stored as the business's local time at the moment of writing rather than as UTC, so every entry written (Resolved)
← Back to bug list · View issue #192 in MantisBT
Reported by: Nour aboulhosn on 2026-08-08T09:14:18+01:00
Assigned to: Claude
Resolved by: Claude on 2026-08-08T14:07:13+01:00
Severity: major
Priority: high
Reproducibility: have not tried
Category: General
Description: Four actions were performed inside the same minute, two with the clock on `Asia/Beirut` and two with it on `Pacific/Midway`. Viewed afterwards under a single timezone, the Beirut pair read `8 Aug 2026, 11:07` and the Midway pair `7 Aug 2026, 21:07` — a 14-hour, date-crossing gap between events that were seconds apart.
If the times were held as UTC and rendered in the current zone, all four would read `11:07`. They do not, so the log's own ordering is wrong: rows written last appear dated a day earlier, and the damage is retroactive and permanent — later corrections cannot recover the true instant.
Timestamps should be stored in UTC and converted to the business timezone only for display. This also matters without anyone touching the setting, since a daylight-saving transition produces the same corruption.
Steps to Reproduce: 1. Settings → Timezone: confirm it is `Asia/Beirut`. Edit any contact so the change is logged. 2. Change the timezone to `Pacific/Midway` and edit the same contact again. 3. Change the timezone back to `Asia/Beirut`. 4. Open Activity. All four rows were written within about a minute of each other. 5. The two written under Beirut read `8 Aug 2026, 11:07`; the two written under Midway read `7 Aug 2026, 21:07` — a day earlier, though they happened *later*. 6. Sort by When. The rows do not fall in the order they occurred, because the column being displayed is not the order the log is held in.
Resolution: What — activity timestamps recorded the business's wall clock at the moment of writing, so entries made seconds apart under different timezone settings were stored hours apart and the log's own ordering became untrue.
Where — `App\Core\AuditLog::record()` (write) and `app/views/activity/index.php` + `ActivityController::export()` (read).
Why — the INSERT never named `created_at`, so it fell to the column's `DEFAULT CURRENT_TIMESTAMP`, which MySQL evaluates in the SESSION's timezone — and `Tenant::applySettings()` sets that to the company's on every request. A DATETIME carries no zone, so what was stored was a wall-clock reading, not a moment. Changing the setting reinterprets every historical row; a daylight-saving transition does the same with nobody touching anything.
Fix — `created_at` is written explicitly as `App\Support\Clock::NOW` (`UTC_TIMESTAMP()`), the constant this codebase already uses for every expiry and throttle window, and rendered through a new `fmt_datetime_utc()` helper that converts to the company's zone at display time. Two names rather than one flag, because swapping them at a call site is a silent double-shift. The `from`/`to` date filter converts in SQL via `CONVERT_TZ(…, '+00:00', @@session.time_zone)` — a numeric offset, because named zones need MySQL's timezone tables, which are not loaded on this host — so “today” still means the business's day. The CSV carries the company-local instant in sortable form so the file and the screen agree.
This does not reverse the standing “no UTC conversion anywhere” rule: that is about CALENDAR DATES, where converting moves an expense across a month boundary. This is a MOMENT.
Verified on the running app: a row written while the company was on `UTC` stored `2026-08-08 12:53:22` and displayed `12:53`; the company was then switched to `Asia/Beirut` through Settings and the SAME row displayed `15:53` — the same instant re-expressed, in correct order beside a new row written under Beirut which itself stored `12:55:05` UTC.
Honest limit — rows written BEFORE this change hold a wall-clock reading whose original zone is not recoverable, so they cannot be corrected. Companies on the default `UTC` are unaffected; for others the historical rows shift by their offset once, which is the ticket's own “the damage is retroactive”.
Activity / Notes
- 2026-08-08T14:07:11+01:00 - Claude: What — activity timestamps recorded the business's wall clock at the moment of writing, so entries made seconds apart under different timezone settings were stored hours apart and the log's own ordering became untrue.
Where — `App\Core\AuditLog::record()` (write) and `app/views/activity/index.php` + `ActivityController::export()` (read).
Why — the INSERT never named `created_at`, so it fell to the column's `DEFAULT CURRENT_TIMESTAMP`, which MySQL evaluates in the SESSION's timezone — and `Tenant::applySettings()` sets that to the company's on every request. A DATETIME carries no zone, so what was stored was a wall-clock reading, not a moment. Changing the setting reinterprets every historical row; a daylight-saving transition does the same with nobody touching anything.
Fix — `created_at` is written explicitly as `App\Support\Clock::NOW` (`UTC_TIMESTAMP()`), the constant this codebase already uses for every expiry and throttle window, and rendered through a new `fmt_datetime_utc()` helper that converts to the company's zone at display time. Two names rather than one flag, because swapping them at a call site is a silent double-shift. The `from`/`to` date filter converts in SQL via `CONVERT_TZ(…, '+00:00', @@session.time_zone)` — a numeric offset, because named zones need MySQL's timezone tables, which are not loaded on this host — so “today” still means the business's day. The CSV carries the company-local instant in sortable form so the file and the screen agree.
This does not reverse the standing “no UTC conversion anywhere” rule: that is about CALENDAR DATES, where converting moves an expense across a month boundary. This is a MOMENT.
Verified on the running app: a row written while the company was on `UTC` stored `2026-08-08 12:53:22` and displayed `12:53`; the company was then switched to `Asia/Beirut` through Settings and the SAME row displayed `15:53` — the same instant re-expressed, in correct order beside a new row written under Beirut which itself stored `12:55:05` UTC.
Honest limit — rows written BEFORE this change hold a wall-clock reading whose original zone is not recoverable, so they cannot be corrected. Companies on the default `UTC` are unaffected; for others the historical rows shift by their offset once, which is the ticket's own “the damage is retroactive”.
Full History
| Date | User | Field | Change |
|---|---|---|---|
| 2026-08-08T09:14:18+01:00 | Nour aboulhosn | issue-new | New Issue |
| 2026-08-08T14:07:11+01:00 | Claude | note-added | Note Added: 0000150 |
| 2026-08-08T14:07:12+01:00 | Claude | tag-added | Tag Attached: done-by-claude |
| 2026-08-08T14:07:13+01:00 | Claude | Assigned To | ⇒ Claudine |
| 2026-08-08T14:07:13+01:00 | Claude | Status | new ⇒ resolved |
Synced automatically from MantisBT issue #192.
