DateTimeOffset - DST offset issue

[domdee]

Hi, I have purchased the version of dotConnect for Postrgresql 7.10. I encountered some error while saving the date to the database. The error also occurs in the version: v7.11.1253.0


My test date is:

Code: Select all

2018-10-28 02:40 +02:00

On this day there is a change of time from summer to winter time. Time 2:40 + 2h is the right time. Then the time from the third hour (3) changes to the second (2), and the time zone changes from +2 to +1.


After posting to the database, postgres gets the entry:

Code: Select all

2018-10-28 01:40:00 +02:00

The value is surprising, because the time has been withdrawn from 2:40 to 1:40 (no one knows why), and the zone has been the same +2.


I would expect the driver to write to the database what has been forwarded.
Please indicate how to do it or fix the error.

I'm attaching a program that can reproduce the error. Currently, this is a mistake with a high status for me, because at the time of the change of time, the dates recorded at the moment of time change are saved incorrectly.<br/>

I would like to mention that I tried to use:

Code: Select all

PgSqlLocalization.TimeZone = "+0";

Interestingly, the time entered the database correctly:

Code: Select all

2018-10-28 02:40 +02:00

however, then the reading is corrupted and the data is changed back to UTC - I need it to be exactly in the same time zone as I have saved (this is very important)


program to reproduce issue:

Code: Select all

using Devart.Common;
using Devart.Data.PostgreSql;
using Devart.Data.PostgreSql.Entity.Migrations;
using System;
using System.Data.Common;
using System.Data.Entity;
using System.Data.Entity.Infrastructure.Interception;
using System.Globalization;
using System.IO;

namespace OffsetDTSIssue
{
    public class Table
    {
        public int Id { get; set; }
        public DateTimeOffset Date { get; set; }
        public string DateAsString { get; set; }
    }

    public class MyDbContext : DbContext
    {
        public MyDbContext(string nameOrConnectionString) : base(nameOrConnectionString)
        {
            Database.SetInitializer<MyDbContext>(null);
        }

        public IDbSet<Table> TestData { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);

            modelBuilder
                .Entity<Table>()
                .ToTable("TestData")
                .HasKey(x => x.Id)
                .Property(x => x.Id).HasDatabaseGeneratedOption(System.ComponentModel.DataAnnotations.Schema.DatabaseGeneratedOption.Identity);

            modelBuilder.Entity<Table>().Property(x => x.Date);
        }
    }

    public class Program
    {
        private static void Main(string[] args)
        {
            Console.WriteLine($"Culture:\t{CultureInfo.CurrentCulture.DisplayName}");
            Console.WriteLine($"Timezone:\t{TimeZoneInfo.Local.DisplayName}");
            LogAssmVersion(typeof(DbCommandBase));
            LogAssmVersion(typeof(PgSqlConnection));
            LogAssmVersion(typeof(PgSqlConnectionInfo));

            var dbInit = new MyDbContext("Domain");

            dbInit.Database.CreateIfNotExists();

            var con = dbInit.Database.Connection;
            con.Open();
            LogPgInfo(dbInit, "Version", "select version()");
            LogPgInfo(dbInit, "Timezone", "select current_setting('TIMEZONE') tz");

            using (var db = new MyDbContext("Domain"))
            {
                var date = new DateTimeOffset(2018, 10, 28, 2, 40, 0, new TimeSpan(2, 0, 0));
                var date2 = new DateTimeOffset(2018, 10, 28, 2, 40, 0, new TimeSpan(1, 0, 0));

                db.TestData.Add(new Table { Id = 1, Date = date, DateAsString = date.ToString() });
                db.TestData.Add(new Table { Id = 2, Date = date2, DateAsString = date2.ToString() });

                db.SaveChanges();
            }
        }

        private static void LogAssmVersion(Type type)
        {
            var info = new FileInfo(type.Assembly.Location);
            Console.WriteLine(
                "Dll Version:\t{0}, v{1}",
                info.Name,
                type.Assembly.GetName().Version.ToString());
        }

        private static void LogPgInfo(MyDbContext store, string label, string command)
        {
            using (var cmd = store.Database.Connection.CreateCommand())
            {
                cmd.CommandText = command;
                Console.WriteLine("Pg {0}:\t{1}", label, cmd.ExecuteScalar());
            }
        }
    }
}

Output:

Code: Select all

Culture:        Polski (Polska)
Timezone:       (UTC+01:00) Sarajewo, Skopie, Warszawa, Zagrzeb
Dll Version:    Devart.Data.dll, v5.0.2045.0
Dll Version:    Devart.Data.PostgreSql.dll, v7.11.1253.0
Dll Version:    Devart.Data.PostgreSql.Entity.EF6.dll, v7.11.1253.0
Pg Version:     PostgreSQL 9.6.6, compiled by Visual C++ build 1800, 64-bit
Pg Timezone:    Europe/Sarajevo

[domdee]

To the Devart team: can you help me in solving this problem?

[Shalex]

We have reproduced the issue and are investigating it. We will notify you about the result as soon as possible.

[domdee]

Is there anything known about this error?

[Shalex]

The investigation is in progress. As soon as we have any result, we will contact you.

[Shalex]

I would expect the driver to write to the database what has been forwarded.

DateTimeOffset is converted to PgSqlTimeStamp by the provider before inserting it into database.
By design:
* PgSqlTimeStamp has to pass a value to the server in UTC time zone (format used by PostgreSQL Server itself for storing timestamp with time zone values)
* when reading data from the server, PgSqlTimeStamp converts it to the current timezone set in your application

After posting to the database, postgres gets the entry:

Code: Select all

2018-10-28 01:40:00 +02:00

Most likely, the tool you used for checking PostgreSQL entries also converted values to some specific timezone (e.g.: timezone of the workstation where the tool is installed).

[domdee]

I did not use any tool to view data, the wrong date was badly saved to the database. The test program shows it. I do not understand how it could be desing for the driver from 2018-10-28 02:40 +02: 00 to do this:

2018-10-28 01:40:00 +02: 00

I understand yet that he would change the time zone, but he would change the time and not the zone, and thus the reading is wrong.

[domdee]

Hello Devart team!

We have one more test.

We launched the test program in two cases:

1) in windows, we had "Automatically adjust clock for Daylight Saving Time" enabled (I attach a screenshot).



then the error was repeated: date: 2018.10.28 2:40 +2: 00 was saved as: 2018.10.28 1:40 +2: 00 - which is the wrong date

2) in windows, we had turned off "Automatically adjust clock for Daylight Saving Time"

then the error was not: namely: 2018.10.28 2:40 +2: 00 was saved as: 2018.10.28 1:40 +1: 00 - which is a different date but identical with 2018.10.28 2:40 +2: 00 so it's porous


Maybe your code somehow unnecessarily counts the DST as the operating system has turned on "Automatically adjust clock for Daylight Saving Time"?

[Shalex]

Thank you for the additional information. We will investigate the issue and notify you about the result.

[domdee]

Hi
Do you have any interesting conclusions yet?

[Shalex]

The investigation is in progress. As soon as we have any results, we will notify you.

[domdee]

Any news ?

[Shalex]

We have fixed the issue. If you are interested in the internal build with the fix, please contact us and specify the email used when registering dotConnect for PostgreSQL.

[domdee]

I've tested the new version. The error has been eliminated :-). We are now waiting for the official version to be published.

[domdee]

Does version 7.12.1328 contain the patch I tested? (I tested the version: 7/12/1326)