Page 1 of 2

DateTimeOffset - DST offset issue

Posted: Mon 19 Nov 2018 20:25
by 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

Re: DateTimeOffset - DST offset issue

Posted: Thu 22 Nov 2018 06:48
by domdee
To the Devart team: can you help me in solving this problem?

Re: DateTimeOffset - DST offset issue

Posted: Thu 22 Nov 2018 14:07
by Shalex
We have reproduced the issue and are investigating it. We will notify you about the result as soon as possible.

Re: DateTimeOffset - DST offset issue

Posted: Fri 30 Nov 2018 08:26
by domdee
Is there anything known about this error?

Re: DateTimeOffset - DST offset issue

Posted: Tue 04 Dec 2018 16:31
by Shalex
The investigation is in progress. As soon as we have any result, we will contact you.

Re: DateTimeOffset - DST offset issue

Posted: Tue 11 Dec 2018 18:11
by Shalex
domdee wrote:
Mon 19 Nov 2018 20:25
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
domdee wrote:
Mon 19 Nov 2018 20:25
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).

Re: DateTimeOffset - DST offset issue

Posted: Tue 11 Dec 2018 18:16
by 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.

Re: DateTimeOffset - DST offset issue

Posted: Thu 13 Dec 2018 10:14
by 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).

Image

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"?

Re: DateTimeOffset - DST offset issue

Posted: Fri 21 Dec 2018 18:38
by Shalex
Thank you for the additional information. We will investigate the issue and notify you about the result.

Re: DateTimeOffset - DST offset issue

Posted: Sun 06 Jan 2019 20:08
by domdee
Hi
Do you have any interesting conclusions yet?

Re: DateTimeOffset - DST offset issue

Posted: Fri 11 Jan 2019 18:30
by Shalex
The investigation is in progress. As soon as we have any results, we will notify you.

Re: DateTimeOffset - DST offset issue

Posted: Wed 30 Jan 2019 14:33
by domdee
Any news ?

Re: DateTimeOffset - DST offset issue

Posted: Mon 04 Feb 2019 17:29
by 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.

Re: DateTimeOffset - DST offset issue

Posted: Wed 06 Feb 2019 13:55
by domdee
I've tested the new version. The error has been eliminated :-). We are now waiting for the official version to be published.

Re: DateTimeOffset - DST offset issue

Posted: Thu 14 Feb 2019 20:42
by domdee
Does version 7.12.1328 contain the patch I tested? (I tested the version: 7/12/1326)