Error: Cannot parse date constant 2042-10-12

[Piercio]

I am using H2 1.1.102 in embedded mode, and could not insert the date
2042-10-12 in the database.
Using squirrel to connect, and running:

call date '2042-10-12';

resulted in the following error:

Error: Cannot parse date constant 2042-10-12, cause:
java.lang.IllegalArgumentException: DAY_OF_MONTH; SQL statement:
call date '2042-10-12' [90009-101]
SQLState: 90009
ErrorCode: 90009

It works for other dates such as 2041-10-11, 2042-10-13, 2041-10-12
etc. It seems to happen only with this specific date.

[Thomas Mueller]

Hi,

It works for me. Could you run this and post the result?

System.getProperties().list(System.out);

Thanks,
Thomas

[Piercio]

This issue is related with the way Java handles DayLight Saving Time.
This particular date (2042-10-12) is the day that daylight saving
starts on my time zone (BRT). So, 2042-10-12 00:00:00 is an invalid
date for java.

The method DateTimeUtils.parseDateTime tries to create this invalid
date using DateTimeUtils.getTime (it sets the time to 00:00:00). This
also happens in DateTimeUtils.CloneAndNormalizeDate.

The workaround we´re using is a custom build of H2 that always sets
the HOUR_OF_DAY to 12 when the type is Value.Date.

Thanks.

The system properties listing follows:

-- listing properties --
java.runtime.name=Java(TM) 2 Runtime Environment, Stand...
sun.boot.library.path=/System/Library/Frameworks/JavaVM.fra...
java.vm.version=1.5.0_16-133
awt.nativeDoubleBuffering=true
gopherProxySet=false
java.vm.vendor=Apple Inc.
java.vendor.url=http://www.apple.com/
path.separator=:
java.vm.name=Java HotSpot(TM) Client VM
file.encoding.pkg=sun.io
user.country=US
sun.java.launcher=SUN_STANDARD
sun.os.patch.level=unknown
java.vm.specification.name=Java Virtual Machine Specification
user.dir=/Users/galmeida/Documents/workspace/h...
java.runtime.version=1.5.0_16-b06-284
java.awt.graphicsenv=apple.awt.CGraphicsEnvironment
java.endorsed.dirs=/System/Library/Frameworks/JavaVM.fra...
os.arch=i386
java.io.tmpdir=/tmp
line.separator=

java.vm.specification.vendor=Sun Microsystems Inc.
os.name=Mac OS X
sun.jnu.encoding=MacRoman
java.library.path=.:/Library/Java/Extensions:/System/Li...
java.specification.name=Java Platform API Specification
java.class.version=49.0
sun.management.compiler=HotSpot Client Compiler
os.version=10.5.5
http.nonProxyHosts=local|*.local|169.254/16|*.169.254/16
user.home=/Users/galmeida
user.timezone=
java.awt.printerjob=apple.awt.CPrinterJob
file.encoding=UTF-8
java.specification.version=1.5
user.name=galmeida
java.class.path=/Users/galmeida/Documents/workspace/h...
java.vm.specification.version=1.0
sun.arch.data.model=32
java.home=/System/Library/Frameworks/JavaVM.fra...
java.specification.vendor=Sun Microsystems Inc.
user.language=en
awt.toolkit=apple.awt.CToolkit
java.vm.info=mixed mode
java.version=1.5.0_16
java.ext.dirs=/Library/Java/Extensions:/System/Libr...
sun.boot.class.path=/System/Library/Frameworks/JavaVM.fra...
java.vendor=Apple Inc.
file.separator=/
java.vendor.url.bug=http://bugreport.apple.com/
sun.cpu.endian=little
sun.io.unicode.encoding=UnicodeLittle
mrj.version=1050.1.5.0_16-284
socksNonProxyHosts=local|*.local|169.254/16|*.169.254/16
ftp.nonProxyHosts=local|*.local|169.254/16|*.169.254/16
sun.cpu.isalist=

On Oct 29, 4:10 pm, "Thomas Mueller" <thomas.tom.muel...@gmail.com>
wrote:

> Hi,
>
> It works for me. Could you run this and post the result?
>
> System.getProperties().list(System.out);
>
> Thanks,
> Thomas
>

[Thomas Mueller]

Hi,

I can now reproduce this problem. It occurs in times zones were the
daylight saving time ends at midnight, for example Brasilia. There was
a similar problem for Chile before, but the exception in that case is
invalid HOUR_OF_DAY. A workaround for this problem is implemented. For
the timezone Brasilia, it looks like the exception is invalid
DAY_OF_MONTH for years larger than 2037.

A solution is to use hour 12 instead of 0. The only problem is that
casting a date to a timestamp will now use the hour 12 instead of 0
(midnight):

select cast(date '2042-10-12' as timestamp) from test;

will return 2042-10-12 12:00:00.0. For HSQLDB it returns 2042-10-11
23:00:00.0. Derby doesn't allow to cast a date to a timestamp, and
PostgreSQL and MySQL use hour 0 (they are not written in Java).

Also, if ResultSet.get...() returns a java.sql.Date, that will
sometimes not be equal to a Date generated with
java.sql.Date.valueOf(...). Not sure if this can be solved. By the way
java.sql.Date.valueOf("2042-10-12").toString() returns 2042-10-11 when
using the time zone Brasilia...

Regards,
Thomas

On Thu, Oct 30, 2008 at 12:07 PM, galmeida <galm...@a0z.org> wrote:
> Hi Thomas,
> I work with piercio (who reported this problem), I'm writing to say we
> can provide you any info, test cases, whatever you need to help us
> solve this problem, also if you prefer to use IM, i'm online on
> GoogleTalk (galmeida at letter a, number zero, letter z, dot, org) and
> AIM (GustavoFromHell), thank you very much.

>
> On Oct 29, 4:10 pm, "Thomas Mueller" <thomas.tom.muel...@gmail.com>
> wrote:

>> Hi,
>>
>> It works for me. Could you run this and post the result?
>>
>> System.getProperties().list(System.out);
>>
>> Thanks,
>> Thomas
>>

[Thomas Mueller]

Hi,

FYI I am discussing this problem with Apache Derby and HSQLDB. Here is
a simple test case:
import java.sql.Date;
import java.util.TimeZone;
public class DateProblem {
public static void main(String[] a) {
System.out.println(System.getProperty("java.runtime.version"));
System.out.println(System.getProperty("os.name"));
String[] ids = TimeZone.getAvailableIDs();
for (int i = 0; i < ids.length; i++) {
TimeZone.setDefault(TimeZone.getTimeZone(ids[i]));
for (int y = 2037; y < 2039; y++) {
for (int m = 101; m < 113; m++) {
for (int d = 101; d < 129; d++) {
test(y, m, d);
}
}
}
}
}
static void test(int y, int m, int d) {
String s = y+"-"+(""+m).substring(1)+"-"+(""+d).substring(1);
String s2 = Date.valueOf(s).toString();
if (!s.equals(s2)) {
String e = "Date.valueOf(\""+s+"\")="+s2;
e += " " + TimeZone.getDefault().getID();
System.out.println(e);
}
}
}
I get:
1.6.0_05-b13
Windows XP
Date.valueOf("2038-03-14")=2038-03-13 America/Havana
Date.valueOf("2038-03-14")=2038-03-13 Cuba
Date.valueOf("2038-10-17")=2038-10-16 America/Asuncion
Date.valueOf("2038-10-10")=2038-10-09 America/Campo_Grande
Date.valueOf("2038-10-10")=2038-10-09 America/Cuiaba
Date.valueOf("2038-10-10")=2038-10-09 America/Santiago
Date.valueOf("2038-10-10")=2038-10-09 Antarctica/Palmer
Date.valueOf("2038-10-10")=2038-10-09 Chile/Continental
Date.valueOf("2038-10-03")=2038-10-02 AGT
Date.valueOf("2038-10-03")=2038-10-02 America/Argentina/Buenos_Aires
Date.valueOf("2038-10-03")=2038-10-02 America/Argentina/Catamarca
Date.valueOf("2038-10-03")=2038-10-02 America/Argentina/ComodRivadavia
Date.valueOf("2038-10-03")=2038-10-02 America/Argentina/Cordoba
Date.valueOf("2038-10-03")=2038-10-02 America/Argentina/Jujuy
Date.valueOf("2038-10-03")=2038-10-02 America/Argentina/La_Rioja
Date.valueOf("2038-10-03")=2038-10-02 America/Argentina/Mendoza
Date.valueOf("2038-10-03")=2038-10-02 America/Argentina/Rio_Gallegos
Date.valueOf("2038-10-03")=2038-10-02 America/Argentina/San_Juan
Date.valueOf("2038-10-03")=2038-10-02 America/Argentina/Tucuman
Date.valueOf("2038-10-03")=2038-10-02 America/Argentina/Ushuaia
Date.valueOf("2038-10-03")=2038-10-02 America/Buenos_Aires
Date.valueOf("2038-10-03")=2038-10-02 America/Catamarca
Date.valueOf("2038-10-03")=2038-10-02 America/Cordoba
Date.valueOf("2038-10-03")=2038-10-02 America/Jujuy
Date.valueOf("2038-10-03")=2038-10-02 America/Mendoza
Date.valueOf("2038-10-03")=2038-10-02 America/Rosario
Date.valueOf("2038-10-10")=2038-10-09 America/Sao_Paulo
Date.valueOf("2038-10-10")=2038-10-09 BET
Date.valueOf("2038-10-10")=2038-10-09 Brazil/East
Date.valueOf("2038-03-28")=2038-03-27 America/Scoresbysund
Date.valueOf("2038-03-28")=2038-03-27 Atlantic/Azores
Date.valueOf("2038-03-25")=2038-03-24 Asia/Amman
Date.valueOf("2038-03-28")=2038-03-27 Asia/Beirut
Date.valueOf("2038-03-26")=2038-03-25 Asia/Damascus
Date.valueOf("2038-04-01")=2038-03-31 Asia/Gaza
Date.valueOf("2038-03-21")=2038-03-20 Asia/Tehran
Date.valueOf("2038-03-21")=2038-03-20 Iran

Regards,
Thomas

[Thomas Mueller]

Hi,

I submitted a bug to http://bugreport.sun.com/bugreport/index.jsp
(JSE). The internal review id is 1384760:
"java.sql.Date.valueOf("2042-10-xx").toString() wrong in some time
zones". I think the bug is not public yet. I will keep you updated.

I have also posted this problem to Apache Derby and HSQLDB, but I
didn't get much feedback:
http://sourceforge.net/forum/message.php?msg_id=5548088
http://mail-archives.apache.org/mod_mbox/db-derby-user/200810.mbox/%3c5f211bd50810310720l53...@mail.gmail.com%3e

In H2, I will implement a workaround in the next release. For the
problematic dates in those timezones, the java.sql.Date objects
returned by the database will have the correct value when calling
toString(), however they will have hour set to 12. This is not a
perfect solution, but (I hope) has minimal impact on existing
applications.

I have added a feature request for: "Avoid using java.util.Calendar
internally because it's slow, complicated, and seems to be buggy".
However it will take some time until this is done, and it can't solve
problems with java.sql.Date / Time / Timestamp.

Regards,
Thomas

On Fri, Oct 31, 2008 at 4:19 PM, Thomas Mueller

[galmeida]

thank you very much Thomas

On Nov 3, 3:39 pm, "Thomas Mueller" <thomas.tom.muel...@gmail.com>
wrote:
> Hi,
>
> I submitted a bug tohttp://bugreport.sun.com/bugreport/index.jsp

> (JSE). The internal review id is 1384760:
> "java.sql.Date.valueOf("2042-10-xx").toString() wrong in some time
> zones". I think the bug is not public yet. I will keep you updated.
>
> I have also posted this problem to Apache Derby and HSQLDB, but I
> didn't get much feedback:http://sourceforge.net/forum/message.php?msg_id=5548088

> http://mail-archives.apache.org/mod_mbox/db-derby-user/200810.mbox/%3c5f211 bd50810310720l531b597fv3c4149e51eaf0...@mail.gmail.com%3e

>
> In H2, I will implement a workaround in the next release. For the
> problematic dates in those timezones, the java.sql.Date objects
> returned by the database will have the correct value when calling
> toString(), however they will have hour set to 12. This is not a
> perfect solution, but (I hope) has minimal impact on existing
> applications.
>
> I have added a feature request for: "Avoid using java.util.Calendar
> internally because it's slow, complicated, and seems to be buggy".
> However it will take some time until this is done, and it can't solve
> problems with java.sql.Date / Time / Timestamp.
>
> Regards,
> Thomas
>
> On Fri, Oct 31, 2008 at 4:19 PM, Thomas Mueller
>

[Thomas Mueller]

Hi,

FYI there is now a bug:
http://bugs.sun.com/bugdatabase/view_bug.do?bug_id=6772689
You can vote for it if you want. I guess it would be resolved quicker
if there is a patch. The problem seems to be related to
http://en.wikipedia.org/wiki/Year_2038_problem

Regards,
Thomas