Обсуждение: Timezone error when casting. Maybe daylight saving

I try to build with the tests, but when I run the test
org.postgresql.test.jdbc2.StatementTest.testDateFunctions() i get:
junit.framework.AssertionFailedError: expected:<-3> but was:<-2>


When I run the same function directly in PostgreSQL, I also get -2:

test=> select extract( day from ((CAST(-3 || ' day' as
interval)+now())-now()));
 date_part
-----------
        -2


When I remove the extract day from the expression, I get:
test=> select  CAST(-3 || ' day' as interval)+now()-now();
     ?column?
-------------------
 -2 days -23:00:00


Also note these two:

test=> select '3 day'::interval + now();
           ?column?
-------------------------------
 2014-04-04 22:13:36.144756+02

test=> select  (CAST(-3 || ' days' as interval)+now());
           ?column?
-------------------------------
 2014-03-29 22:13:38.880739+01


Which should give the same timezone, but they are in +01 and +02.

This seems like a timezone problem, but I do not yet fully understand
why. My timezone is "Europe/Oslo". My PostgreSQL version is 9.3.4, from
PostgreSQL's YUM-repo.


PS: I sent this to both bugs and pgjdbc as it is a bug in PostgreSQL and
affects pgjdbc.


Regards,

Kjetil Nygård
Phone: +47 41 47 43 37

Only that it breaks a test in pgjdbc project.

But the real bug is in PostgreSQL.

Kjetil =?ISO-8859-1?Q?Nyg=E5rd?= <polpot78@gmail.com> writes:
> I try to build with the tests, but when I run the test
> org.postgresql.test.jdbc2.StatementTest.testDateFunctions() i get:
> junit.framework.AssertionFailedError: expected:<-3> but was:<-2>

> When I run the same function directly in PostgreSQL, I also get -2:

> test=> select extract( day from ((CAST(-3 || ' day' as
> interval)+now())-now()));
>  date_part
> -----------
>         -2

I get -3 ... unless I set my timezone to Europe/Oslo.  I'm guessing
that you had a daylight savings transition this past weekend?  If so,
the issue is that the query gives a different answer for a couple
of days after a transition, as a consequence of the fact that adding
'1 day' to a timestamp is not the same as adding '24 hours'.

We used to have issues of this sort with the core regression tests;
they'd fail predictably for a couple of days in spring and fall.
Eventually we changed all the test cases to not hit the boundary
condition ... which is arguably a loss of test coverage, but it
wasn't worth the hassle of having unstable regression test results.
It sounds like JDBC's test cases still have the issue.

            regards, tom lane

On Wed, Apr 2, 2014 at 7:43 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Kjetil =?ISO-8859-1?Q?Nyg=E5rd?= <polpot78@gmail.com> writes:
>> I try to build with the tests, but when I run the test
>> org.postgresql.test.jdbc2.StatementTest.testDateFunctions() i get:
>> junit.framework.AssertionFailedError: expected:<-3> but was:<-2>
>
>> When I run the same function directly in PostgreSQL, I also get -2:
>
>> test=> select extract( day from ((CAST(-3 || ' day' as
>> interval)+now())-now()));
>>  date_part
>> -----------
>>         -2
>
> I get -3 ... unless I set my timezone to Europe/Oslo.  I'm guessing
> that you had a daylight savings transition this past weekend?  If so,
> the issue is that the query gives a different answer for a couple
> of days after a transition, as a consequence of the fact that adding
> '1 day' to a timestamp is not the same as adding '24 hours'.
>
> We used to have issues of this sort with the core regression tests;
> they'd fail predictably for a couple of days in spring and fall.
> Eventually we changed all the test cases to not hit the boundary
> condition ... which is arguably a loss of test coverage, but it
> wasn't worth the hassle of having unstable regression test results.
> It sounds like JDBC's test cases still have the issue.
The same question gets asked from time to time...
http://www.postgresql.org/message-id/17307.1021949260@sss.pgh.pa.us
http://www.postgresql.org/message-id/21758.1363108146@sss.pgh.pa.us
--
Michael