NodaTime time-zone conversions disagree with NodaTime: ambiguous local times, date parts in the session time zone, `Instant.InZone().ToInstant()`

Offen
  • Stimmt etwas nicht?
#4,085 0 Kommentare 0 Reaktionen 0 zugewiesene Personen Auf GitHub ansehen

Maintainer antworten meist innerhalb von 1 Tag

Dieses Issue hat noch niemand übernommen.

Bewertung

Schwierigkeit
3/5
Geschätzter Aufwand
1-2 Tage
Anfängerfreundlichkeit
70/100
Issue-Typ
Bug
Klarheit
Klar beschrieben
Aktivitätsstatus
Aktiv
Tech-Stack
csharp, postgresql, sql
Bereich
databases

Rechercherichtung

Start by reading src/EFCore.PG.NodaTime/Query/Internal/NpgsqlNodaTimeMethodCallTranslatorPlugin.cs (lines 179-180, 201-210) and NpgsqlNodaTimeMemberTranslatorPlugin.cs (lines 309-319) to see how LocalDateTime and Instant are translated. Run the repro from the issue and the existing test LocalDateTimeTranslationsTest.InZoneLeniently_ToInstant. Done means the generated SQL matches NodaTime semantics for ambiguous times, zoned date parts, and Instant.InZone().ToInstant() identity, with tests updated.

Vom Indexierungsmodell aus dem Issue-Text verfasst.

Beschreibung

Environment
PostgreSQL 18.6 (postgres:18), session TimeZone Etc/UTC
.NET 10 runtime 10.0.11, Npgsql.EntityFrameworkCore.PostgreSQL.NodaTime 10.0.3, EF Core 10.0.4
.NET 11 runtime 11.0.0-rc.2, Npgsql.EntityFrameworkCore.PostgreSQL.NodaTime 11.0.0-rc.2, EF Core 11.0.0-rc.2
Also Npgsql 10.0.3 on both lines, NodaTime 3.2.2, macOS arm64

Both lines print the same output. main at 150011d has the same code.

Three translations around AT TIME ZONE give answers that differ from NodaTime, without an error.

LINQ Row .NET PostgreSQL SQL
r.Local.InZoneLeniently(Tzdb["Europe/Berlin"]).ToInstant() 2026-10-25 02:30, which happens twice 2026-10-25T00:30:00Z 2026-10-25T01:30:00Z r."Local" AT TIME ZONE 'Europe/Berlin'
r.Local.InZoneLeniently(Tzdb["Europe/Berlin"]).Hour 2026-06-15 10:30 10 8 date_part('hour', r."Local" AT TIME ZONE 'Europe/Berlin')::int
r.Instant.InZone(Tzdb["Europe/Berlin"]).ToInstant() == r.Instant any every row no row r."Instant" AT TIME ZONE 'Europe/Berlin' = r."Instant"
Repro 
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
using NodaTime;
using Npgsql;

await using var context = new TestContext();
await context.Database.EnsureDeletedAsync();
await context.Database.EnsureCreatedAsync();

context.Rows.Add(new Row { Local = new LocalDateTime(2026, 10, 25, 2, 30), Instant = Instant.FromUtc(2026, 6, 15, 12, 0) }); // 02:30 happens twice in Berlin
context.Rows.Add(new Row { Local = new LocalDateTime(2026, 6, 15, 10, 30), Instant = Instant.FromUtc(2026, 6, 15, 12, 0) });
await context.SaveChangesAsync();
var rows = await context.Rows.AsNoTracking().OrderBy(r => r.Id).ToListAsync();

// 1. An ambiguous local time
Console.WriteLine($"InZoneLeniently(Berlin).ToInstant(): .NET {rows[0].Local.InZoneLeniently(DateTimeZoneProviders.Tzdb["Europe/Berlin"]).ToInstant()}, database {await context.Rows.Where(r => r.Id == 1).Select(r => r.Local.InZoneLeniently(DateTimeZoneProviders.Tzdb["Europe/Berlin"]).ToInstant()).SingleAsync()}");

// 2. Date parts of the zoned value are taken in the session time zone (UTC, set in OnConfiguring), not in Berlin
Console.WriteLine($"InZoneLeniently(Berlin).Hour: .NET {rows[1].Local.InZoneLeniently(DateTimeZoneProviders.Tzdb["Europe/Berlin"]).Hour}, database {await context.Rows.Where(r => r.Id == 2).Select(r => r.Local.InZoneLeniently(DateTimeZoneProviders.Tzdb["Europe/Berlin"]).Hour).SingleAsync()}");

// 3. Instant.InZone(z).ToInstant() is the identity in .NET
Console.WriteLine($"Instant.InZone(Berlin).ToInstant() == Instant: .NET {rows.Count(r => r.Instant.InZone(DateTimeZoneProviders.Tzdb["Europe/Berlin"]).ToInstant() == r.Instant)}, database {await context.Rows.CountAsync(r => r.Instant.InZone(DateTimeZoneProviders.Tzdb["Europe/Berlin"]).ToInstant() == r.Instant)}");

public class TestContext : DbContext
{
    public DbSet<Row> Rows => Set<Row>();

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseNpgsql(new NpgsqlConnectionStringBuilder(Environment.GetEnvironmentVariable("Test__Npgsql__DefaultConnection")) { Timezone = "UTC" }.ConnectionString, o => o.UseNodaTime())
            .LogTo(Console.WriteLine, LogLevel.Information)
            .EnableSensitiveDataLogging();
}

public class Row
{
    public int Id { get; set; }
    public LocalDateTime Local { get; set; }
    public Instant Instant { get; set; }
}

Output, the same on both lines:

InZoneLeniently(Berlin).ToInstant(): .NET 2026-10-25T00:30:00Z, database 2026-10-25T01:30:00Z
InZoneLeniently(Berlin).Hour: .NET 10, database 8
Instant.InZone(Berlin).ToInstant() == Instant: .NET 2, database 0
  • Ambiguous local times. For a local time that happens twice, PostgreSQL picks the later instant and NodaTime's lenient resolver the earlier one. A skipped local time (2026-03-29 02:30 in Berlin) agrees. The translation is NpgsqlNodaTimeMethodCallTranslatorPlugin.cs#L201-L210. LocalDateTimeTranslationsTest.InZoneLeniently_ToInstant uses 2018-04-20 10:31, which is not near a transition. Matching NodaTime needs the earlier candidate in the ambiguous case. If that's not worth the SQL, a note in the translation table would at least make the difference visible.
  • Date parts of a zoned local time. timestamp AT TIME ZONE z is a timestamptz, an instant, but TranslateZonedDateTime types it as LocalDateTime (NpgsqlNodaTimeMemberTranslatorPlugin.cs#L309-L319). date_part, the ::date cast and comparisons then go through the session time zone, so the answer is only right when the session TimeZone equals z. For a LocalDateTime operand, the local value in that zone is the operand itself, apart from skipped times.
  • Instant.InZone(z).ToInstant(). Instant.InZone builds the same pending expression (NpgsqlNodaTimeMethodCallTranslatorPlugin.cs#L179-L180), and ToInstant then applies AT TIME ZONE as if the operand were a LocalDateTime. For an Instant operand the result is the operand.
Vorherrschende Sprache
C#
Sterne
1.8k
Forks
268
Ø Merge
2 Std. 35 Min.
Gemergte PRs (30 T.)
136

Entwicklungsumgebung

Dieses Projekt bietet weder Dev-Container noch Dockerfile noch Beitragsleitfaden – die Einrichtung liegt bei Ihnen. Beginnen Sie mit der README; die allgemeinen Schritte stehen in unserem Leitfaden für den ersten Beitrag.

Erste Schritte

  1. Lesen Sie das ganze Issue und danach den Beitragsleitfaden des Projekts.
  2. Schreiben Sie ins Issue, dass Sie es übernehmen — das erspart doppelte Arbeit.
  3. Forken Sie das Repository und arbeiten Sie in einem Branch.
  4. Öffnen Sie einen Pull Request, der die Issue-Nummer nennt.

Mehr aus npgsql/efcore.pg

Alle Issues in npgsql/efcore.pg

Ähnliche Issues

Weitere Issues zu C#

Neue Issues direkt in Ihr Postfach

Eine kurze Übersicht über anfängerfreundliche GitHub-Issues.