You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
JDBC API mandates the usage of java.sql.Timestamp (that is thin wrapper over java.util.Date) for passing dates to DB and reading dates from DB
in DuckDB JDBC java.sql.Timestamp is currently used for parsing dates, formatting dates and converting it to-from java.time.LocalDateTime
apparently java.sql.Timestamp is completely broken for BCE dates and cannot be used neither for parsing/formatting, nor for converting to/from java.time.LocalDateTime, the following behaviour cannot be correct:
java.time.LocalDateTimeldt = java.time.LocalDateTime.of(-490, 9, 12, 9, 0, 0);
System.out.println("Original LocalDateTime: " + ldt);
// convert to timestamp field-by-fieldjava.sql.Timestampts1 = java.sql.Timestamp.valueOf(ldt);
System.out.println("Timestamp converted field-by-field: " + ts1);
// convert to timestamp using UNIX time offset in millisecondsjava.sql.Timestampts2 = newjava.sql.Timestamp(ldt.toInstant(ZoneOffset.UTC).toEpochMilli());
System.out.println("Timestamp converted using UNIX time offset: " + ts2);
// convert back to LocalDateTime - year value is in CE nowjava.time.LocalDateTimeldt1 = ts1.toLocalDateTime();
System.out.println("LocalDateTime converted back: " + ldt1);
System.out.println(ldt.getYear() == ldt1.getYear());
Original LocalDateTime: -0490-09-12T09:00
Timestamp converted field-by-field: 0491-09-12 09:00:00.0
Timestamp converted using UNIX time offset: 0491-09-17 09:00:00.0
LocalDateTime converted back: 0491-09-12T09:00
false
Apparently nobody uses java.sql.Timestamp in practice (except JDBC drivers) and this problem is a known bug in JDK.
This may be a low priority (CE dates work correctly), but users can realistically load some datasets with historic dates.
To fix this in DuckDB driver, all internal usage of java.sql.Timestamp (and java.sql.Date) functionality needs to be replaced with java.time usage. And java.sql.Timestamp values (coming from client app, or returned to client app) need to be treated only as UNIX time milliseconds offset holders.
The text was updated successfully, but these errors were encountered:
Noticed some discrepancies when using negative year values in tests and tried to look deeper:
java.sql.Timestamp
(that is thin wrapper overjava.util.Date
) for passing dates to DB and reading dates from DBjava.sql.Timestamp
is currently used for parsing dates, formatting dates and converting it to-fromjava.time.LocalDateTime
java.sql.Timestamp
is completely broken for BCE dates and cannot be used neither for parsing/formatting, nor for converting to/fromjava.time.LocalDateTime
, the following behaviour cannot be correct:Apparently nobody uses
java.sql.Timestamp
in practice (except JDBC drivers) and this problem is a known bug in JDK.This may be a low priority (CE dates work correctly), but users can realistically load some datasets with historic dates.
To fix this in DuckDB driver, all internal usage of
java.sql.Timestamp
(andjava.sql.Date
) functionality needs to be replaced withjava.time
usage. Andjava.sql.Timestamp
values (coming from client app, or returned to client app) need to be treated only as UNIX time milliseconds offset holders.The text was updated successfully, but these errors were encountered: