Preserve named time zones when persisting ZonedDateTime in JSON

Start with TimestampTzZonedDateTimeMapping and the JSON reader/writer used in the repro, then inspect the prior implementation and tests at commit 817b4fff. First decide the persisted representation and compatibility policy, including existing data, query behavior, and precision; then trace JSON projections, predicates, recordset fields, collections, and containment. Done means named zones and offsets round-trip while existing deployments and query semantics are explicitly addressed.

Description

ZonedDateTime JSON currently stores the local date/time and UTC offset, but not the named time zone. A value in Europe/Berlin is therefore indistinguishable from a value in another zone with the same offset. Deserialization cannot recover the original zone identity or its daylight-saving rules.

Split out of #4021. That issue covers the reader throwing on the existing writer's nonzero-offset output; #4022 fixes that failure without changing the persisted format, returning a fixed-offset zone. This issue covers the separate capability of preserving the original named zone and is not fixed by #4022.

Minimal repro

Run this as a .NET 11 console application referencing src/EFCore.PG.NodaTime/EFCore.PG.NodaTime.csproj from this repository. No PostgreSQL server is required.

using System;
using NodaTime;
using Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal;

var readerWriter = TimestampTzZonedDateTimeMapping.Default.JsonValueReaderWriter!;

foreach (var zoneId in new[] { "Europe/Berlin", "Europe/Paris" })
{
    var value = new LocalDateTime(2018, 4, 20, 10, 31, 33)
        .InZoneLeniently(DateTimeZoneProviders.Tzdb[zoneId]);
    Console.WriteLine(value.Zone.Id);
    Console.WriteLine(readerWriter.ToJsonString(value));
}

Both values produce the same JSON string (the plus sign may be JSON-escaped as \u002B):

Europe/Berlin
"2018-04-20T10:31:33+02"
Europe/Paris
"2018-04-20T10:31:33+02"

After the compatible reader fix in #4022, this representation can be read as a fixed-offset zone, but neither original zone ID can be recovered from it.

Desired behavior

Provide a way to persist and recover the original named zone for ZonedDateTime JSON values. Retain the explicit offset so ambiguous local times during a daylight-saving overlap can be disambiguated. This concerns JSON document round-tripping; PostgreSQL timestamptz columns do not preserve named zones.

Proposed approach and compatibility decisions

An earlier implementation in #4022 used a JSON-specific NodaTime pattern, ZonedDateTimePattern.ExtendedFormatOnlyIso.WithZoneProvider(DateTimeZoneProviders.Tzdb), producing text such as:

2018-04-20T10:31:33 Europe/Berlin (+02)

It was removed in commit 8681ae17 because it was not a backward-compatible bug fix. The proposed representation remains a candidate, not an approved design:

• Older providers cannot read the new output, even for UTC. A fallback in the new reader does not make mixed-version deployments or rollbacks safe. Decide whether this should be opt-in or a documented breaking change, and define existing-data/migration behavior.
• The zone-bearing string is not valid PostgreSQL timestamptz input. EF scalar JSON projections/predicates, typed json[b]_to_recordset fields, primitive collections, and representation-sensitive containment need deliberate query semantics. EF-only conversions would not repair application-written SQL, views, or indexes that cast the JSON text directly.
• Keep SQL and embedded PostgreSQL literal formatting separate from JSON representation changes.
• The historical pattern also expanded precision from six fractional digits to nanoseconds. That expansion is not required for named-zone persistence and should be decided separately. PostgreSQL rounds to microseconds while Npgsql parameter encoding truncates toward the PostgreSQL epoch; blindly expanding precision can make equality predicates miss saved values.
• Clearly distinguish full-document named-zone round-tripping from scalar timestamptz query results, which retain instant/UTC semantics rather than named-zone identity.

Prior implementation

The complete reverted implementation and its tests can be inspected at 817b4fff. It added mapping-specific JSON extraction conversion, a query postprocessor, a containment optimization exclusion, and precision normalization. Those changes are historical reference, not a requirement to reintroduce that architecture. The follow-up should choose the simplest justified representation and compatibility policy before implementation.