DateTimeOffset loses its offset on BSON round-trip (WriteDateTimeOffset/ReadDateTimeOffset)

[mrdevrobot]

Summary

BsonValue.FromDateTimeOffset / BsonSpanWriter.WriteDateTimeOffset serialize a DateTimeOffset as a plain BSON DateTime (8-byte UTC millisecond timestamp). The offset itself is never written to disk, so BsonSpanReader.ReadDateTimeOffset() always reconstructs the value with Offset = 0.

The absolute instant survives the round-trip; the offset does not. Any consumer that formats or reads .DateTime off the result without an explicit ToLocalTime()/ToOffset() call gets a value that's silently wrong by exactly the original UTC offset — e.g. a value written as 20:00+02:00 (CEST) reads back as 18:00+00:00.

Repro

var buffer = new byte[256];
var writer = new BsonSpanWriter(buffer, keyMap);
writer.WriteDateTimeOffset("ts", new DateTimeOffset(2026, 6, 15, 20, 0, 0, TimeSpan.FromHours(2)));

var reader = new BsonSpanReader(buffer, keys);
var readBack = reader.ReadDateTimeOffset();

readBack.Offset;      // TimeSpan.Zero - expected +02:00
readBack.ToString();  // "2026-06-15T18:00:00+00:00" - expected "...20:00:00+02:00"

Root cause

• BsonValue.cs: FromDateTimeOffset → new(BsonType.DateTime, BitConverter.Int64BitsToDouble(value.ToUnixTimeMilliseconds())) — only the UTC instant is kept.
• BsonSpanWriter.cs: WriteDateTimeOffset writes the same 8-byte layout as WriteDateTime, tagged BsonType.DateTime — indistinguishable on the wire from a plain DateTime field.
• BsonSpanReader.cs: ReadDateTimeOffset() → DateTimeOffset.FromUnixTimeMilliseconds(ms) — no offset in the input, so the result is always Offset = 0.

Impact

Every DateTimeOffset-typed field ever persisted through BsonSpanWriter/BsonValue loses its offset on the very first write. Not a corruption in transit — a permanent property of the storage format as it exists today.

Constraint

Any fix must stay backward-compatible with databases written before it: existing data has DateTimeOffset fields tagged as plain BsonType.DateTime (8 bytes, offset already unrecoverable). A fix cannot retroactively "repair" that data, but reading it must keep working exactly as it does today (Offset = 0, same as now — no crash, no regression), while newly-written values should start round-tripping the offset correctly.

Proposed direction

Add a distinct BsonType.DateTimeOffset wire tag (BSON spec leaves 0x14 unassigned) with a 10-byte layout: the existing 8-byte UTC millisecond timestamp + a 2-byte signed offset-in-minutes trailer. WriteDateTimeOffset starts tagging with the new type; every place that currently branches on BsonType.DateTime (skip-length, BsonValue accessors/equality/comparison, BLQL predicate/index-key building, projection, schema generation, the source-generated entity readers) needs a matching BsonType.DateTimeOffset arm, generally delegating to the same instant-based logic so ordering/index behavior is unaffected — only display/read-back of the offset changes. A reader encountering the legacy BsonType.DateTime tag on a DateTimeOffset-typed field keeps decoding it exactly as today (Offset = 0), so old databases keep working unmodified; only values written after the fix gain a correct offset.

Have a working fix along these lines, ready as a PR.