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
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Not sure what this has to do with JDBC ?
Dave Cramer
dave.cramer(at)credativ(dot)ca
http://www.credativ.ca
On Tue, Apr 1, 2014 at 4:18 PM, Kjetil Nygård <polpot78@gmail.com> wrote:
Show quoted text
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
-----------
-2When I remove the extract day from the expression, I get:
test=> select CAST(-3 || ' day' as interval)+now()-now();
?column?
-------------------
-2 days -23:00:00Also note these two:
test=> select '3 day'::interval + now();
?column?
-------------------------------
2014-04-04 22:13:36.144756+02test=> select (CAST(-3 || ' days' as interval)+now());
?column?
-------------------------------
2014-03-29 22:13:38.880739+01Which 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--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
Only that it breaks a test in pgjdbc project.
But the real bug is in PostgreSQL.
1. apr. 2014 22:44 skrev "Dave Cramer" <pg@fastcrypt.com> følgende:
Show quoted text
Not sure what this has to do with JDBC ?
Dave Cramer
dave.cramer(at)credativ(dot)ca
http://www.credativ.caOn Tue, Apr 1, 2014 at 4:18 PM, Kjetil Nygård <polpot78@gmail.com> wrote:
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
-----------
-2When I remove the extract day from the expression, I get:
test=> select CAST(-3 || ' day' as interval)+now()-now();
?column?
-------------------
-2 days -23:00:00Also note these two:
test=> select '3 day'::interval + now();
?column?
-------------------------------
2014-04-04 22:13:36.144756+02test=> select (CAST(-3 || ' days' as interval)+now());
?column?
-------------------------------
2014-03-29 22:13:38.880739+01Which 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--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
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
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
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
-----------
-2I 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...
/messages/by-id/17307.1021949260@sss.pgh.pa.us
/messages/by-id/21758.1363108146@sss.pgh.pa.us
--
Michael
--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc