strange storing DateTimeOffset

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for PostgreSQL
Post Reply
domdee
Posts: 18
Joined: Mon 19 Nov 2018 15:11

strange storing DateTimeOffset

Post by domdee » Fri 18 Jun 2021 17:32

Hi

I discovered strange storing DateTimeOffset :).
I'm saving to db 2 dates:

new DateTimeOffset(1883, 12, 31, 0, 0, 0, 0, new TimeSpan(1, 0, 0))
and
new DateTimeOffset(1884, 12, 31, 0, 0, 0, 0, new TimeSpan(1, 0, 0))

first is saved as (little confused way of savings):
"1883-12-31 00:22:00+01:22"

second (looks normal):
"1884-12-31 00:00:00+01"

Why this 22 minutes are added to date : new DateTimeOffset(1883, 12, 31, 0, 0, 0, 0, new TimeSpan(1, 0, 0)) and Offset ?

Dominik

domdee
Posts: 18
Joined: Mon 19 Nov 2018 15:11

Re: strange storing DateTimeOffset

Post by domdee » Fri 18 Jun 2021 18:52

Im using net core 3.1 and ubuntu 20 platform

domdee
Posts: 18
Joined: Mon 19 Nov 2018 15:11

Re: strange storing DateTimeOffset

Post by domdee » Sat 19 Jun 2021 17:55

It's worse than I thought for Linux and net core 3.1 and ef 6.4. Well, a simple program that writes the date default (DateTimeOffset) to the postgres database writes the value: 0001-01-01 00: 58: 00 + 01: 22, which is a wrong time because after subtracting the offset, the value goes beyond the minimum for the DateTimeOffset type.
Caution. The value written running on windows system(exactly the same code) writes the value: 0001-01-01 01: 22: 00 + 01: 22. This is a strange notation but correct.

The devart version used was 7.17. I am asking for your opinion. I am using dotConnect Postgresql commercially.

domdee
Posts: 18
Joined: Mon 19 Nov 2018 15:11

Re: strange storing DateTimeOffset

Post by domdee » Mon 21 Jun 2021 14:22

ok, I would like to summarize the topic.

I wrote a test program. It runs on Linux Ubuntu 20. On Linux, the time zone is CEST.

Code: Select all

using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using System.Globalization;
using System.IO;
using System.Linq;
using Devart.Common;
using Devart.Data.PostgreSql;
using Devart.Data.PostgreSql.Entity.Configuration;
using Devart.Data.PostgreSql.Entity.Migrations;
using Microsoft.Extensions.Configuration;

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

    [DbConfigurationType(typeof(PgSqlConfiguration))]
    public class MyDbContext : DbContext
    {
        public MyDbContext(string nameOrConnectionString) : base(nameOrConnectionString)
        {
            Database.SetInitializer<MyDbContext>(new CreateDatabaseIfNotExists<MyDbContext>());
        }

        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
    {
        public static IConfigurationRoot Config;

        private static void Main(string[] args)
        {
            Config = ConfigureAppSettings();

            Console.WriteLine($"Culture:\t{CultureInfo.CurrentCulture.DisplayName}");
            Console.WriteLine($"Timezone:\t{TimeZoneInfo.Local.DisplayName} + {TimeZoneInfo.Local.Id}");
            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 = default(DateTimeOffset);
                var date2 = new DateTimeOffset(1884, 12, 31, 0, 0, 0, 0, new TimeSpan(1, 0, 0));
                var date3 = new DateTimeOffset(1883, 12, 31, 0, 0, 0, 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.TestData.Add(new Table { Id = 3, Date = date3, DateAsString = date3.ToString() });

                db.SaveChanges();
            }

            using (var db = new MyDbContext("Domain"))
            {
                try
                {
                    db.TestData.ToList();
                }
                catch (Exception ex)
                {
                    Console.WriteLine("Exception reading dates" + ex.Message);
                }
            }

            Console.ReadLine();
        }

        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());
                var reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    Console.WriteLine(String.Format("{0}", reader[0]));
                }
            }
        }

        private static IConfigurationRoot ConfigureAppSettings(string fileName = "appsettings")
        {
            var configurationBuilder = new ConfigurationBuilder()
                .SetBasePath(AppDomain.CurrentDomain.BaseDirectory)
                .AddJsonFile($"{fileName}.json", false, true);

            return configurationBuilder.Build();
        }
    }

    public class ConnectionStringSettings
    {
        public string Name { get; set; }

        public string ConnectionString { get; set; }

        public string ProviderName { get; set; }
    }

    public class PgSqlConfiguration : DbConfiguration
    {
        public PgSqlConfiguration()
        {
            SetProviderFactory("Devart.Data.PostgreSql", Devart.Data.PostgreSql.PgSqlProviderFactory.Instance);
            SetProviderServices("Devart.Data.PostgreSql", Devart.Data.PostgreSql.Entity.PgSqlEntityProviderServices.Instance);
            SetDefaultConnectionFactory(new PostgresDbConnectionFactory());
            PgSqlEntityProviderConfig.Instance.DatabaseScript.Schema.DeleteDatabaseBehaviour = DeleteDatabaseBehaviour.Database;
        }
    }

    public class PostgresDbConnectionFactory : IDbConnectionFactory
    {
        private PgSqlProviderFactory ProviderFactory = PgSqlProviderFactory.Instance;

        public DbConnection CreateConnection(string nameOrConnectionString)
        {
            var connection = ProviderFactory.CreateConnection();

            if (nameOrConnectionString.Contains(";"))
            {
                connection.ConnectionString = nameOrConnectionString;
            }
            else
            {
                connection.ConnectionString = Program.Config.GetSection("ConnectionStrings").Get<Dictionary<string, ConnectionStringSettings>>()[nameOrConnectionString].ConnectionString;
            }

            return connection;
        }
    }
}

Output:

Code: Select all

Culture:	und
Timezone:	(UTC+01:00) GMT+01:00 + Europe/Warsaw
Dll Version:	Devart.Data.dll, v5.0.2697.0
Dll Version:	Devart.Data.PostgreSql.dll, v7.20.1905.0
Dll Version:	Devart.Data.PostgreSql.Entity.EF6.dll, v7.20.1905.0
Pg Version:	PostgreSQL 9.6.6, compiled by Visual C++ build 1800, 64-bit
PostgreSQL 9.6.6, compiled by Visual C++ build 1800, 64-bit
Pg Timezone:	Europe/Sarajevo
Europe/Sarajevo

Exception thrown: 'System.ArgumentOutOfRangeException' in System.Private.CoreLib.dll
Exception reading datesThe UTC time represented when the offset is applied must be between year 0 and 10,000. (Parameter 'offset')

As you can see the date:

Code: Select all

default (DateTimeOffset);
is written incorrectly as:

Code: Select all

0001-01-01 00: 58: 00 + 01: 22
an attempt to read ends with an error:

Code: Select all

The UTC time represented when the offset is applied must be between year 0 and 10,000. (Parameter 'offset')

It is also interesting that between 1884 and 1883 there was some change, dates later than 1884 are written as normal, for example:

Code: Select all

"1884-12-31 00: 00: 00 + 01"
and earlier dates have oddly changed time zones:

Code: Select all

"1883-12-31 00: 22: 00 + 01: 22"

Maybe it is connected with:

https://stackoverflow.com/questions/142 ... lliseconds

and

https://en.wikipedia.org/wiki/Internati ... Conference

I think the error is somewhere in this or an analogous piece of code that calculates dates:

Code: Select all

          
          if (string.IsNullOrEmpty(PgSqlLocalization.TimeZone))
          {
            DateTime localTime = DateTime.SpecifyKind(pgSqlTimeStamp.Value, DateTimeKind.Utc).ToLocalTime();
            TimeSpan timeSpan1 = PgSqlLocalization.a(localTime);
            TimeSpan timeSpan2 = PgSqlLocalization.a(new DateTime(localTime.Ticks, DateTimeKind.Local));
            TimeSpan timeSpan3 = timeSpan1 - timeSpan2;
            pgSqlTimeStamp = pgSqlTimeStamp.AddSeconds(timeSpan1.TotalSeconds);
            pgSqlTimeStamp.h = (timeSpan3.Hours * 60 + timeSpan3.Minutes) * 60;
          }
Yes, I do not have PgSqlLocalization.TimeZone set, because I want dates to return with a saved time zone and not a fixed time zone.

I also send a bug report to support.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: strange storing DateTimeOffset

Post by Shalex » Wed 23 Jun 2021 14:26

Does the error persist when you run the same code on Windows?

domdee
Posts: 18
Joined: Mon 19 Nov 2018 15:11

Re: strange storing DateTimeOffset

Post by domdee » Thu 24 Jun 2021 05:19

Running the code on Windows causes the date to be written like this:

Code: Select all

0001-01-01 01:24:00 +01: 24
This is an unusual notation, but still correct.

Let me remind you that on Linux the date is incorrect (unreadable for .net core):

Code: Select all

0001-01-01 00: 58: 00 + 01: 22

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: strange storing DateTimeOffset

Post by Shalex » Thu 24 Jun 2021 15:40

Please run this code on both your Linux and Windows, specify the outputs:

Code: Select all

      var dateTime = new DateTime(1883, 12, 31, 0, 0, 0);
      var offest1 = System.TimeZone.CurrentTimeZone.GetUtcOffset(dateTime);
      var offest2 = new DateTimeOffset(dateTime).Offset;

domdee
Posts: 18
Joined: Mon 19 Nov 2018 15:11

Re: strange storing DateTimeOffset

Post by domdee » Thu 24 Jun 2021 15:49

Test program:

Code: Select all

using System;

namespace ConsoleApp1
{
    class Program
    {
        static void Main(string[] args)
        {
            var dateTime = new DateTime(1883, 12, 31, 0, 0, 0);
            var offest1 = System.TimeZone.CurrentTimeZone.GetUtcOffset(dateTime);
            var offest2 = new DateTimeOffset(dateTime).Offset;

            Console.WriteLine("offest1=" + offest1);
            Console.WriteLine("offest2=" + offest2);
            Console.ReadLine();
                
        }
    }
}

Windows output:

Code: Select all

offest1=01:00:00
offest2=01:00:00

Linux output:

Code: Select all

offest1=01:00:00
offest2=01:24:00

interesting ...

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: strange storing DateTimeOffset

Post by Shalex » Fri 25 Jun 2021 14:36

In PostgreSQL Server, all timezone-aware dates and times are stored internally in UTC. They are converted to local time in the zone specified by the timezone configuration parameter before being displayed to the client.

If you do not set PgSqlLocalization.TimeZone explicitly, the provider will use "new DateTimeOffset(dateTime).Offset" for converting timestamptz value on the client side. On Linux, the result value goes beyond the range of accepted values for System.DateTime when you insert and read default(DateTimeOffset).

Our provider includes the special type PgSqlTimeStamp that handles a wider range of dates than the standard System.DateTime. But PgSqlTimeStamp could not be used in EF6 mapping.

domdee
Posts: 18
Joined: Mon 19 Nov 2018 15:11

Re: strange storing DateTimeOffset

Post by domdee » Sun 27 Jun 2021 20:21

I do not accept such arguments. I wrote a program using EF 6.4 and the NPGSQL driver and ran it on linux. The date is written as:

Code: Select all

0001-01-01 01: 22: 00 + 01: 22
and reads correctly.

Code: Select all

using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using System.Globalization;
using System.IO;
using System.Linq;
using Microsoft.Extensions.Configuration;
using Npgsql;

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

    [DbConfigurationType(typeof(PgSqlConfiguration))]
    public class MyDbContext : DbContext
    {
        public MyDbContext(string nameOrConnectionString) : base(nameOrConnectionString)
        {
            Database.SetInitializer<MyDbContext>(new CreateDatabaseIfNotExists<MyDbContext>());
        }

        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
    {
        public static IConfigurationRoot Config;

        private static void Main(string[] args)
        {
            Config = ConfigureAppSettings();

            Console.WriteLine($"Culture:\t{CultureInfo.CurrentCulture.DisplayName}");
            Console.WriteLine($"Timezone:\t{TimeZoneInfo.Local.DisplayName} + {TimeZoneInfo.Local.Id}");
            //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 = default(DateTimeOffset);
                var date2 = new DateTimeOffset(1884, 12, 31, 0, 0, 0, 0, new TimeSpan(1, 0, 0));
                var date3 = new DateTimeOffset(1883, 12, 31, 0, 0, 0, 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.TestData.Add(new Table { Id = 3, Date = date3, DateAsString = date3.ToString() });

                db.SaveChanges();
            }

            using (var db = new MyDbContext("Domain"))
            {
                try
                {
                    db.TestData.ToList();
                }
                catch (Exception ex)
                {
                    Console.WriteLine("Exception reading dates" + ex.Message);
                }
            }

            Console.ReadLine();
        }

        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());
                var reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    Console.WriteLine(String.Format("{0}", reader[0]));
                }
                reader.Close();
            }
        }

        private static IConfigurationRoot ConfigureAppSettings(string fileName = "appsettings")
        {
            var configurationBuilder = new ConfigurationBuilder()
                .SetBasePath(AppDomain.CurrentDomain.BaseDirectory)
                .AddJsonFile($"{fileName}.json", false, true);

            return configurationBuilder.Build();
        }
    }

    public class ConnectionStringSettings
    {
        public string Name { get; set; }

        public string ConnectionString { get; set; }

        public string ProviderName { get; set; }
    }

    public class PgSqlConfiguration : DbConfiguration
    {
        public PgSqlConfiguration()
        {
            var name = "Npgsql";

            SetProviderFactory(providerInvariantName: name,
                               providerFactory: NpgsqlFactory.Instance);

            SetProviderServices(providerInvariantName: name,
                                provider: NpgsqlServices.Instance);

            SetDefaultConnectionFactory(connectionFactory: new PostgresDbConnectionFactory());
        }
    }

    public class PostgresDbConnectionFactory : IDbConnectionFactory
    {
        private NpgsqlConnectionFactory ProviderFactory = new NpgsqlConnectionFactory();

        public DbConnection CreateConnection(string nameOrConnectionString)
        {
            if (nameOrConnectionString.Contains(";"))
            {
                return ProviderFactory.CreateConnection(nameOrConnectionString);
            }
            else
            {
                return ProviderFactory.CreateConnection(Program.Config.GetSection("ConnectionStrings").Get<Dictionary<string, ConnectionStringSettings>>()[nameOrConnectionString].ConnectionString);
            }
        }
    }
}
In the case of the Devart driver, the default DateTimeOffset is saved as:

Code: Select all

0001-01-01 00: 58: 00 + 01: 22
instead of 1:22 it is 00:58 which causes that if the offset is subtracted, it goes beyond the minimum value.
This is a bug in your codes because the other driver doesn't have such problems.
I have a site license and I do not accept the fact that I pay a lot of money for the license, I get the answer that it is not a bug but a feature. Likewise, I do not accept the use of:

Code: Select all

PgSqlLocalization.TimeZone
because the original time zone (from the moment of writing) is lost after reading.

Please contact me urgently to solve this problem.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: strange storing DateTimeOffset

Post by Shalex » Tue 29 Jun 2021 10:29

domdee wrote: Sun 27 Jun 2021 20:21 I do not accept such arguments. I wrote a program using EF 6.4 and the NPGSQL driver and ran it on linux. The date is written as:

Code: Select all

0001-01-01 01: 22: 00 + 01: 22
and reads correctly.
We will investigate the question and contact you as soon as possible.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: strange storing DateTimeOffset

Post by Shalex » Wed 30 Jun 2021 13:05

We have sent the internal build with the fix to your email.

domdee
Posts: 18
Joined: Mon 19 Nov 2018 15:11

Re: strange storing DateTimeOffset

Post by domdee » Wed 30 Jun 2021 20:24

I have a problem with starting the driver. Well, the target code is in .net core 3.1 and the delivered driver has no nuget packages. This is most likely why when attaching files:

Code: Select all

Devart.Data.dll Devart.Data.Linq.dll Devart.Data.PostgreSql.dll Devart.Data.PostgreSql.Entity.EF6.dll Devart.Data.PostgreSql.Linq.dll
I get an exception:

Code: Select all

An unhandled exception of type 'System.MissingMethodException' occurred in Devart.Data.PostgreSql.dll: 'Method not found:' System.String System.AppDomainSetup.get_LicenseFile () '.'
Stack trace:
 > at Devart.Common.CRLicenseProvider.a (LicenseContext A_0, Type A_1, String & A_2, String & A_3, String & A_4)
 > at Devart.Common.CRLicenseProvider.a (LicenseContext A_0, Type A_1, String & A_2, Boolean & A_3, String & A_4, String & A_5)
 > at Devart.Common.CRLicenseProvider.a (LicenseContext A_0, Type A_1, Boolean A_2)
 > at Devart.Data.PostgreSql.PgSqlProviderFactory.CreateConnection ()
 > at DefaultDateTimeOffsetIssue.PostgresDbConnectionFactory.CreateConnection (String nameOrConnectionString) in c: \ work \ TestDataOffsetDevart \ DefaultDateTimeOffsetIssue \ Program.cs: line 157
 > at System.Data.Entity.Internal.LazyInternalConnection.Initialize ()
 > at System.Data.Entity.Internal.LazyInternalConnection.get_Connection ()
 > at System.Data.Entity.Internal.LazyInternalContext.get_Connection ()
 > at System.Data.Entity.Database.CreateIfNotExists ()
 > at DefaultDateTimeOffsetIssue.Program.Main (String [] args) in c: \ work \ TestDataOffsetDevart \ DefaultDateTimeOffsetIssue \ Program.cs: line 64

Question:
How can I test a change to .net core 3.1 with the internal build you provided me?
Do you have an internal nuget packege feed?

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: strange storing DateTimeOffset

Post by Shalex » Thu 01 Jul 2021 07:00

Please download the corresponding NuGet package from http://download.devart.com/nuget_postgr ... 0_1924.zip.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: strange storing DateTimeOffset

Post by Shalex » Fri 09 Jul 2021 08:46

New build of dotConnect for PostgreSQL 7.20.1930 is available for download now: viewtopic.php?f=3&t=47232.

Post Reply