Re: [BUGS] Timezone error when casting. Maybe daylight saving

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