Re: Daylight saving time rules being applied to DateTimes that don't have a timezone

On 16/11/2016, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> wrote:
> On 11/16/2016 01:09 PM, Nathan Kendall wrote:
>
> Ccing list
>> On 16/11/2016, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> wrote:
>>> On 11/15/2016 01:51 PM, Nathan Kendall wrote:
>>>> On 15/11/2016, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)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?
>

postgresql.conf has timezone = 'Canada/Atlantic'

You conducted your test with data type timestamptz. I am working with
columns of type timestamp *without* time zone because I have to
support data sources which do not obey daylight saving time rules.
When I key in INSERT statements into pgAdmin, it has no trouble with
accepting both '2016-03-13 02:00' and '2016-03-13 03:00', without
shifting the time by an hour or producing a PK violation, because the
column in the database table is defined as timestamp without time
zone.

>
> 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(dot)klaver(at)aklaver(dot)com
>>>>>
>>>>
>>>
>>>
>>> --
>>> Adrian Klaver
>>> adrian(dot)klaver(at)aklaver(dot)com
>>>
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>