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

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?

>
>
>>
>>>
>>> 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