YET Another UTC Offset issue

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for PostgreSQL
Post Reply
agillesp
Posts: 11
Joined: Fri 03 Jun 2011 07:50

YET Another UTC Offset issue

Post by agillesp » Fri 09 Nov 2018 21:05

We seem to be in the habit of finding your UTC bugs (see a previous post by myself in 2014). Run this code and you'll repeat the issue. Note that this has been tested in MT and PST timezones. I'm unsure if this'll repeat in Germany.

Code: Select all

using System;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Data.Common;
using System.Data.Entity;
using System.Globalization;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity.Infrastructure.Interception;

using Devart.Common;
using Devart.Data.PostgreSql;
using Devart.Data.PostgreSql.Entity.Migrations;

namespace DevartBugTester
{
    // Setup a new DB and execute the following:

    // create table data
    // (
    //   id serial,
    //   date timestamp without time zone,
    //   constraint pk_data primary key (id)
    // )

    public class Data
    {
        public int Id { get; set; }
        public DateTime Date { get; set; }

        public const string Table = "data";
    }

    public class Projection
    {
        public int id { get; set; }
        public DateTimeOffset date { get; set; }
    }

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

        public IDbSet<Data> Data { get; set; }

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

            modelBuilder
                .Entity<Data>()
                .ToTable(DevartBugTester.Data.Table)
                .HasKey(x => x.Id)
                .Property(x => x.Id)
                .HasColumnName("id")
                .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

            modelBuilder.Entity<Data>().Property(x => x.Date).HasColumnName("date");
        }
    }

    public class StoreConfiguration : DbConfiguration
    {
        class CommandInterceptor : IDbCommandInterceptor
        {
            public void NonQueryExecuted(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
            {
            }

            public void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
            {
            }

            public void ReaderExecuted(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
            {
            }

            public void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
            {
                Console.WriteLine();
                Console.WriteLine(command.CommandText);
            }

            public void ScalarExecuted(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
            {
            }

            public void ScalarExecuting(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
            {
            }
        }

        public StoreConfiguration()
        {
            AddInterceptor(new CommandInterceptor());
        }
    }

    class Program
    {
        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 name = "Bug";
            var store = new Store(name);

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

            // Calculate the evil hour.
            //var dsEnd = DateTime.SpecifyKind(new DateTime(2018, 11, 5), DateTimeKind.Utc);
            //var localizedDsEnd = TimeZoneInfo.ConvertTimeFromUtc(dsEnd, TimeZoneInfo.Local);
            //var evilHour = (dsEnd - localizedDsEnd).TotalHours;
            // 
            // More direct way to calculate the evil hour:
            var evilHour = -(TimeZoneInfo.Local.BaseUtcOffset.TotalHours);
            Console.WriteLine($"Evil hour:\t{evilHour}");

            Bootstrap(con, (int)evilHour);

            void test(int id)
            {
                try
                {
                    var item = store
                        .Data
                        .Where(x => x.Id == id)
                        .Select(x => new Projection
                        {
                            date = x.Date,
                            id = x.Id
                        })
                        .Single();

                    Console.WriteLine();
                    Console.WriteLine($"Success:\t{item.id}\t{item.date.UtcDateTime}");
                }
                catch (Exception e)
                {
                    Console.WriteLine();
                    Console.WriteLine(e.Message);
                    Console.WriteLine(e.StackTrace);
                }
            };

            // Success.
            test(1);
            test(2);
            // Fail - causes "The UTC Offset of the local dateTime parameter does not match the offset argument."
            test(3);
        }

        static void Bootstrap(DbConnection connection, int evilHour)
        {
            using (var cmd = connection.CreateCommand())
            {
                void execute(string sql)
                {
                    cmd.CommandText = sql;
                    cmd.ExecuteNonQuery();
                };

                void addTest(int id, int hour)
                {
                    execute($"insert into data values({id},'2018-11-04 {hour}:00:00')");
                };

                execute(
@"create table if not exists data
(
    id serial,
    date timestamp without time zone,
    constraint pk_data primary key (id)
)");
                execute("truncate table data");

                addTest(1, 0);
                addTest(2, evilHour + 1);
                addTest(3, evilHour);
            }
        }

        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());
        }

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

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

Re: YET Another UTC Offset issue

Post by Shalex » Tue 13 Nov 2018 20:18

Our output is:

Code: Select all

Timezone:       (UTC+02:00) Helsinki, Kyiv, Riga, Sofia, Tallinn, Vilnius
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.0, compiled by Visual C++ build 1800, 64-bit
Pg Timezone:    Europe/Helsinki
Evil hour:      -2

SELECT
"Limit1".id,
"Limit1"."C1"
FROM ( SELECT
        "Extent1".id,
        CAST("Extent1"."date" AS timestamptz) AS "C1"
        FROM "data" AS "Extent1"
        WHERE "Extent1".id = (CAST(:p__linq__0 AS int))
        LIMIT 2
)  AS "Limit1"

Success:        1       03.11.2018 22:00:00

SELECT
"Limit1".id,
"Limit1"."C1"
FROM ( SELECT
        "Extent1".id,
        CAST("Extent1"."date" AS timestamptz) AS "C1"
        FROM "data" AS "Extent1"
        WHERE "Extent1".id = (CAST(:p__linq__0 AS int))
        LIMIT 2
)  AS "Limit1"

Success:        2       03.11.2018 22:00:00

SELECT
"Limit1".id,
"Limit1"."C1"
FROM ( SELECT
        "Extent1".id,
        CAST("Extent1"."date" AS timestamptz) AS "C1"
        FROM "data" AS "Extent1"
        WHERE "Extent1".id = (CAST(:p__linq__0 AS int))
        LIMIT 2
)  AS "Limit1"

Success:        3       03.11.2018 22:00:00
Could you please specify your output?

agillesp
Posts: 11
Joined: Fri 03 Jun 2011 07:50

Re: YET Another UTC Offset issue

Post by agillesp » Tue 13 Nov 2018 20:53

Code: Select all

Culture:        English (United States)
Timezone:       (UTC-08:00) Pacific Time (US & Canada)
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.1172.0
Pg Version:     PostgreSQL 10.5 on x86_64-pc-mingw64, compiled by gcc.exe (Rev5, Built by MSYS2 project) 4.9.2, 64-bit
Pg Timezone:    UTC
Evil hour:      8

SELECT
"Limit1".id,
"Limit1"."C1"
FROM ( SELECT
        "Extent1".id,
        CAST("Extent1"."date" AS timestamptz) AS "C1"
        FROM "data" AS "Extent1"
        WHERE "Extent1".id = (CAST(:p__linq__0 AS int))
        LIMIT 2
)  AS "Limit1"

Success:        1       11/4/2018 12:00:00 AM

SELECT
"Limit1".id,
"Limit1"."C1"
FROM ( SELECT
        "Extent1".id,
        CAST("Extent1"."date" AS timestamptz) AS "C1"
        FROM "data" AS "Extent1"
        WHERE "Extent1".id = (CAST(:p__linq__0 AS int))
        LIMIT 2
)  AS "Limit1"

Success:        2       11/4/2018 9:00:00 AM

SELECT
"Limit1".id,
"Limit1"."C1"
FROM ( SELECT
        "Extent1".id,
        CAST("Extent1"."date" AS timestamptz) AS "C1"
        FROM "data" AS "Extent1"
        WHERE "Extent1".id = (CAST(:p__linq__0 AS int))
        LIMIT 2
)  AS "Limit1"

The UTC Offset of the local dateTime parameter does not match the offset argument.
Parameter name: offset
   at System.DateTimeOffset..ctor(DateTime dateTime, TimeSpan offset)
   at Devart.Data.PostgreSql.ab.bf(Byte[] A_0, Int32 A_1, Int32 A_2)
   at Devart.Data.PostgreSql.PgSqlDataReader.GetDateTimeOffset(Int32 i)
   at Devart.Data.PostgreSql.Entity.u.c(Int32 A_0)
   at Devart.Data.PostgreSql.Entity.u.a(Int32 A_0)
   at Devart.Common.Entity.et.GetValue(Int32 ordinal)
   at System.Data.Entity.Core.Common.Internal.Materialization.Shaper.ErrorHandlingValueReader`1.GetUntypedValueDefault(DbDataReader reader, Int32 ordinal)
   at System.Data.Entity.Core.Common.Internal.Materialization.Shaper.ErrorHandlingValueReader`1.GetValue(DbDataReader reader, Int32 ordinal)
   at lambda_method(Closure , Shaper )
   at System.Data.Entity.Core.Common.Internal.Materialization.Coordinator`1.ReadNextElement(Shaper shaper)
   at System.Data.Entity.Core.Common.Internal.Materialization.Shaper`1.SimpleEnumerator.MoveNext()
   at System.Data.Entity.Internal.LazyEnumerator`1.MoveNext()
   at System.Linq.Enumerable.Single[TSource](IEnumerable`1 source)
   at System.Data.Entity.Core.Objects.ELinq.ObjectQueryProvider.<GetElementFunction>b__3[TResult](IEnumerable`1 sequence)
   at System.Data.Entity.Core.Objects.ELinq.ObjectQueryProvider.ExecuteSingle[TResult](IEnumerable`1 query, Expression queryRoot)
   at System.Data.Entity.Core.Objects.ELinq.ObjectQueryProvider.System.Linq.IQueryProvider.Execute[TResult](Expression expression)
   at System.Data.Entity.Internal.Linq.DbQueryProvider.Execute[TResult](Expression expression)
   at System.Linq.Queryable.Single[TSource](IQueryable`1 source)
   at DevartBugTester.Program.<Main>g__test|0_0(Int32 id, <>c__DisplayClass0_0& ) in

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

Re: YET Another UTC Offset issue

Post by Shalex » Wed 14 Nov 2018 14:12

agillesp wrote:
Tue 13 Nov 2018 20:53

Code: Select all

Dll Version:    Devart.Data.PostgreSql.Entity.EF6.dll, v7.11.1172.0
Your application loads old Devart.Data.PostgreSql.Entity.EF6.dll. Its version should be 7.11.1253.0. Does the issue persist with Devart.Data.PostgreSql.Entity.EF6.dll v7.11.1253.0?

JIC: our current output is

Code: Select all

Culture:        English (United States)
Timezone:       (UTC-08:00) Pacific Time (US & Canada)
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 10.0, compiled by Visual C++ build 1800, 64-bit
Pg Timezone:    Europe/Helsinki
Evil hour:      8

SELECT
"Limit1".id,
"Limit1"."C1"
FROM ( SELECT
        "Extent1".id,
        CAST("Extent1"."date" AS timestamptz) AS "C1"
        FROM "data" AS "Extent1"
        WHERE "Extent1".id = (CAST(:p__linq__0 AS int))
        LIMIT 2
)  AS "Limit1"

Success:        1       11/3/2018 10:00:00 PM

SELECT
"Limit1".id,
"Limit1"."C1"
FROM ( SELECT
        "Extent1".id,
        CAST("Extent1"."date" AS timestamptz) AS "C1"
        FROM "data" AS "Extent1"
        WHERE "Extent1".id = (CAST(:p__linq__0 AS int))
        LIMIT 2
)  AS "Limit1"

Success:        2       11/4/2018 7:00:00 AM

SELECT
"Limit1".id,
"Limit1"."C1"
FROM ( SELECT
        "Extent1".id,
        CAST("Extent1"."date" AS timestamptz) AS "C1"
        FROM "data" AS "Extent1"
        WHERE "Extent1".id = (CAST(:p__linq__0 AS int))
        LIMIT 2
)  AS "Limit1"

Success:        3       11/4/2018 6:00:00 AM

agillesp
Posts: 11
Joined: Fri 03 Jun 2011 07:50

Re: YET Another UTC Offset issue

Post by agillesp » Wed 14 Nov 2018 15:53

We’ve found these issues can often be attributed to the Pg time zone being UTC. Please set yours to that and try running again. That will probably repro the issue.

When I get a chance in a few hours I’ll try the newest Devart libs.

Thanks.

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

Re: YET Another UTC Offset issue

Post by Shalex » Wed 14 Nov 2018 16:38

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

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

Re: YET Another UTC Offset issue

Post by Shalex » Thu 29 Nov 2018 15:04

The bug with calculating a time zone in case of "(UTC-08:00) Pacific Time (US & Canada)" is fixed: viewtopic.php?f=3&t=38121.

Post Reply