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

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?

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