Re: Daylight saving time rules being applied to DateTimes that don't have a timezone
От | Nathan Kendall |
---|---|
Тема | Re: Daylight saving time rules being applied to DateTimes that don't have a timezone |
Дата | |
Msg-id | CAOvUx4kJ5C1G=h9ct3GwM87BUBRO980diUaaQXcaKHJzpfgsBg@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: Daylight saving time rules being applied to DateTimes that don't have a timezone (Adrian Klaver <adrian.klaver@aklaver.com>) |
Список | pgsql-odbc |
On 16/11/2016, Adrian Klaver <adrian.klaver@aklaver.com> wrote: > 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? > postgresql.conf has timezone = 'Canada/Atlantic' You conducted your test with data type timestamptz. I am working with columns of type timestamp *without* time zone because I have to support data sources which do not obey daylight saving time rules. When I key in INSERT statements into pgAdmin, it has no trouble with accepting both '2016-03-13 02:00' and '2016-03-13 03:00', without shifting the time by an hour or producing a PK violation, because the column in the database table is defined as timestamp without time zone. > > 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
Следующее
От: Alvaro HerreraДата:
Сообщение: [pongsiri@ttei.toshiba.co.th: Test Connection to PostgreSQL by PostgreSQL ODBC Driver]