Preserve named time zones when persisting ZonedDateTime in JSON

オープン
  • 何か問題がありますか？
#4,036 コメント 0 件 リアクション 0 件 担当者 0 名 GitHub で見る

メンテナーはふだん 1 日以内に返信

まだ誰も着手していません。

評価

難易度
5/5
見積もり時間
1週間以上
初心者へのやさしさ
25/100
issue の種類
機能追加
明瞭さ
おおむね明確
活発さ
活発
技術スタック
csharp, postgresql
領域
database, databases

調査の方向性

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.

索引モデルが issue の本文から書いたものです。

説明

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.

主要言語
C#
スター
1.8k
フォーク
268
平均マージ
2時間 35分
マージ済み PR（30日）
136

環境構築

このプロジェクトには開発コンテナ、Dockerfile、コントリビューションガイドがありません。まず README を読み、一般的な手順ははじめてのコントリビューションガイドを参照してください。

はじめの一歩

  1. issue を最後まで読み、次にプロジェクトのコントリビューションガイドを読みます。
  2. 着手することを issue にコメントします — 二人が同じ作業をするのを防げます。
  3. リポジトリをフォークし、ブランチを切って変更します。
  4. issue 番号を参照したプルリクエストを送ります。

npgsql/efcore.pg のほかの issue

npgsql/efcore.pg の issue をすべて見る

似ている issue

C# の issue をもっと見る

新しい issue をメールで受け取る

初心者向けの GitHub issue を短くまとめたダイジェスト。