efcore.pg: Range function resolution fails for column (tstzrange) and parameter (tsrange)

With the following database table:

create table public.some_record (
  during tstzrange
);

The following model:

public class SomeRecord
{
    public NpgsqlRange<DateTime> During { get; set; }
}

causes the following exception on insert:

2018-08-02 15:36:52.732 +02:00 [Error] [8504472] [14] [] [] [:] [Microsoft.EntityFrameworkCore.Database.Command] Failed executing DbCommand ("17"ms) 
[Parameters=["@p0='[2018-08-02 15:36:51,)' (DbType = Object)"], CommandType='Text', CommandTimeout='600']"
""INSERT INTO public.some_record (during)
VALUES (@p0);"
Npgsql.PostgresException (0x80004005): 42804: column "during" is of type tstzrange but expression is of type tsrange
   at Npgsql.NpgsqlConnector.<>c__DisplayClass161_0.<<ReadMessage>g__ReadMessageLong|0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at Npgsql.NpgsqlConnector.<>c__DisplayClass161_0.<<ReadMessage>g__ReadMessageLong|0>d.MoveNext() in C:\projects\npgsql\src\Npgsql\NpgsqlConnector.cs:line 1032
--- End of stack trace from previous location where exception was thrown ---
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming) in C:\projects\npgsql\src\Npgsql\NpgsqlDataReader.cs:line 444
   at Npgsql.NpgsqlDataReader.NextResult() in C:\projects\npgsql\src\Npgsql\NpgsqlDataReader.cs:line 332
   at Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken) in C:\projects\npgsql\src\Npgsql\NpgsqlCommand.cs:line 1219
   at Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior) in C:\projects\npgsql\src\Npgsql\NpgsqlCommand.cs:line 1130

About this issue

Original URL
State: closed
Created 6 years ago
Comments: 31 (22 by maintainers)

Most upvoted comments

To supplement what @Brar said above, NodaTime also uses the IANA timezone database, which is pretty much the standard outside of the Windows world. The advantage of NodaTime is a sane date/time type hierarchy along with the timezone database - basically everything is taken care of for you.

Not to distract from the wider discussion, but while every time zone has a definition of DST, not every locale in the time zone necessarily observes it:

Bah, date/time insanity… That’s why it should all be delegated to a library which actually knows about all that complexity…

roji on Aug 6, 2018

Incidentally I came to the same conclusions. If team lead agrees we are going to set sails with nodatime instants, tsrange and timestamp without timezones. Where required we will introduce additional columns for client local times and/or the timezone id of the client that created or updated that specific entity.

dpsenner on Aug 4, 2018

@dpsenner converting an UTC timestamp to a local timestamp is more than just adding an offset in hours. There are changes in the assignment of a country to a specific time zone, introduction or dismissal of daylight saving time in a country and other weird things that might happen and be set in effect at a specific timestamp due to government decisions.

It’s best to rely on tools that are backed by time zone databases that have all the knowledge of those details and do the heavy lifting for you.

TimeZoneInfo Methods are probably the most natural way for .Net but there is also https://github.com/LZorglub/TimeZone which uses the IANA time zone database (I haven’t tried it). The idea is to convert the timestamp back to its original value before displaying it to the user

Brar on Aug 6, 2018

@dpsenner good to hear it, not everyone agrees with my views on date/time handling 😃

Note that if you want to represent the client’s timezone inside your database, you need a separate column in the database no matter what, since timestamp with time zone doesn’t actually store a timezone. This is true whether you go with NodaTime or not.

Another important point is that DateTimeOffset does not contain a timezone - only an offset. Many people confuse the two, and end up storing a DateTimeOffset in SQL Server thinking that they’ve resolved the question. However, a timezone also has daylight saving rules, which can be very significant and are completely ignored when all you have is an offset. That’s another disadvantage of DateTimeOffset - it gives you the illusion of having taken care of the problem, whereas in fact you haven’t. In that sense I prefer PostgreSQL which doesn’t have an actual “timestamp with offset” type, but rather forces you to think about things and represent the timezone in a separate column if that’s what you really want.

@austindrenski have we actually done any change in this issue? If not can you please remove the label and milestone?

roji on Aug 5, 2018

@austindrenski it’s hard to make sweeping recommendations without taking account any specific needs etc.

But yes, my personal opinion is that in the general case, unless your application has special needs and cannot be UTC-everywhere:

Avoid using DateTimeOffset.
If at all possible, use NodaTime, which makes most of these issues disappear.
If NodaTime isn’t an option, use DateTime with Kind=UTC and make sure all timestamps are properly normalized/converted to UTC at the very edge.
Assume timestamps in your application are UTC, and add assertions in critical places to ensure this is so.
Consider using an analyzer to further enforce UTC DateTime (e.g. disallow DateTime.Now).

Note that this is my personal take on things and I’m sure many would disagree.

roji on Aug 3, 2018