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

Aberta
  • Algo errado?
#4,085 0 comentários 0 reações 0 responsáveis Ver no GitHub

Mantenedores costumam responder em até 1 dia

Ninguém assumiu esta issue ainda.

Avaliação

Dificuldade
3/5
Tempo estimado
1-2 dias
Facilidade para iniciantes
70/100
Tipo de issue
Bug
Clareza
Claramente especificada
Status de atividade
Ativa
Stack de tecnologia
csharp, postgresql, sql
Domínio
databases

Direção de pesquisa

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.

Escrita pelo modelo de indexação a partir do texto da issue.

Descrição

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.
Linguagem predominante
C#
Estrelas
1.8k
Forks
268
Merge médio
2h 35min
PRs com merge (30d)
136

Preparar o ambiente

Este projeto não oferece contêiner de desenvolvimento, Dockerfile nem guia de contribuição, então a configuração fica por sua conta: comece pelo README e veja nosso guia da primeira contribuição para os passos gerais.

Primeiros passos

  1. Leia a issue inteira e depois o guia de contribuição do projeto.
  2. Comente na issue dizendo que vai assumir — evita que duas pessoas façam o mesmo trabalho.
  3. Faça um fork do repositório e trabalhe em uma branch.
  4. Abra um pull request que referencie o número da issue.

Mais de npgsql/efcore.pg

Todas as issues de npgsql/efcore.pg

Issues semelhantes

Mais issues de C#

Receba novas issues na sua caixa de entrada

Um resumo curto de issues do GitHub para quem está começando.