Stored Timestamp Is A Local Wall Clock

The column holds a wall clock, not an instant

Two halves, each defensible on its own, written months apart. The producer captures the device's wall clock:

// adapted
fun now(): LocalDateTime = Clock.System.now().toLocalDateTime(TimeZone.currentSystemDefault())

The ORM's converter then encodes that wall clock as if it were UTC, and decodes it the same way:

// adapted — an exact inverse of itself
@TypeConverter fun dateToTimestamp(date: LocalDateTime?): Long? =
    date?.toInstant(TimeZone.UTC)?.toEpochMilliseconds()

@TypeConverter fun fromTimestamp(value: Long?): LocalDateTime? =
    value?.let { Instant.fromEpochMilliseconds(it).toLocalDateTime(TimeZone.UTC) }

So the number in the column is not the instant the event happened. At +07:00 it is that instant plus seven hours: the wall clock the user was looking at, carried on an epoch-millis scale. Read back through the converter it is exactly right, and that is the only path on which it is.

The mechanism, exactly

The converter is selected by the target type of the field being filled, not by the column, not by an annotation you can see at the read site. One word in a projection decides which of these you get:

Nothing in the type system objects: the column really is an integer, Long really is what it holds, and a millis-shaped field is the honest-looking choice in a hand-written result class.

Traps

Every total still adds up, which is why this ships. The encoding shifts every row by the same constant while the device's offset is constant, and preserves order within that, so COUNT, SUM, MIN/MAX and any BETWEEN whose bounds went through the same converter are all unaffected. A DST transition makes the shift piecewise: the repeated hour interleaves wrongly under ORDER BY, the skipped hour holds no rows at all, and a difference of two stored values across the transition is off by the offset change — the totals still add. Only field extraction — hour, date, day of week — reads wrong, and only after the second decode. No assertion fails, no total looks off, no row is missing. Measured here: the busiest hour landed at 03:00 instead of 20:00, and 34% of events were attributed to the following day. What caught it was a human asking who does this at three a.m.?

The error is exactly zero in one time zone, and that is usually the author's. A double decode at UTC+0 is a no-op. Written and reviewed in London, it is correct; shipped, it is wrong by the user's own offset, in the direction that makes late-evening activity look like early morning.

The fix is a type, not a time zone. Ask for LocalDateTime in the projection and the decision disappears along with the bug — there is no zone left to pick wrongly, and no reader has to know how the column is encoded:

// adapted — the whole fix is the declared type of the first field
data class ActivitySample(
    @ColumnInfo(name = "timestamp") val timestamp: LocalDateTime,
    @ColumnInfo(name = "listenedSecond") val listenedSecond: Long,
)

"Decode the raw number with TimeZone.UTC" produces the same answer today and leaves the trap armed for the next projection someone writes.

Do not fix the column. The round trip is exact and every existing query — every range bound, every sort — already agrees with the encoding. Re-encoding to true instants is a migration that shifts every historical row, and it is a different decision from the one in front of you. Fix the read; schedule the encoding change on its own, if at all.

Range bounds must travel the same path as the column. Here they do: the range parameters are declared LocalDateTime, so the converter encodes them the same way and the comparison happens on one scale. A window built as raw millis and passed to a converted column silently searches a shifted span — the same class of error, moved to the other side of the comparison, and just as total-preserving.

Wall-clock storage is right for calendar questions and wrong for elapsed-time ones. "What did I do on Tuesday" wants the wall clock; "how long has this token been valid" wants an instant, and this encoding gets it wrong across any offset change. kotlinx-datetime-helper-kit covers the other remediation — store instants, convert to local only for display — and the two are not interchangeable. Mixing both in one table is worse than committing to either.

A projection is not covered by the entity's tests. Whatever exercises the entity round trip exercises the converter; a hand-written result class for one query does not go near it. Every @Query that returns a bespoke data class is a fresh chance to opt out, one field at a time.

Wall-clock numbers from two devices are on two different scales. Inside one database the encoding is a constant shift, so everything is self-consistent. Across databases it is not: the same instant written at UTC+7 and at UTC+0 produces numbers seven hours apart, so merging a backup from another device — or from the same device after the user moved — interleaves the two histories in the wrong order, silently and irreversibly. Nothing about a single-device restore reveals this; only a merge does. If these rows will ever be merged or synced, the wall clock is the wrong thing to store and this is the decision to revisit before the feature exists, not after.

Verifying it

1. Find the disagreement in one shot. These are the only two zone rules that can exist, and a producer in the second list feeding a converter in the first is the shape described above:

   grep -rn --include='*.kt' -E "toInstant\(TimeZone\.|toLocalDateTime\(TimeZone\." . \
     | grep -v '/build/' | sed -E 's/.*(TimeZone\.[A-Za-z()]*).*/\1/' | sort | uniq -c
   

   Here: 5 × TimeZone.UTC (the converter and its siblings) against 15 × currentSystemDefault() (the producers). Two rules, one table.

2. List every hand-written projection over a converted column, and read the declared type:

   grep -rn --include='*.kt' -E "ColumnInfo\(name = \"timestamp\"\)" . | grep -v '/build/'
   

   Every hit must say LocalDateTime. One saying Long is the bug, silent and total-preserving.

3. Run the discriminating query instead of reasoning about it. Because the column is a local wall clock, millis-since-local-midnight is plain arithmetic — no zone function, no process dependency:

   SELECT (timestamp % 86400000) / 3600000 AS local_hour, COUNT(*)
     FROM activity_event GROUP BY local_hour ORDER BY 2 DESC LIMIT 3;
   

   Compare that histogram against what the screen draws. If the screen's peak is offset from this one by your own UTC offset, the read side decoded twice. If they agree, the projection is typed correctly — and this query is also the check to keep, because it keeps working after someone adds the next projection.