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

Aperta
  • Qualcosa non va?
#4,085 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à
3/5
Tempo stimato
1-2 giorni
Idoneità per principianti
70/100
Tipo di issue
Bug
Chiarezza
Specificata chiaramente
Stato di attività
Attiva
Stack tecnologico
csharp, postgresql, sql
Ambito
databases

Direzione di ricerca

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.

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

Descrizione

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.
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.