Timestamp handling is broken for BCE dates
Nobody has claimed this yet.
Assessment
- Difficulty
- 5/5
- Estimated time
- Over a week
- Newbie friendliness
- 35/100
Research direction
Start by tracing the DuckDB JDBC driver's internal uses of java.sql.Timestamp and java.sql.Date for parsing, formatting, and conversion to and from java.time.LocalDateTime. Reproduce the BCE example and verify that client-provided and returned Timestamp values use UNIX millisecond offsets while BCE dates round-trip correctly.
Written by the indexing model from the issue text.
Description
Noticed some discrepancies when using negative year values in tests and tried to look deeper:
- historically Java has 2 different date-time APIs - java.util.Date + java.util.Calendar from Java 1.1 and java.time from Java 8
- JDBC API mandates the usage of
java.sql.Timestamp(that is thin wrapper overjava.util.Date) for passing dates to DB and reading dates from DB - in DuckDB JDBC
java.sql.Timestampis currently used for parsing dates, formatting dates and converting it to-fromjava.time.LocalDateTime - apparently
java.sql.Timestampis 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:
java.time.LocalDateTime ldt = java.time.LocalDateTime.of(-490, 9, 12, 9, 0, 0);
System.out.println("Original LocalDateTime: " + ldt);
// convert to timestamp field-by-field
java.sql.Timestamp ts1 = java.sql.Timestamp.valueOf(ldt);
System.out.println("Timestamp converted field-by-field: " + ts1);
// convert to timestamp using UNIX time offset in milliseconds
java.sql.Timestamp ts2 = new java.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 now
java.time.LocalDateTime ldt1 = 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.
- Dominant language
- C++
- Stars
- 127
- Forks
- 80
- Avg merge
- 13h 41m
- Merged PRs (30d)
- 44
Contributor guide
No contributing guide indexed for this repository
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
More from duckdb/duckdb-java
-
Difficulty 2/5 1-3 hours Newbie friendliness 72/100
duckdb/duckdb-java#832 · 2 comments ·
-
Native SIGSEGV in DuckDB JDBC when repeatedly executing queries with window functions and LIMIT 0 Open
Difficulty 4/5 3-5 days Newbie friendliness 48/100
duckdb/duckdb-java#871 · 4 comments ·
-
Difficulty 4/5 3-5 days Newbie friendliness 45/100
duckdb/duckdb-java#872 ·
-
Difficulty 5/5 Over a week Newbie friendliness 35/100
duckdb/duckdb-java#837 ·
-
executeBatch()` on a prepared INSERT runs s are ~1–2 orders of magnitude slower than the Appender Open
Difficulty 5/5 Over a week Newbie friendliness 42/100
duckdb/duckdb-java#815 · 1 comment ·
All issues in duckdb/duckdb-java
Similar issues
-
Difficulty 1/5 Under an hour Newbie friendliness 90/100
AXERA-TECH/ax-llm#77 ·
-
Difficulty 1/5 Under an hour Newbie friendliness 90/100
games-on-whales/wolf#509 ·
-
Difficulty 2/5 1-3 hours Newbie friendliness 74/100
-
bug-unconfirmed
Difficulty 2/5 1-3 hours Newbie friendliness 76/100
-
Difficulty 2/5 1-3 hours Newbie friendliness 74/100
NVIDIA/cuda-samples#453 ·