Re: Daylight saving time rules being applied to DateTimes that don't have a timezone - Mailing list pgsql-odbc

On 11/16/2016 01:09 PM, Nathan Kendall wrote:

Ccing list
> On 16/11/2016, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
>> On 11/15/2016 01:51 PM, Nathan Kendall wrote:
>>> On 15/11/2016, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
>>>> On 11/10/2016 06:45 AM, Nathan Kendall wrote:
>>>>> We are having a problem with psqlODBC versions 09.05.0100 through
>>>>> 09.05.0400.  With the database table schema listed below, the sample
>>>>> C# test app below that fails with a violation of the primary key.
>>>>> However, the same C# code works without issue when using psqlODBC
>>>>> version 09.03.0400 or older.  It would seem as though psqlODBC
>>>>> versions from 09.05.0100 onward are applying daylight saving time
>>>>> rules to DateTimes that are specified as not being associated with a
>>>>> timezone.
>>>>
>>>> What is the error you are getting back or the value you are seeing that
>>>> is different?
>>>
>>> With psqlODBC version 09.05.x, the DateTime value of '2016-03-13
>>> 02:00:00' is getting pushed ahead by an hour and is appearing in the
>>> database as '2016-03-13 03:00:00'.  This is causing the following
>>> iteration of the loop ('2016-03-13 03:00:00') to fail with an
>>> exception of type 'System.Data.Odbc.OdbcException': ERROR [23505]
>>> ERROR: duplicate key value violates unique constraint "time_test_pk";
>>>
>>> Error while executing the query
>>>
>>> Perhaps I should also mention that I am working on computers whose
>>> operating system level timezone is set to Atlantic Standard Time,
>>> which is one hour ahead of the time in New York.  However, the
>>> timezone should not be getting applied to a column of type "timestamp
>>> without time zone".  As mentioned previously, there was no problem
>>> with psqlODBC 09.03.0400.
>>
>> Does the above mean the client you are entering the data on is in New York?
>
> No, I only mentioned New York as a reference point.  The client and
> server operating systems are both running on the Atlantic Time.  The
> significance of 2016-03-13 is that daylight saving time begins at 2am
> on the second Sunday of March in most of North America.

Yeah,  03/13/2016 2:00:00 and 03/13/2016 3:00:00 are the same time:

test[5432]=# set timezone = 'America/Halifax';
SET
test[5432]=# select '03/13/2016 2:00:00'::timestamptz at time zone 'utc';
       timezone
---------------------
  2016-03-13 06:00:00
(1 row)

test[5432]=# select '03/13/2016 3:00:00'::timestamptz at time zone 'utc';
       timezone
---------------------
  2016-03-13 06:00:00
(1 row)

So to be strictly accurate the PK constraint error is actually valid.
The question remains though, what caused the change in behavior. Before
I forget:

What is the TimeZone set to in postgresql.conf?


The answer to why the behavior changed I suspect lies in:

https://odbc.postgresql.org/docs/release.html

psqlODBC 09.05.0100 Release

Use libpq for all communication with the server
Previously, libpq was only used for authentication. Using it for all
communication lets us remove a lot of duplicated code. libpq is now
required for building or using libpq.


Send datatype information for query parameters, when known
If a query parameter is bound with a specific SQL type, pass on that
information to the server. This makes the behaviour of queries like
"SELECT '555' > ?" more sensible, where the result depends on whether
the query parameter is interpreted as an integer or a string.


I'm afraid it is going to take someone more knowledgeable of the
internals to fully answer this though.


>
>>
>>>
>>>
>>>>
>>>>>
>>>>> Nathan
>>>>>
>>>>>
>>>>> CREATE TABLE time_test
>>>>> (
>>>>>   stationid character varying(8) NOT NULL,
>>>>>   date_time timestamp without time zone NOT NULL,
>>>>>   temperature double precision,
>>>>>   CONSTRAINT time_test_pk PRIMARY KEY (stationid, date_time)
>>>>> );
>>>>>
>>>>>
>>>>> C# Test App Source Code:
>>>>>
>>>>> using System;
>>>>> using System.Data.Odbc;
>>>>>
>>>>> namespace TimeTestApp1
>>>>> {
>>>>>   class Program
>>>>>   {
>>>>>     static void Main(string[] args)
>>>>>     {
>>>>>       using (var conn = new OdbcConnection("Driver={PostgreSQL
>>>>> ANSI(x64)};Server=localhost;Database=weather;Uid=userGoesHere;Pwd=passGoesHere;"))
>>>>>       {
>>>>>         conn.Open();
>>>>>         using (var command = new OdbcCommand("", conn))
>>>>>         {
>>>>>           command.Parameters.Add(new OdbcParameter("stationid",
>>>>> OdbcType.VarChar));
>>>>>           command.Parameters.Add(new OdbcParameter("date_time",
>>>>> OdbcType.DateTime));
>>>>>           command.Parameters.Add(new OdbcParameter("temperature",
>>>>> OdbcType.Double));
>>>>>           command.CommandText = "INSERT INTO time_test (stationid,
>>>>> date_time, temperature) VALUES (?, ?, ?)";
>>>>>           command.CommandTimeout = 60;
>>>>>           command.Prepare();
>>>>>
>>>>>           DateTime[] testTimes = new DateTime[] {
>>>>>             new DateTime(2016, 3, 13, 1, 0, 0,
>>>>> DateTimeKind.Unspecified),
>>>>>             new DateTime(2016, 3, 13, 2, 0, 0,
>>>>> DateTimeKind.Unspecified),
>>>>>             new DateTime(2016, 3, 13, 3, 0, 0,
>>>>> DateTimeKind.Unspecified)
>>>>>           };
>>>>>
>>>>>           int i = 0;
>>>>>           foreach (var dt in testTimes)
>>>>>           {
>>>>>             i++;
>>>>>             command.Parameters[0].Value = "Place";
>>>>>             command.Parameters[1].Value = dt;
>>>>>             command.Parameters[2].Value = 60 - i;
>>>>>             command.ExecuteNonQuery();
>>>>>           }
>>>>>         }
>>>>>       }
>>>>>     }
>>>>>   }
>>>>> }
>>>>>
>>>>>
>>>>
>>>>
>>>> --
>>>> Adrian Klaver
>>>> adrian.klaver@aklaver.com
>>>>
>>>
>>
>>
>> --
>> Adrian Klaver
>> adrian.klaver@aklaver.com
>>


--
Adrian Klaver
adrian.klaver@aklaver.com