DateTimeOffset does not mapped correctly to query parameter #303
Comments
|
Sorry it took me so long to get to this. timestamptz is, frankly, a very problematic type in general that indeed causes a great deal of confusion. Always keep in mind that it does not actually store a timezone - Now regarding your specific issue. It's not exactly clear from your logs why you think the conversion isn't happening (especially since you don't post the time at the client side). What I see is that when PostgreSQL logs the timestamptz value ( The wire representation of a timestamptz (what Npgsql actually sends) is simply a UTC timestamp with no timezone component, and as written above, when asked to send a DateTimeOffset, Npgsql first converts to to UTC and then sends that. Again, I'd actually recommend dropping timestamptz if possible, switching to timestamp and using UTC everywhere. But if you think you need timestamptz for some reason (it would be interesting to know why, aside from legacy reasons), please try to post a full, clear code sample that illustrates the issue, preferably with fixed values and not |
|
Thank you for detailed answer! First of all, let me explain why I use DateTimeOffset and timestamptz. Initially, I had DateTime for .NET and timestamp for PostgreSQL. It worked fine on developer machine, where browser, app server and database had same timezone settings. But when we started to use our application in test and beta environment, we met a lot of timezone-related problems. Default Ubuntu and Docker installation of PostgreSQL has UTC timezone. User browsers have different timezones. So conversions from JS to .NET and from .NET to PostgreSQL (and in reverse order) brought many discrepancies. I examined related issues in Npgsql repository (for example this, this and this) and made a conclusion that I need to move to DateTimeOffset and timestamptz. According to documentation, in this case .NET, PostgreSQL and Npgsql should take care of all timezone conversions. According to this table DateTimeOffset and timestamptz are closest types and conversion between them is most natural.
So I was sure that this is best type combination and I will never meet any timezone problems again. Now I discouraged to meet again timezone problem and surprised with your advice to get rid of timestamptz. Now regarding to the specific issue:
This log was collected on developer machine with 'Europe/Moscow' timezone and as you can see datetime value in parameter matches datetime value in log entry ( I prepared separate project with this error reproduced here: I get this issue stably with this project on my local developer machine: Please pay attention to create sample data migration - there are 4 records with following ExpiresAt values:
Here is query and according to it's logic and sample data it should return 2 records: var borderDate = new DateTimeOffset(2018, 03, 02, 18, 00, 00, TimeSpan.FromHours(3));
return _context.ShopOffers.Include(so => so.Item)
.Where(so => so.IsActive && (so.ExpiresAt == null || borderDate < so.ExpiresAt))
.OrderBy(so => so.Position).ThenBy(so => so.Id)
.ToListAsync();But it returns only one record (the last one). And here what I see int PostgreSQL log:
So, datetime value with +03 timezone offset was not converted to UTC. P.S. Very interesting feature in this issue - it is not reproducable in console application, when I call repository method in sync manner. In that case datetime is converted to UTC and I see '+00' in parameter in PostgreSQL log. But in async ASP.NET Core MVC action it is stably reproducable. |
Thanks for providing more detail. I have no idea what kind of troubles you previously had with DateTime and It sounds from the above that this was not the case. I'm really not sure what in the docs or the issues you cited convinced you to move to Regarding the actual issue, I'll be taking a look soon to understand exactly what's going on. One quick note though... When you say the following:
You should understand what's really going on - the above aren't raw values. In your database,
I can't think of anyway where the application type (console vs. ASP.NET Core) could have any impact on this. Are you running your console application on the same server as where you're running the ASP.NET Core MVC? If not it's very likely that the different machines have a different timezone or something similar, and that explains the difference. |
I understand. I insert those values in text query, so PostgreSQL automatically converts them to UTC.
Yes, I'm running console and ASP.NET Core MVC on the same developer machine. |
|
OK, I'll try to take a closer look in the coming days, but please consider switching to a simple UTC-everywhere architecture with |
|
Ok, I'll try to describe my thoughts and reasons about DateTime\timestamp switching:
|
|
Here are some answers, not in the same order as the questions.
My answer to this has two parts... First, and that's a very important point: you don't necessarily have to configure PostgreSQL or your servers to UTC. What I'm suggesting is that your PostgreSQL and machine timezones have absolutely no effect on your application. At no point should your application look at the local machine's timezone, nor should that timezone be a part in any part of an application's timestamp operation. Anything else exposes your application to changes by a completely arbitrary parameter outside its control - the location where some server happens to be located. If you move some component from a data center in Europe to the US, your application starts behaving differently - not an amazing situation. 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). I'd suggest that your read this rant, which forces this point a little :) I indeed configure all my servers around the world to be UTC; this prevents time jumps because of daylight saving changes (oh the agony), allows them to talk to each other transparently without having to worry about conversions, and allows me to copy log files without having to start thinking where they came from when examining timestamps. And in any case, if you're displaying timestamps to users, it's those users' timezones which matter, and not the geographical location of the servers they're talking to. At the end of the day, timezones are something that matters to humans, not to computers - so it's generally best to keep them out of application altogether, except for display/input purposes (of course this is a generalization, and specific applications may have different needs).
Ahh, that's quite a debate. First I recommend you take a long look at the Nodatime library and read on why it exists; the next version of Npgsql will optionally support Nodatime types instead of the built-in .NET types (#546).The basic .NET types (DateTime, DateTimeOffset) are actually flawed in many ways and promote some bad programming by their ambiguities - this is what Nodatime attempts to fix. I'd have to know more about what your application does, but you have to ask yourself one important question... When you're storing your timestamp in the database, is that simply a moment that occurred at some point, and which, when displayed, needs to be shown in the user's timezone? In other words, is the offset or timezone (more on that later) part of what it is that you're storing, or does it just represent an instant in the "global timeline", regardless of which timezone a particular observer is in? Another way to look at it, is to ask yourself whether it's conceivable that the same timestamp would have to be displayed to different users, and therefore in different timezones? 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. Regarding the Microsoft docs... How is DateTimeOffset better at uniquely an unambiguously identifying a single point of time, more than a simple Unix-epoch timestamp or a UTC DateTime? In fact, two different DateTimeOffset can represent the same instant, simply because they have two offsets (14:00 at offset +1 or 15:00 at offset +2). When the Microsoft docs talk about a "single point of time", they're referring to a user's local time, i.e. 14:00 in England - that's very rarely something that should be stored somewhere (as opposed to displayed). Note also that the docs do state that DateTime is fine as long you're representing UTC, and that arithmetic works too - why wouldn't it. Once again I recommend reading this Nodatime document, which explains the concepts above. Note also that DateTimeOffset has an offset, but an offset is not a timezone. Timezones also have daylight saving, so a simple offset is simply insufficient to fully display timestamps to users - it will sometimes work, and sometimes not. That's just one flaw in the BCL types, and that alone makes it unusable for many (most?) purposes (this is why the Microsoft docs state that "it is not a fully time zone-aware data structure") .
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? The reality is actually the opposite - UTC everywhere reduces the probability of mistakes and general confusion. If you're storing your timestamps as PostgreSQL
That's the same objection as the one made above (1) for developers. Yes, projects have rules that need to be followed, otherwise things turn into a catastrophic mess, and telling everyone they can use whatever timezone they want will not reduce mistakes. But more importantly, you may be forgetting that PostgreSQL's
That's a good question. When you use PostgreSQL To continue this discussion I'd need a bit more detail on how your application interacts with timestamps. Where do timestamps come from, what is done with them? |
|
Wow, thanks for detailed and impressive answer. I've read mentioned articles about NodaTime and "Use UTC everywhere" manifest. Here are my thoughts and answers:
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. 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.
Unfortunately, it's not always possible. Due to administrative policy and other applications working on the same server (or database).
Yes, my application also uses simple instants. There is no need to store timezone.
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). |
That's a valid point, you're right that accidental usage of 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'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 Aside from the legitimate risk discussed above of a programmer calling
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.
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.
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 There's still an issue about the actual behavior of DateTimeOffset and |
|
Is there any update? |
|
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 Can you please confirm that you're still encountering the problematic |
|
Wow, I just upgraded my reproduce project to .NET Core 2.1 + Npgsql 4.0 and problem has gone. It seems to be this issue was somehow fixed with your refactoring in 4.0, so you can close it. |
|
Ok, great! |
|
Hi guys, thank you for such long and detailed conversation. I have another question which I was not able to resolve. And I'm receiving
I would like to know if there any way to execute such filtering queries on the server side? I saw you answer saying:
I'm concerned that such @grayver what do you do with the client-side evaluation? Looking forward for your response. |
|
Not trying to resurrect an old issue, but I was linked here from another place, and this seems like good information for someone seeking info like me... until I find that it seems the |
|
@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. |
I have an issue with DateTimeOffset parameter mapping. Here is my model:
Here is my EF.Core query for this model:
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:
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?
The text was updated successfully, but these errors were encountered: