Efcore.pg: DateTimeOffset Conversion

[Silvenga]

According to the docs, DateTimeOffset is converted to UTC before sending it. So for example,

new DateTimeOffset(2012, 1, 1, 1, 1, 1, TimeSpan.FromHours(-6))

1/1/2012 1:01:01 AM -06:00

Gets converted to:

1/1/2012 7:01:01 AM +00:00

When inserted into the database.

This is great. However, when pulling this value out of the database, the following is returned:

1/1/2012 7:01:01 AM -06:00

It appears that npgsql is using the local timezone when values are pulled out, even though npgsql inserted a UTC offset'ed value.

I would expect either:

1/1/2012 *1*:01:01 AM -06:00

or

1/1/2012 *7*:01:01 AM +00:00

Either is fine since the time is equivalent, but this is definitely not correct:

1/1/2012 *7*:01:01 AM *-06:00*

[austindrenski]

@Silvenga This issue belongs in npgsql/npgsql, since this behavior is unrelated to the EF Core provider.

But before you do that, take another look at the date and time docs (_tables truncated for clarity_):

Detailed Behavior: Sending values to the database

.NET value | NpgsqlDbType | Action
-- | -- | --
DateTimeOffset | NpgsqlDbType.TimestampTz (default) | Convert to UTC locally before sending

Detailed Behavior: Reading values from the database

PG type | .NET value | Action
-- | -- | --
timestamptz | DateTimeOffset | In local timezone offset

So reading back from a column of timestamptz into a value of DateTimeOffset ___ought___ to result in the local timezone offset.

[Silvenga]

Thanks @austindrenski

I read "In local timezone offset" as convert to local time zone from UTC (assuming that the values are always stored as UTC). Not, add local timezone offset to stored value.

[austindrenski]

@Silvenga No worries, I assumed that was part of the confusion. Let us know if you have any further questions.

[roji]

@Silvenga @austindrenski am re-opening so I can take another look, to make sure that we're doing the right thing. Will try to look at this soon.

[austindrenski]

@roji Should this be moved to npgsql/npgsql?

[Silvenga]

@roji I pulled down the driver to figure out what the issue was, and there wasn't any issues.

A little embarrassing - I tested everything again in linqpad and everything looks good. My only excuse is sleep deprivation. Sorry to waste everyone's time.

[roji]

Thanks for checking again, no problem, these things happen (all the time).