java.sql.Timestamp incorrect timezone shift

[zhming0]

Summary

The DuckDB JDBC driver has timezone bugs in java.sql.Timestamp handling. Both write and read paths have compensating bugs that make JDBC roundtrips appear correct while silently corrupting the stored data.

For now, I can get around this issue by using LocalDateTime or OffsetDateTime types.

Reproduction

import java.sql.*;
import java.time.*;
import java.util.TimeZone;

public class TimestampBug {
    public static void main(String[] args) throws Exception {
        TimeZone.setDefault(TimeZone.getTimeZone("America/Los_Angeles")); // UTC-8
        
        try (Connection conn = DriverManager.getConnection("jdbc:duckdb:")) {
            // Create timestamp: 2024-01-01 12:00 LA time = 2024-01-01 20:00 UTC
            Timestamp original = Timestamp.valueOf("2024-01-01 12:00:00");
            System.out.println("Original: " + original + " (epoch ms: " + original.getTime() + ")");
            System.out.println("This represents UTC time: " + LocalDateTime.ofInstant(
                Instant.ofEpochMilli(original.getTime()), ZoneOffset.UTC));
            
            try (Statement s = conn.createStatement()) {
                s.execute("CREATE TABLE test(ts TIMESTAMP)");
            }
            try (PreparedStatement ps = conn.prepareStatement("INSERT INTO test VALUES (?)")) {
                ps.setTimestamp(1, original);
                ps.execute();
            }
            
            // Check what's actually stored in DuckDB
            try (Statement s = conn.createStatement();
                 ResultSet rs = s.executeQuery("SELECT epoch_us(ts) as micros FROM test")) {
                rs.next();
                long storedMicros = rs.getLong(1);
                long expectedMicros = original.getTime() * 1000;
                
                System.out.println("\nStored epoch micros: " + storedMicros);
                System.out.println("Expected epoch micros: " + expectedMicros);
                System.out.println("Stored as UTC time: " + LocalDateTime.ofEpochSecond(
                    storedMicros / 1000000, 0, ZoneOffset.UTC));
                System.out.println("\nBUG: Storage is " + 
                    ((expectedMicros - storedMicros) / 3600000000L) + " hours off!");
            }
        }
    }
}

To run:

javac -cp duckdb_jdbc.jar TimestampBug.java
java -cp duckdb_jdbc.jar:. TimestampBug

Expected output

Original: 2024-01-01 12:00:00.0 (epoch ms: 1704139200000)
This represents UTC time: 2024-01-01T20:00

Stored epoch micros: 1704139200000000
Expected epoch micros: 1704139200000000
Stored as UTC time: 2024-01-01T20:00

Actual output (v1.4.3.0)

Original: 2024-01-01 12:00:00.0 (epoch ms: 1704139200000)
This represents UTC time: 2024-01-01T20:00

Stored epoch micros: 1704110400000000
Expected epoch micros: 1704139200000000
Stored as UTC time: 2024-01-01T12:00

BUG: Storage is 8 hours off!

The timestamp is shifted by +8 hours (the UTC offset of America/Los_Angeles applied incorrectly).

Root Cause

The bug is in DuckDBTimestamp.java. DuckDB stores TIMESTAMP as UTC epoch microseconds internally, but the Java conversion code uses timezone-dependent methods:

Write path (DuckDBTimestamp constructor):

public DuckDBTimestamp(Timestamp sqlTimestamp) {
    // BUG: toLocalDateTime() returns wall-clock time in system timezone but RefLocalDateTime is a UTC wall-clock time.
    this.timeMicros = DuckDBTimestamp.RefLocalDateTime.until(
        sqlTimestamp.toLocalDateTime(), ChronoUnit.MICROS);
}

The issue is that Timestamp.toLocalDateTime() returns the wall-clock time in the system timezone, but RefLocalDateTime.until() measures microseconds as if it were UTC.

Read path (DuckDBTimestamp.toSqlTimestamp()):

public Timestamp toSqlTimestamp() {
    // BUG: Timestamp.valueOf() interprets LocalDateTime in system timezone
    return Timestamp.valueOf(this.toLocalDateTime());
}

Similarly, toLocalDateTime() returns UTC wall-clock time, but Timestamp.valueOf() interprets it in the system timezone.

[staticlibs]

@zhming0

Hi, thanks for the report! For some reason I cannot reproduce this on your example. With setting system timezone explicitly to non-UTC one:

$ sudo timedatectl set-timezone Australia/Darwin
$ date
Sun Jan  4 01:23:55 AM ACST 2026

I am getting the following output:

Original: 2024-01-01 12:00:00.0 (epoch: 1704139200000)
Result:   2024-01-01 12:00:00.0 (epoch: 1704139200000)
Match: true

Perhaps some details were not included in the example? I was running this on 1.4.3.0.

In general timezone-handling in JDBC must follow the invariants described in #166. And current timestamp conversion, besides having known problems (like #200), is pretty messy, if you will see improvements (like in "Root Cause" paragraph) that can be added without breaking invariants from #166 - PRs are welcome!

[zhming0]

@staticlibs My apology! I have updated the reproduce script and description, now it should reliably reproduce the issue on 1.4.3.0. So on 1.4.3.0, round trip is okay, because two bugs cancelled each other out.

I had some attempts trying to fix up the issue, I must've confused myself on which jar is for which version 🤦🏿 .

Some of the existing test cases introduced by #166 seems to be incorrect. For example:

s.executeQuery("SELECT '2020-01-01 01:23:45.678901'::TIMESTAMP WITHOUT TIME ZONE")
...
assertEquals(1577834625678L, rs.getTimestamp(1).getTime()); // WRONG, it should be 1577841825678L instead 

See the result returned directly by duckdb cli:

D SELECT epoch_ms('2020-01-01 01:23:45.678901'::TIMESTAMP WITHOUT TIME ZONE);
┌───────────────────────────────────────────────────────────┐
│ epoch_ms(CAST('2020-01-01 01:23:45.678901' AS TIMESTAMP)) │
│                           int64                           │
├───────────────────────────────────────────────────────────┤
│                       1577841825678                       │
│                      (1.58 trillion)                      │
└───────────────────────────────────────────────────────────┘

I am happy to make PRs but the change might be extensive, so wanting to reach out first.

[staticlibs]

@zhming0

I have updated the reproduce script and description, now it should reliably reproduce the issue on 1.4.3.0

The updated example gives me the same result when running in Postgres, using this query:

SELECT EXTRACT(EPOCH FROM ts) * 1000000 FROM test

So this implementation in DuckDB is intentional.

Some of the existing test cases introduced by #166 seems to be incorrect.

Again, Postgres gives me the same result for this example.

It may be argued, that interpretation of the required JDBC timezone-conversions in Postgres is incorrect, but there is a lot of Java code that depend on this interpretation, and in DuckDB we are trying to be consistent with Postgres' behaviour where reasonable.

I am happy to make PRs but the change might be extensive, so wanting to reach out first.

Extensive changes in this area are acceptable, assuming the invariants described in #166 are not getting broken.

[zhming0]

Ah, I was not aware that PostgreSQL JDBC has the same issue, I verified it and it's true 🫠 🫠 . I can confirm with DuckDB/PG alone, SELECT epoch_ms('2020-01-01 01:23:45.678901'::TIMESTAMP WITHOUT TIME ZONE) yield 1577841825678, but with DuckDB/PG JDBC, the same statement results in 1577834625678 (hours off) being returned.

If the intention is to have a bug-for-bug like similarity, then I suppose we should close this issue 😬 . Thanks for your time @staticlibs !