Preserve named time zones when persisting ZonedDateTime in JSON

Abierto
  • ¿Algo no está bien?
#4,036 0 comentarios 0 reacciones 0 asignados Ver en GitHub

Los mantenedores suelen responder en 1 día

Nadie ha tomado este issue todavía.

Evaluación

Dificultad
5/5
Tiempo estimado
Más de una semana
Aptitud para principiantes
25/100
Tipo de issue
Nueva funcionalidad
Claridad
Bastante claro
Estado de actividad
Activo
Stack tecnológico
csharp, postgresql
Área
database, databases

Línea de trabajo

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.

Escrito por el modelo de indexación a partir del texto del issue.

Descripción

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.

Lenguaje dominante
C#
Estrellas
1.8k
Forks
268
Merge medio
2 h 35 min
PR fusionados (30 d)
136

Preparar el entorno

Este proyecto no incluye contenedor de desarrollo, Dockerfile ni guía de contribución, así que la configuración corre por tu cuenta: empieza por su README y consulta nuestra guía para la primera contribución para los pasos generales.

Primeros pasos

  1. Lee el issue completo y luego la guía de contribución del proyecto.
  2. Comenta en el issue que vas a ocuparte — evita que dos personas hagan lo mismo.
  3. Haz un fork del repositorio y trabaja en una rama.
  4. Abre un pull request que haga referencia al número del issue.

Más de npgsql/efcore.pg

Todos los issues de npgsql/efcore.pg

Issues similares

Más issues de C#

Recibe los nuevos issues en tu correo

Un resumen breve de issues de GitHub para principiantes.