NodaTime: LocalDate - LocalDate returns days only, and .Year of BCE dates is off by one

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.

LINQ	Row	.NET	PostgreSQL	SQL
`r.To - r.From`	2026-01-31 − 2025-12-31	`P1M`	`P31D`	`make_interval(days => r."To" - r."From")`
`(r.To - r.From).Months == 1`	same	match	no match	`date_part('month', make_interval(days => r."To" - r."From"))::int = 1`
`r.Bce.Year`	`new LocalDate(-100, 12, 31)`	−100	−101	`date_part('year', r."Bce")::int`

Repro

using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
using NodaTime;

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

context.Rows.Add(new Row { From = new LocalDate(2025, 12, 31), To = new LocalDate(2026, 1, 31), Bce = new LocalDate(-100, 12, 31) });
await context.SaveChangesAsync();
var row = await context.Rows.AsNoTracking().SingleAsync();

// 1. NodaTime's LocalDate - LocalDate is Period.Between in years, months and days
Console.WriteLine($"To - From: .NET {row.To - row.From}, database {await context.Rows.Select(r => r.To - r.From).SingleAsync()}");
Console.WriteLine($"(To - From).Months == 1: .NET {((row.To - row.From).Months == 1 ? 1 : 0)}, database {await context.Rows.CountAsync(r => (r.To - r.From).Months == 1)}");

// 2. ISO year -100 is 101 BC; PostgreSQL has no year 0
Console.WriteLine($"Year of LocalDate(-100, 12, 31): .NET {row.Bce.Year}, database {await context.Rows.Select(r => r.Bce.Year).SingleAsync()}");

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

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

public class Row
{
    public int Id { get; set; }
    public LocalDate From { get; set; }
    public LocalDate To { get; set; }
    public LocalDate Bce { get; set; }
}

Output, the same on both lines:

To - From: .NET P1M, database P31D
(To - From).Months == 1: .NET 1, database 0
Year of LocalDate(-100, 12, 31): .NET -100, database -101

Date subtraction. Incorrect translation of NodaTime LocalDate subtraction #1979 fixed the type of LocalDate - LocalDate (an int vs Period) with make_interval(days => ...) (NpgsqlSqlTranslatingExpressionVisitor.cs#L231-L255). NodaTime's operator is Period.Between in years, months and days, so any span of a month or more comes out different, and so do .Months/.Years. PostgreSQL's age(to, from) returns years, months and days and is much closer.
BCE years. NodaTime numbers years astronomically, so year 0 is 1 BC. PostgreSQL has no year 0, so date_part('year', ...) of a BCE date is one less (NpgsqlNodaTimeMemberTranslatorPlugin.cs#L228). CASE WHEN y < 0 THEN y + 1 ELSE y END would match.

AI Triage

The below is an AI-generated analysis and may contain inaccuracies.

Confirmed two provider query-translation bugs. The supplied repro produces exactly the reported results on PostgreSQL 18.6, with session time zone Etc/UTC:

Expression	.NET	Database
`To - From` for 2026-01-31 minus 2025-12-31	`P1M`	`P31D`
Count where `(To - From).Months == 1`	1	0
`Year` of `new LocalDate(-100, 12, 31)`	-100	-101

Cause. The LocalDate subtraction translation generates make_interval(days => "To" - "From"). This supplies an interval of elapsed days, whereas NodaTime's operator returns a calendar period with years, months and days. The NodaTime year-member translation extracts PostgreSQL's year without adjusting BCE numbering. ISO year -100 is 101 BC, for which PostgreSQL returns -101. Insertion and materialization of the original LocalDate succeed; the incorrect result arises in the server-side member translation.

Version/regression check. Both mismatches reproduced with all of these package combinations, using NodaTime 3.2.2:

Npgsql EF NodaTime provider	EF Core	Target
8.0.11	8.0.11	.NET 8
10.0.2	10.0.4	.NET 10
10.0.3	10.0.4	.NET 10
10.0.3	10.0.13	.NET 10
11.0.0-rc.2	11.0.0-rc.2.26504.105	.NET 11

These are long-standing bugs, not newly introduced in 10.0.3 or 11 RC2. No previous working version was identified; the original introducing versions remain undetermined.

Important caveat about age(). It fixes the particular subtraction example in the report, but is not an exact replacement for NodaTime's operator. For 2026-02-28 minus 2026-01-31, NodaTime returns P1M, while PostgreSQL age(timestamp '2026-02-28', timestamp '2026-01-31') returns P28D. Any fix needs to account for these month-end semantics rather than simply replacing make_interval() with age().

Provider scope. Both causes are in Npgsql-specific translation code, so this issue belongs here. As a cross-provider check, switching the unchanged model to stock EF Core SQLite 10.0.4 fails model validation because LocalDate is not natively mapped. That prevents an equivalent SQLite query comparison; it is not evidence of the same bug upstream.

Duplicate search. No matching open or closed issue was found in dotnet/efcore. dotnet/efcore#10656 concerns SQL Server DateTime subtraction, not NodaTime calendar-period semantics. In this repository, #1979 and its fix #1980 concern the earlier subtraction result-type mismatch; this report demonstrates the remaining calendar-component mismatch. #3532 fixed BCE SQL literal formatting, which is distinct from the incorrect year extraction here.

minimal repro

Use a .NET 10 console app with Npgsql.EntityFrameworkCore.PostgreSQL.NodaTime 10.0.3, Microsoft.EntityFrameworkCore 10.0.4, and NodaTime 3.2.2. Set Test__Npgsql__DefaultConnection to a PostgreSQL connection string targeting a disposable database: this program deletes and recreates it.

using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
using NodaTime;

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

context.Rows.Add(new Row { From = new LocalDate(2025, 12, 31), To = new LocalDate(2026, 1, 31), Bce = new LocalDate(-100, 12, 31) });
await context.SaveChangesAsync();
var row = await context.Rows.AsNoTracking().SingleAsync();

Console.WriteLine($"To - From: .NET {row.To - row.From}, database {await context.Rows.Select(r => r.To - r.From).SingleAsync()}");
Console.WriteLine($"(To - From).Months == 1: .NET {((row.To - row.From).Months == 1 ? 1 : 0)}, database {await context.Rows.CountAsync(r => (r.To - r.From).Months == 1)}");
Console.WriteLine($"Year of LocalDate(-100, 12, 31): .NET {row.Bce.Year}, database {await context.Rows.Select(r => r.Bce.Year).SingleAsync()}");

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

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

public class Row
{
    public int Id { get; set; }
    public LocalDate From { get; set; }
    public LocalDate To { get; set; }
    public LocalDate Bce { get; set; }
}

Thanks, will indeed change out NodaTime subtraction to use age() as suggested, in #4109. Note that this doesn't provide full fidelity with NodaTime subtraction - doing so in PG would be quite complex, requiring custom SQL that would potentially hurt perf etc; so age() is indeed an improvement, but would not bring us to 100% fidelity. In addition, the switch to age() could introduce some breaking changes, so I'm merging this for 11.0 only.

Fix NodaTime BCE year extraction

Navigation Menu

Repository navigation

NodaTime: `LocalDate - LocalDate` returns days only, and `.Year` of BCE dates is off by one #4089

AI Triage

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

NodaTime: LocalDate - LocalDate returns days only, and .Year of BCE dates is off by one #4089

Description

Activity

roji commented on Oct 25, 2026

AI Triage

roji commented on Oct 25, 2026

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions

NodaTime: `LocalDate - LocalDate` returns days only, and `.Year` of BCE dates is off by one #4089