Critical bug with storage of DateTimeOffset in TIMESTAMP WITHOUT TIME ZONE columns

[roji]

From @nathan-alden on June 9, 2015 15:59

I have a sample project that reproduces the issue. First, information about my environment:

• PostgreSQL 9.4 running on Ubuntu 14.04.2 LTS
• Npgsql 2.2.5
• Npgsql.EntityFramework 2.2.5
• EntityFramework 6.1.3

The bug: DateTimeOffset values with no Offset (Offset == TimeSpan.Zero) are being serialized as local timestamps when the column type is timestamp without time zone. It's my opinion that this is incorrect behavior, or at the very least is inconsistent with how DateTimeOffset is serialized under other circumstances.

The test database's schema:

CREATE DATABASE test
  WITH ENCODING='UTF8'
       CONNECTION LIMIT=-1;

CREATE TABLE "table"
(
  id uuid NOT NULL,
  utc_timestamp_without_time_zone timestamp without time zone NOT NULL,
  utc_timestamp_with_time_zone timestamp with time zone NOT NULL,
  local_timestamp_without_time_zone timestamp without time zone NOT NULL,
  local_timestamp_with_time_zone timestamp with time zone NOT NULL,
  CONSTRAINT table_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE "table"
  OWNER TO postgres;

The connection string:

host=ubuntu; port=5432; database=test; user id=postgres; pooling=true; commandtimeout=60;

The context class:

public class TestContext : DbContext
{
    static TestContext()
    {
        Database.SetInitializer(new NullDatabaseInitializer<TestContext>());
    }

    public TestContext()
        : base("host=ubuntu; port=5432; database=test; user id=postgres; pooling=true; commandtimeout=60;")
    {
    }

    public DbSet<Table> Tables { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Table>();
    }
}

The entity definition:

[Table("table", Schema = "public")]
public class Table
{
    [Key]
    [Required]
    [Column("id", Order = 0)]
    public Guid Id { get; set; }

    [Required]
    [Column("utc_timestamp_without_time_zone", Order = 1)]
    public DateTimeOffset UtcTimestampWithoutTimeZone { get; set; }

    [Required]
    [Column("utc_timestamp_with_time_zone", Order = 2)]
    public DateTimeOffset UtcTimestampWithTimeZone { get; set; }

    [Required]
    [Column("local_timestamp_without_time_zone", Order = 3)]
    public DateTimeOffset LocalTimestampWithoutTimeZone { get; set; }

    [Required]
    [Column("local_timestamp_with_time_zone", Order = 4)]
    public DateTimeOffset LocalTimestampWithTimeZone { get; set; }
}

The sample code:

using (var testContext = new TestContext())
{
    var table = new Table
                {
                    Id = Guid.NewGuid(),
                    UtcTimestampWithoutTimeZone = DateTimeOffset.UtcNow,
                    UtcTimestampWithTimeZone = DateTimeOffset.UtcNow,
                    LocalTimestampWithoutTimeZone = DateTimeOffset.Now,
                    LocalTimestampWithTimeZone = DateTimeOffset.Now
                };

    testContext.Tables.Add(table);
    testContext.SaveChanges();
}

The output:

SELECT * FROM "table";

The utc_timestamp_without_time_zone column should contain 2015-06-09 15:47:24.085368, but instead it is storing the local timestamp, even though the source value was DateTimeOffset.UtcNow. Note how the other three column values are stored as expected (although it could be argued that local_timestamp_without_timezone should be in UTC). Note especially how utc_timestamp_without_time_zone and local_timestamp_without_time_zone have the same values.

If I apply the principal of least surprise, it's my opinion that I should see the following column values:

Column	Value
utc_timestamp_without_time_zone	2015-06-09 15:47:24.085368
utc_timestamp_with_time_zone	2015-06-09 10:47:24.085368-05
local_timestamp_without_time_zone	2015-06-09 10:47:24.085368
local_timestamp_with_time_zone	2015-06-09 10:47:24.085368-05

Copied from original issue: npgsql/npgsql#640

[roji]

From @nathan-alden on June 9, 2015 19:40

I have cloned the repository and am debugging locally. I think I've narrowed down the source of the issue. I'll share my findings here in awhile.

[roji]

From @nathan-alden on June 9, 2015 19:48

The problem lies with the Npgsql.SqlGenerators.ConstantExpression class. The WriteSql method appears to be what converts the constant expression to a SQL string. The code switches on primitive type, one of which is DateTimeOffset. However, the code only ever generates the string TIMESTAMP WITH TIME ZONE '...'; it doesn't appear to acknowledge timestamp without time zone at all.

INSERT INTO "public"."table"("id","utc_timestamp_without_time_zone","utc_timestamp_with_time_zone","local_timestamp_without_time_zone","local_timestamp_with_time_zone") VALUES ('1a486c13-5bab-465e-b1e1-fc026574e923'::uuid,TIMESTAMP WITH TIME ZONE '2015-06-09T19:52:29.1089000+00:00',TIMESTAMP WITH TIME ZONE '2015-06-09T19:52:29.1089000+00:00',TIMESTAMP WITH TIME ZONE '2015-06-09T14:52:29.1089000-05:00',TIMESTAMP WITH TIME ZONE '2015-06-09T14:52:29.1098776-05:00')

The incorrect timestamp is actually being generated by a combination of PostgreSQL and the host OS. The expression TIMESTAMP WITH TIME ZONE '2015-06-09T19:52:29.1089000+00:00' is being implicitly cast to timestamp without time zone like this:

SELECT timestamp with time zone '2015-06-09T19:56:56.7621180+00:00'::timestamp without time zone

timestamp with time zone converts the timestamp to a local timestamp (2015-06-09 14:56:56.762118-05), then the cast to timestamp without time zone chops off the offset. My Ubuntu VM is set to Central Daylight Time for its time zone, thus the bad conversion.

I use timestamp without time zone because I always store timestamps in UTC. I don't care about offsets, nor will the columns ever hold offsets. Under these circumstances, it seems to me that timestamp without time zone is the appropriate type to choose. Unfortunately, I am now faced with the uncomfortable possibility of having to modify my data schema to accomodate Npgsql, which is something I've managed to avoid so far.

Is there any possibility of somehow addressing this shortcoming? Can Npgsql be changed such that I can tell it somehow that the underlying storage is timestamp without time zone, and that I always want DateTimeOffset.UtcDateTime to be used when calculating a column's value?

Please advise.

[roji]

From @nathan-alden on June 9, 2015 20:2

One thing that occurred to me is anyone using the EntityFramework integration may be unaware that Npgsql is writing incorrect timestamps in these circumstances (DateTimeOffset, timestamp without time zone, database server with its time zone set to a non-zero UTC offset). This may be a pretty common scenario. You may want to consider putting out some kind of bulletin to folks so they know this is an issue. Time is very rarely something that can be wrong in an application!

In the meantime, in order to mitigate this issue, I have since changed my Ubuntu database server's system time zone to UTC by executing this command:

sudo dpkg-reconfigure tzdata

I then rebooted the server. Timestamps are now being written correctly, but I want to stress that this is incidental. Npgsql's translation of DateTimeOffset to timestamp without time zone should be independent of a server's time zone setting.

[roji]

@nathan-alden, thanks for the report and the detailed analysis. Sorry it took us so long to get back to you.

We've done a lot of work to make Npgsql behavior more sane with timestamps in version 3.0, but at the ADO.NET level rather than at EF specifically.

I understand your issue and I agree that the behavior is somewhat unexpected. However, it seems quite odd to map a .NET a DateTimeOffset to a PostgreSQL TIMESTAMP WITHOUT TIME ZONE - why not simply use a .NET DateTime? I realize I'm telling you to change your schema, but in this particular case it seems like your choice may be unjustified (and also happens to be the source of your problem). Do you have any particular reason here?

Regardless, I'm not sure what can be done here... I'm not a big expert on the EF6 provider, but the code that produces the SQL literal that represents your DateTimeOffset instance does not know the specific PostgreSQL backend type it's about to insert to, and therefore cannot make any decision based on that. Because of this, rendering DateTimeOffset as TIMESTAMP WITH TIME ZONE seems like a good choice...

I could be wrong here though, let me know your thoughts.

[roji]

From @nathan-alden on June 17, 2015 14:19

I don't really want to go into the numerous reasons why DateTimeOffset is better than DateTime in a GitHub issue. This article sums up many of the reasons, though. Generally, as a good architectural principle, code and databases should not be aware of time zones until a human requires it; generally, that means in a user interface, a report, etc. DateTimeOffset and timestamp without time zone follow that good principle.

Regardless, the justifications for using one or the other are irrelevant. One of the reasons I am generally hesitant to use ORMs are because of issues like these--the library designers force me to change what would otherwise be the better design to fit their overly-restrictive abstraction. It seems like I am being told "deal with it," which is unfortunate, because Npgsql is an excellent library and has made working with PostgreSQL in .NET much, much easier than it would otherwise be. Again, I'll sum up the issue and leave it at that. You folks can choose whether to care or not. My hope is that other people running into this issue--and there are bound to be some--can benefit from my research.

1. Npgsql assumes an underlying column data type of timestamp with time zone whenever DateTimeOffset is used.
2. Npgsql will write an incorrect timestamp when timestamp without time zone is used on a host whose time zone is not 0 UTC, even though serialization appears to work otherwise (no errors are thrown). In my opinion, this violates the principle of least astonishment.
3. Npgsql does not provide a mechanism to override this behavior.

I also want to thank you folks for your excellent work on this library. It has saved me many hours of having to roll my own solution to interfacing well with PostgreSQL in .NET. I am just concerned for other developers out there who don't even realize they may be writing incorrect timestamps to their databases.

[roji]

@nathan-alden,

I don't really want to go into the numerous reasons why DateTimeOffset is better than DateTime in a GitHub issue. This article sums up many of the reasons, though. Generally, as a good architectural principle, code and databases should not be aware of time zones until a human requires it; generally, that means in a user interface, a report, etc. DateTimeOffset and timestamp without time zone follow that good principle.

I didn't mean to ask why use DateTimeOffset is better than DateTime in general, I'm (painfully...) well-aware of the shortcomings of DateTime and how DateTimeOffset resolves them. The question is about the choice of DateTimeOffset in this particular case of yours. You said above that you always store timestamps in UTC and don't care about timezones - the choice of DateTimeOffset in this particular case begs the question. IMHO with the shortcomings and issues of DateTime, if your system is all-UTC, it's entirely acceptable and even recommended to use DateTime (and store values as TIMESTAMP WITHOUT TIME ZONE).

Regardless, the justifications for using one or the other are irrelevant. One of the reasons I am generally hesitant to use ORMs are because of issues like these--the library designers force me to change what would otherwise be the better design to fit their overly-restrictive abstraction. It seems like I am being told "deal with it," which is unfortunate, because Npgsql is an excellent library and has made working with PostgreSQL in .NET much, much easier than it would otherwise be. Again, I'll sum up the issue and leave it at that. You folks can choose whether to care or not. My hope is that other people running into this issue--and there are bound to be some--can benefit from my research.

I don't disagree with the general reticence towards ORMs - they by definition imply a loss of power and flexibility. But in this very concrete case, the question regards which type to use on an entity that the ORM is supposed to read and write to the database on your behalf. It seems like a reasonable place to be somewhat limited by the ORM, but that's just my opinion.

Npgsql assumes an underlying column data type of timestamp with time zone whenever DateTimeOffset is used.

That's not entirely accurate: Npgsql's EF provider will send a timestamptz when DateTimeOffset is used. It's up to PostgreSQL to implicitly cast this value to the underlying column data type.

Npgsql will write an incorrect timestamp when timestamp without time zone is used on a host whose time zone is not 0 UTC, even though serialization appears to work otherwise (no errors are thrown). In my opinion, this violates the principle of least astonishment.

After giving the matter a bit of thought, I don't agree. Npgsql's only decision (which you can criticize) is to send DateTimeOffset as timestamptz. Consider the following line executed in psql (I'm in UTC+3):

select '2015-05-01T10:00:00+00:00'::TIMESTAMPTZ::TIMESTAMP;
      timestamp      
---------------------
 2015-05-01 13:00:00
(1 row)

We're sending a UTC timestamptz. PostgreSQL's documentation states:

Conversions between timestamp without time zone and timestamp with time zone normally assume that the timestamp without time zone value should be taken or given as timezone local time.

So the UTC timestamptz we sent gets converted to a local timestamp. In other words, the Npgsql EF provider is in line with the standard PostgreSQL behavior, which is a good thing. Whether DateTimeOffset should be mapped to timestamptz is another question (see below).

Npgsql does not provide a mechanism to override this behavior.

Here again, I think the problem is more in Entity Framework itself. I'm far from being an EF expert, but an EF6 provider is quite limited in what it can and cannot do - the framework doesn't provide hooks and API points to allow you to tweak how values are converted to the database. Note that if you're operating at the ADO.NET level (and not EF) you have exactly the kind of control you're missing and can send a DateTimeOffset as a timestamp without time zone.

I really do understand the frustration... I can't see a way for Npgsql to make life easier for you in this particular case, I can only recommend that if you're only storing UTC timestamps, you may want to reconsider simply writing a DateTime... I'm also open to any other concrete idea on making the situation better.

[roji]

From @franciscojunior on June 17, 2015 16:48

Hi, @nathan-alden.

It seems like I am being told "deal with it," which is unfortunate, because Npgsql is an excellent library and has made working with PostgreSQL in .NET much, much easier than it would otherwise be.

It is very sad to hear that. :(

I hope this is only a shallow interpretation of the situation. As @roji said, there are areas of Npgsql, and in this specific case regarding EF interation, where more polish is needed, but we are working on it. We already had a long discussion about timezone handling and we tried to cover every corner of this problem, but there is always the possibility that we didn't handle some case.

I think the fact this problem is still around is because few people talked or reported about it. Or in some extreme cases, where people did report, we simply couldn't fix it yet because we were working to implement other parts. But since the inception of this project we always struggled to implement and create the best experience for the developer to use Postgresql with .Net.

@roji created an issue about adding the ability to set a timezone when connecting to the server. #646 . This will enable fixing this issue by implementing your suggestion number 3.

In your tests, you changed the timezone of your server. Would it be too much trouble that you try it in another server with your desired timezone and try to send a set timezone command to the same timezone your server is configured (effectively overriding the default UTC timezone used by Npgsql) when initializing your EF context? If it works ok, this would guide us to the right direction to fix this issue by adding the option to specify a timezone to be used when connecting to the database.

Thanks in advance.

[roji]

Keeping open to update documentation

[roji]

From @nathan-alden on June 25, 2015 14:8

What about using a configuration section? I use these frequently in my own code to allow the developer to configure how components work internally. The below code is backward-compatible; if the configuration section isn't present, it defaults to the original behavior. The configuration section could also be used to control other internal behavior that currently is hard-coded.

public enum DateTimeOffsetTimestampType
{
    TimestampWithTimeZone,
    TimestampWithoutTimeZone
}

public class NpgsqlConfigurationSection : System.Configuration.ConfigurationSection
{
    [ConfigurationProperty("dateTimeOffsetTimestampType", IsRequired = false, DefaultValue = DateTimeOffsetTimestampType.TimestampWithTimeZone)]
    public DateTimeOffsetTimestampType DateTimeOffsetTimestampType
    {
        get { return (DateTimeOffsetTimestampType)this["dateTimeOffsetTimestampType"]; }
        set { this["dateTimeOffsetTimestampType"] = value; }
    }
}

<npgsql dateTimeOffsetTimestampType="WithoutTimeZone" />

NpgSqlConfigurationSection configurationSection = (NpgSqlConfigurationSection)ConfigurationManager.GetSection("npgsql");

sqlText.AppendFormat(
    "{0} '{1:o}'",
    configurationSection != null && configurationSection.DateTimeOffsetTimestampType == DateTimeOffsetTimestampType.TimeStampWithoutTimeZone ? "TIMESTAMP WITHOUT TIME ZONE" : "TIMESTAMP WITH TIME ZONE",
    _value);

[roji]

From @Emill on August 16, 2015 0:34

We have decided to map DateTimeOffset to timestamptz, which means DateTimeOffset values will be sent as timestamptz values since we think that is more correct than sending it as a timestamp and stripping the offset (or convert to some time zone and then strip the offset). A side effect will be that if your column type is timestamp instead of timestamptz, PostgreSQL will implicitly convert the timestamptz to a timestamp according to its conversion rules (it assumes you want it in local time). Therefore I don't think this is an Npgsql issue, but rather a design choice in PostgreSQL.

The only thing we can provide is like you mention, configurability for users that don't like the default mappings.

[ItWorksOnMyMachine]

All of my datatypes are timezone with time stamp in our database. The model type is DateTime. I'm setting a date value to DateTime.UtcNow in an insert, but am getting this error:

InvalidCastException: Cannot write DateTime with Kind=UTC to PostgreSQL type 'timestamp without time zone', consider using 'timestamp with time zone'. Note that it's not possible to mix DateTimes with different Kinds in an array/range. See the Npgsql.EnableLegacyTimestampBehavior AppContext switch to enable legacy behavior.

I tried setting a ColumnAttribute on the column to Column(TypeName="timestamptz") and am getting this error: MappingException: Schema specified is not valid. Errors: (8329,12) : error 2019: Member Mapping specified is not valid. The type 'Edm.DateTime[Nullable=False,DefaultValue=,Precision=]' of member 'CreatedDate' in type 'CodeFirstNamespace.TemporaryJoiner' is not compatible with 'Npgsql.timestamptz[Nullable=False,DefaultValue=,Precision=7]' of member 'createddate' in type 'CodeFirstDatabaseSchema.TemporaryJoiner'.