now() at time zone 'utc' written into timestamptz columns skews mt_last_modified and mt_events.timestamp by the server's UTC offset

[jeremydmiller]

Three write paths store now() at time zone 'utc' into a timestamp with time zone column. That expression strips the offset to produce a naive timestamp holding UTC wall-clock time, and assigning a naive timestamp to a timestamptz re-interprets it in the session's TimeZone. On any database whose TimeZone is not UTC the stored instant is wrong by the UTC offset — it is a genuinely different point in time, not a display artifact.

The matching INSERT defaults ((now()), (transaction_timestamp())) are correct, so a row is stamped correctly on insert and then jumps by the offset the first time it goes through one of these paths.

Reproduction

create database tz_probe;
alter database tz_probe set timezone='Europe/Berlin';
\c tz_probe

create table probe(seq bigserial primary key, ts timestamp with time zone not null default (now()));

insert into probe(ts) values ((now() at time zone 'utc'));  -- what the write paths below do
insert into probe(ts) values (default);                     -- what the column DEFAULT does

select seq, ts, now() as server_now, now() - ts as reads_this_far_in_the_past from probe order by seq;

 seq |               ts              |          server_now           | reads_this_far_in_the_past
-----+-------------------------------+-------------------------------+----------------------------
   1 | 2026-08-03 13:41:09.340771+02 | 2026-08-03 15:41:09.340771+02 | 02:00:00
   2 | 2026-08-03 15:41:09.340771+02 | 2026-08-03 15:41:09.340771+02 | 00:00:00

Two hours in the past on a UTC+2 database. West of UTC it skews the other way (America/Chicago → five hours in the future).

Affected sites

Site	Column	Impact
LastModifiedColumn.cs:27 — WriteMetadataInUpdateStatement	mt_last_modified (timestamp with time zone, default (transaction_timestamp()))	Every document UPDATE. Broadest blast radius: IDocumentMetadata.LastModified, ModifiedSince/ModifiedBefore queries, and anything auditing document freshness. Inserted rows are correct; the value shifts on first update.
QuickAppendEventFunction.cs:69	mt_events.timestamp (timestamp with time zone)	AppendMode.Quick only. IEvent.Timestamp, AggregateStreamAsync(streamId, timestamp:), any time-bounded event query or archiving-by-age policy. mt_streams.timestamp in the same function correctly uses bare now().
ReplaceEventOperation.cs:47	mt_events.timestamp	Event-data-protection rewrites re-stamp the event with a skewed timestamp regardless of append mode.

EventAppendMode.Rich (the default) and QuickWithServerTimestamps bind a client DateTimeOffset, which is unambiguous — those paths look correct.

Why it is reachable

Neither Marten nor Npgsql pins the session TimeZone. Npgsql exposes an opt-in Timezone connection-string parameter; when it is not supplied the session inherits the server/database TimeZone GUC. Managed Postgres offerings commonly default to UTC, but on-prem installs and initdb on a non-UTC host frequently do not, and ALTER DATABASE … SET TimeZone is a normal thing for an operator to do.

I have verified the SQL semantics and the code sites above, but have not run an end-to-end Marten test against a non-UTC-TimeZone database — worth confirming before treating the fix as mechanical, in case something upstream pins the session timezone that I have missed.

Suggested fix

Replace all three with bare now() (or transaction_timestamp(), matching the corresponding column DEFAULT). No schema migration is needed and no round-trip behavior changes on a UTC database, so this should be safe to backport.

Worth pinning with a regression test that runs against a database with a non-UTC TimeZone — asserting that a document's LastModified after an update, and a Quick-appended IEvent.Timestamp, are both within a small window of now().

Why this surfaced

Noticed while reviewing #5109, which proposes computing a stuck-gap age as transaction_timestamp() - mt_events.timestamp to bound the SkipStaleGapsDespiteLiveTransactionsAfter cap. That arithmetic inherits this skew directly: on a UTC+2 database in Quick append mode every gap would read two hours older than it is, collapsing a configured cap into "skip as soon as StaleSequenceThreshold passes" and dropping events from live appends. That PR is a separate discussion (see #5108, #5125), but it should not land on top of this column until the column is trustworthy.

[jeremydmiller]

Correcting my own impact assessment above — it was wrong in two of the three sites. The SQL defect is real and I've now proven it end-to-end, but the reachability claims were not verified when I filed this, and two of them do not hold.

What is confirmed

The expression really does skew. Calling the generated mt_quick_append_events from a session at Europe/Berlin and comparing against now() in the same statement:

timestamp                     | now()                         | skew
2026-08-03 15:56:59.769163+02 | 2026-08-03 17:56:59.769163+02 | 02:00:00

Exactly the UTC offset, in a real Marten-generated function. No doubt about the mechanism.

What I got wrong

mt_last_modified is not affected on "every document UPDATE". LastModifiedColumn.WriteMetadataInUpdateStatement is only ever called from PatchOperation — the ordinary Store() upsert path does not go through it. So the exposure is session.Patch<T>(...), not all document updates. That is a much smaller blast radius than I described, and the "broadest" label in the table above is wrong.

Marten's default Quick append does not call the affected function. With AppendMode.Quick, statement logging shows the runtime path emitting per-row inserts with the timestamp bound as a client parameter:

insert into <schema>.mt_events (data, type, mt_dotnet_type, bdata, id, stream_id, version, timestamp, tenant_id, seq_id)
values ($1,$2,$3,$4,$5,$6,$7,$8,$9, nextval('<schema>.mt_events_sequence'))

A client-bound DateTimeOffset is unambiguous, so that path is correct. I verified this by writing through Marten at a Berlin session and getting a correct instant, while a direct call to the function in the same table and same timezone skewed by two hours.

Going further: CreateQuickAppendEventsOperation — the factory that builds the function-calling operation — is assigned in PostgresEventStoreDialect but I can find no reader for it anywhere under src/. So mt_quick_append_events currently looks unreachable at runtime while still being generated into every event schema. Someone who knows the intent behind that seam should confirm; "unused" and "used by a path I failed to find" look identical from the outside, and I would rather be corrected than have this closed on my say-so.

ReplaceEventOperation I have not re-checked. Event data masking is a supported public API, so I currently expect that one to be genuinely reachable, but I am not asserting it without the same evidence I applied to the other two.

Where that leaves it

Still worth fixing — all three expressions are wrong wherever they run, each is a one-line change, and the schema one will bite whoever wires that operation up — but this is a latent-correctness cleanup, not the widespread data-integrity problem the original description implies. Anyone who read the first version and went looking for skewed mt_last_modified values across all their documents can stop: check your patching paths instead, and only if your database's TimeZone is not UTC.

Also worth recording, because it is the reason this survived: the dev container and CI both run at Etc/UTC, where the buggy and correct expressions are byte-identical. No existing test can see this, and none would have caught it.

Apologies for the noise on the original — I should have traced the call sites before writing the impact table rather than after.

[jeremydmiller]

Closing as latent, not worth carrying as an open defect.

Recap of where this landed after the correction above: the SQL mechanism is real (a clean 02:00:00 skew on a Berlin session), but two of the three sites are not the exposure I originally described. The default Quick append path binds a client DateTimeOffset and is correct, mt_last_modified is only reached through session.Patch<T>(), and CreateQuickAppendEventsOperation — the seam that would call the affected function — is assigned in both the Postgres and SQL Server dialects but read by neither, so mt_quick_append_events is dead code in Marten and Polecat alike.

That leaves a wrong-but-unreachable expression in generated schema plus a narrow patching-path exposure that only manifests on a database whose TimeZone is not UTC. Not worth a schema delta for every store to correct.

Recorded here rather than fixed so it is findable if someone revives the function seam, or if a non-UTC deployment ever reports odd mt_last_modified values from patching. The reproduction is in the thread: set an Npgsql Timezone= on the connection, and compare the written column against now() in the same statement.

Worth noting for anyone auditing similar code: the dev container and CI both run at Etc/UTC, where the buggy and correct forms are byte-identical, so no test in the suite can see this class of bug.