Preserve named time zones when persisting ZonedDateTime in JSON

Aperta
  • Qualcosa non va?
#4,036 0 commenti 0 reazioni 0 assegnatari Vedi su GitHub

I maintainer di solito rispondono entro 1 giorno

Nessuno ha ancora preso questa issue.

Valutazione

Difficoltà
5/5
Tempo stimato
Più di una settimana
Idoneità per principianti
25/100
Tipo di issue
Funzionalità
Chiarezza
Abbastanza chiara
Stato di attività
Attiva
Stack tecnologico
csharp, postgresql
Ambito
database, databases

Direzione di ricerca

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.

Scritto dal modello di indicizzazione a partire dal testo della issue.

Descrizione

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.

Lingua principale
C#
Stelle
1.8k
Fork
268
Merge medio
2h 35m
PR unite (30g)
136

Preparare l'ambiente

Questo progetto non fornisce container di sviluppo, Dockerfile né guida per i contributori, quindi l'ambiente è a tuo carico: parti dal suo README e consulta la nostra guida al primo contributo per i passaggi generali.

Come iniziare

  1. Leggi tutta la issue e poi la guida ai contributi del progetto.
  2. Commenta sulla issue per dire che te ne occupi tu — evita che due persone facciano lo stesso lavoro.
  3. Fai un fork del repository e lavora su un branch.
  4. Apri una pull request che faccia riferimento al numero della issue.

Altre issue di npgsql/efcore.pg

Tutte le issue di npgsql/efcore.pg

Issue simili

Altre issue su C#

Ricevi le nuove issue nella tua casella

Un breve riepilogo di issue GitHub adatte ai principianti.