Re: Daylight saving time rules being applied to DateTimes that don't have a timezone
От | Adrian Klaver |
---|---|
Тема | Re: Daylight saving time rules being applied to DateTimes that don't have a timezone |
Дата | |
Msg-id | a2f72ed7-a451-fb00-d5fd-b24bdebd9c98@aklaver.com обсуждение исходный текст |
Ответ на | Daylight saving time rules being applied to DateTimes that don't have a timezone (Nathan Kendall <fzzwuzzy8@gmail.com>) |
Ответы |
Re: Daylight saving time rules being applied to DateTimes that
don't have a timezone
|
Список | pgsql-odbc |
On 11/16/2016 01:09 PM, Nathan Kendall wrote: Ccing list > On 16/11/2016, Adrian Klaver <adrian.klaver@aklaver.com> wrote: >> 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? > > 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.klaver@aklaver.com >>>> >>> >> >> >> -- >> Adrian Klaver >> adrian.klaver@aklaver.com >> -- Adrian Klaver adrian.klaver@aklaver.com
В списке pgsql-odbc по дате отправления:
Следующее
От: Adrian KlaverДата:
Сообщение: Re: Daylight saving time rules being applied to DateTimes that don't have a timezone