[SOLVED] Can Npgsql and/or NodaTime perform a select on a timestamp column with a timezone offset with an Entity Framework model?

Can Npgsql and/or NodaTime perform a select on a timestamp column with a timezone offset with an Entity Framework model?

I have Postgres database that has a timestamp with time zone column, and the local time zone for each record is different. I want to return all records in their 'local' time zone, and I've found out how using the at time zone keywords.

The query below is the raw SQL that I use to get this information, and I'm wondering if npgsql or NodaTime has extensions to produce the at time zone part of the query.

select
    e.type,
    e.description,
    -- note: `z.name` resolves to zone names like 'Australia/Sydney'
    e.date_time at time zone z.name as local_time
from events e
join sources s on e.source_id = s.id
join zones z on s.zone_id = z.id

Solution

Assuming you're asking about Entity Framework Core, the PostgreSQL NodaTime plugin allows you to write queries such as the following:

var events = await ctx.Events
    .OrderBy(e => e.LocalTimestamp.InZoneLeniently(DateTimeZoneProviders.Tzdb[e.TimeZoneId]).ToInstant())
    .ToListAsync();

In other words, you can call NodaTime methods like InZoneLeniently inside your EF LINQ query, and the provider will translate that to the AT TIME ZONE SQL expression.

Here is the full list of translations supported by the NodaTime plugin. See this article for more on using local timestamps and time zones with PostgreSQL.