ChronoSafe.Npgsql 1.1.2

ChronoSafe.Npgsql

Automatically converts DateTime values to UTC for PostgreSQL — so you never see Cannot write DateTime with Kind=Local again, and every value you read back is guaranteed to be UTC.

Works with raw Npgsql, Dapper, and EF Core.

The problem

PostgreSQL stores timestamptz in UTC and returns values without timezone info. .NET's DateTime has a Kind property (Utc, Local, Unspecified) that Npgsql must respect — but your app almost certainly mixes kinds, especially when deserialising from JSON or reading from a form. This causes:

• InvalidCastException: Cannot write DateTime with Kind=Local to column of type 'timestamp with time zone'
• Silent data corruption when Kind=Unspecified is written as-is
• UTC values read back with Kind=Unspecified, breaking comparisons

ChronoSafe.Npgsql fixes all of this at the data layer — one line of setup, zero changes to your domain code.

Installation

dotnet add package ChronoSafe.Npgsql

Usage

Raw Npgsql

var dataSource = new NpgsqlDataSourceBuilder(connectionString)
    .UseChronoSafe()       // ← one line
    .Build();

// Everything after this point just works.
await using var conn = await dataSource.OpenConnectionAsync();
await using var cmd = conn.CreateCommand();
cmd.CommandText = "INSERT INTO events (occurred_at) VALUES (@t)";

// Kind=Local → converted to UTC automatically
cmd.Parameters.AddWithValue("t", DateTime.Now);
await cmd.ExecuteNonQueryAsync();

EF Core

Call UseChronoSafe() inside OnModelCreating:

public class AppDbContext : DbContext
{
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.UseChronoSafe();   // ← one line, covers every DateTime property
    }
}

Dapper

Register the global type handlers once at startup (e.g. Program.cs):

ChronoSafeExtensions.AddDapperHandlers();

// Then use Dapper as normal — conversion is automatic.
var events = await conn.QueryAsync<MyEvent>(
    "SELECT occurred_at FROM events WHERE id = @id",
    new { id = 42 });

Configuration

All three integrations accept an optional ChronoSafeOptions delegate:

// EF Core
modelBuilder.UseChronoSafe(opts =>
{
    opts.UnspecifiedHandling = UnspecifiedKindHandling.AssumeLocal;
    opts.TimeZoneId = "Asia/Manila"; // optional — convert reads to local time
});

// Dapper
ChronoSafeExtensions.AddDapperHandlers(opts =>
{
    opts.UnspecifiedHandling = UnspecifiedKindHandling.AssumeUtc;
    opts.TimeZoneId = "Asia/Manila";
});

UnspecifiedKindHandling values

Reading back in local time (TimeZoneId)

By default, values read from PostgreSQL are returned as UTC. Set TimeZoneId to automatically convert every DateTime read to your server's local timezone — no manual conversion needed anywhere in your code.

modelBuilder.UseChronoSafe(opts =>
{
    opts.UnspecifiedHandling = UnspecifiedKindHandling.AssumeLocal;
    opts.TimeZoneId = "Asia/Manila"; // UTC+8, DST-aware
});

Setting an invalid ID throws TimeZoneNotFoundException at startup, so misconfiguration is caught immediately.

Finding your timezone ID

Use the IANA timezone database ID for your region. These are cross-platform and work on Linux, macOS, and Windows (.NET 6+).

⚠️ Do not use Windows-style IDs like "Singapore Standard Time" — they break on Linux where most servers run.

Common IANA IDs:

Full list: https://www.iana.org/time-zones

How it works

FAQ

Does this affect DateTimeOffset? No. DateTimeOffset always carries offset information and Npgsql handles it correctly. This package only touches DateTime.

What about timestamp without time zone columns? Those columns store no timezone data. If you're using timestamptz (recommended), this package is all you need.

Is there a performance overhead? Negligible. The TimeZoneInfo is resolved once at startup and cached — no per-query lookup.

Why Kind=Unspecified on the read result? After converting UTC → local time, the value is no longer UTC and shouldn't be treated as Local either (that would trigger another OS-level conversion). Unspecified means "already in target timezone — display as-is."

License

MIT © Johann Christopher Desepeda