efcore.pg: DateTimeOffset does not mapped correctly to query parameter

I have an issue with DateTimeOffset parameter mapping. Here is my model:

    public class ShopPack
    {
        public int Id { get; set; }
        public int ShopItemId { get; set; }
        public decimal Price { get; set; }
        public decimal? StrikethroughPrice { get; set; }
        public bool IsActive { get; set; }
        public DateTimeOffset? ExpiresAt { get; set; }
        public int? Position { get; set; }

        [ForeignKey("ShopItemId")]
        public ShopItem Item { get; set; }
    }

Here is my EF.Core query for this model:

DbContext.ShopPacks.Include(sp => sp.Item)
                .Where(sp => sp.IsActive && (sp.ExpiresAt == null || DateTimeOffset.Now < sp.ExpiresAt))
                .OrderBy(sp => sp.Position)
                .ToListAsync();

Machine, running this .NET code has following timezone: ‘Europe/Moscow’ PostgreSQL server has following timezone: ‘UTC’

According to this mapping table Npgsql should convert DateTimeOffset to UTC locally and then send it to PostgreSQL as timestamptz value. But it doesn’t. My models, which have expire time in less than 3 hours, are not selected by the query.

I tried to investigate and collect some logs and here what I found in PostgreSQL logs:

2018-02-14 17:17:08 MSK LOG: execute <unnamed>: SELECT “sp”.“Id”, “sp”.“Count”, “sp”.“CreatedAt”, “sp”.“ExpiresAt”, “sp”.“IsActive”, “sp”.“LastModifiedAt”, “sp”.“Position”, “sp”.“Price”, “sp”.“ShopItemId”, “sp”.“StrikethroughPrice”, “sp”.“SubscriptionDuration”, “sp.Item”.“Id”, “sp.Item”.“BasePrice”, “sp.Item”.“BaseSubscriptionDuration”, “sp.Item”.“CreatedAt”, “sp.Item”.“IsSubscription”, “sp.Item”.“LastModifiedAt”, “sp.Item”.“Name”, “sp.Item”.“Title”

FROM “ShopPacks” AS “sp”

INNER JOIN “ShopItems” AS “sp.Item” ON “sp”.“ShopItemId” = “sp.Item”.“Id”

WHERE (“sp”.“IsActive” = TRUE) AND (“sp”.“ExpiresAt” IS NULL OR ($1 < “sp”.“ExpiresAt”))

ORDER BY “sp”.“Position”, “sp”.“Id” 2018-02-14 17:17:08 MSK DETAIL: parameters: $1 = ‘2018-02-14 17:17:08.635765’

So, as you can see, DateTimeOffset value is not converted to UTC before sending and that is cause of the problem. I also tried to change DateTimeOffset.Now in query to DateTimeOffset.UtcNow - it helped in that timezone configuration, but what if PostgreSQL server will have another timezone? We have different PostgreSQL servers for different environments and some of them doesn’t have ‘UTC’ timezone.

Is there any solution, which would not depend on timezone configuration of ASP.NET Core server and PostgreSQL?

I’m wondering why this problem exists at all. .NET has special type (DateTimeOffset) to solve timezone confusions. PostgreSQL has special type (timestamptz) to solve timezone confusions. But there are still timezone confusions. How to solve them?

About this issue

Original URL
State: closed
Created 6 years ago
Comments: 17 (9 by maintainers)

Most upvoted comments

Wow, I just upgraded my reproduce project to .NET Core 2.1 + Npgsql 4.0 and problem has gone. Query now returns 2 records and log entry contains correct parameter value with timezone:

2018-06-04 00:44:22 MSK DETAIL:  parameters: $1 = '2018-03-02 15:00:00+00'

It seems to be this issue was somehow fixed with your refactoring in 4.0, so you can close it.

grayver on Jun 3, 2018

@timbze that’s correct - this issue is very old and Npgsql’s timestamp handling has changed in a significant way in 6.0, as you wrote.

roji on Sep 29, 2022

@sguryev right now DateTimeOffset’s members ~are~ aren’t translated, so evaluation happens on the client. This is unlike DateTime which does translate. #473 tracks translating DateTimeOffset’s members.

roji on Jul 14, 2018

Sorry, no, I didn’t have time to look at this at all with the big 4.0 release… Note that we did do some changes to date/time handling for 4.0: DateTime is now always sent as timestamp and DateTimeOffset is always sent as timestamptz (#1940), and in addition the NodaTime plugin has been released.

Can you please confirm that you’re still encountering the problematic timestamptz behavior with the new version 4.0?

roji on Jun 3, 2018

Of course all developers on your application have to agree and work together… Would you want one developer to encode strings in UTF8 and another in UTF16, or one to work in C# and another in Java?

Sorry, I haven’t meant not to have any convention at all. I meant - if we have a chance to choose from convention and restriction, then last one should be preferred. If a human has a chance to make a mistake, then it happens one day. One day developer will use DateTime.Now insted of DateTime.UtcNow and this would cause a bug in application.

That’s a valid point, you’re right that accidental usage of DateTime.Now can be dangerous. This kind of problem can be addressed with analyzers - take a look at this article, there’s also a suggestion here, reiterating the idea that any sort of timezone awareness in a server application is a bad idea.

This kind of mistake is also prevented when using Nodatime, since you don’t have a single kind that can mean both a timezone-independent instant in time and a zoned timestamp.

I’ll just repeat myself by saying that using DateTimeOffset everywhere introduces its own set of risks of programmer mistakes - the conversions that have to happen at the edge of every system (database to application, between different servers…) increase the risk that at some point somebody will badly convert (e.g. forget the offset or similar). With UTC everywhere you’re just passing around the same value, having the same meaning, absolutely everywhere.

I would prefer automatic DateTime handling (with DateTimeOffset either with NodaTime) over manual. Of course, only if it works correctly and covers all corner cases. According to provided articles, DateTimeOffset doesn’t cover all corner cases.

I’m still not sure what “automatic DateTime handling” means, or what you expect DateTimeOffset or Nodatime to do for you. Either an offset/timezone is relevant for your server application (which it isn’t in 99% of cases) or it isn’t - if it’s the latter you shouldn’t be reasoning about it. Nodatime fixes things by having a much more sane type hierarchy which separates Instants (points in a global timeline regardless of observer timezones) from types like ZonedDateTime; the .NET types mix things up, create a lot of ambiguiity and thus encourage bad programming practices.

Aside from the legitimate risk discussed above of a programmer calling DateTime.Now instead of DateTime.UtcNow, I really can’t see the advantage of moving an offset/timezone around in your application and having to convert values everywhere.

The second part of my answer is that yes, I’d strongly recommend configuring your servers in UTC (although that’s beyond the scope of this issue).

Unfortunately, it’s not always possible. Due to administrative policy and other applications working on the same server (or database).

I understand, that’s true in many cases. I would still try to push moving to UTC on your servers - for your administrators’ own good - but the least you can do is isolate your application from whatever timezone your server happens to be configured to.

In the vast majority of the applications I’ve seen, timestamps represent simple instants in time, and therefore should be represented in something like milliseconds since the Unix epoch, or a DateTime (which is basically the same). No timezone or offset is necessary or advisable, as it can only cause confusion wherever the value needs to be interpreted.

Yes, my application also uses simple instants. There is no need to store timezone.

So I’d really recommend you use a type that corresponds to that. I’d also recommend considering using Nodatime for your timestamp processing. It’s true that currently you’d have to convert DateTime values coming from Npgsql to Nodatime Instants, but in the next version of Npgsql you’ll have the option of reading Nodatime types directly.

Where do timestamps come from, what is done with them?

In most cases application server generates them with trivial datetime arithmetics. Also there are functions in PostgreSQL which contain timestamp comparision logic (usually comparing with CURRENT_TIMESTAMP) and INTERVAL type arithmetics. In very rare cases administrative users can input some datetime data via web forms (Angular 4 + ASP.NET Core).

OK, so that sounds like a quite standard application. As I wrote above I’d consider using Nodatime (this is pretty much my recommendation for almost any app doing date/time logic in .NET), but at the very least I’d switch to timestamp and DateTime.

There’s still an issue about the actual behavior of DateTimeOffset and timestamptz (proper conversion when writing), so I’ll keep this open and try to look soon.

roji on Mar 5, 2018