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

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.


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