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

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?

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