Some values can not be stored into a column of type "Timestamp With Timezone". They will be changed

[blo-kisters-de]

DuckDB version 1.4.3

Using the data type TimestampTZ (Timestamp with Time Zone) some timestamps can not be saved correctly. We give an example below for the Timestamp ISO: 2025-10-25T23:15:00Z

The underlaying JAVA-implementation uses OffsetDateTime to bind the millisecond-value with the db-column.

We give the code in KiScript, that uses the JDBC-driver, prepared statements and so on:

// create a table with one column ts of time TimestampTZ
DuckDB.createTable(c, "test1", Reader.emptyRow("Timestamp ts").desc())

// prepare an insert-statement
let w = c.prepare("insert into test1 (ts) values (?)")

// Write 1761434100000L, which is ms since 1970-01-01: ISO: 2025-10-25T23:15:00Z,
w.setTimestamp(0, 1761434100000L)
w.execute()

// Read the value back from database:
Reader r = c.select("* from test1")
r.next()
(r[0] as Timestamp).asStringISO()
// The result (Timestamp ISO-Format) will be 2025-10-25T22:15:00Z
// the millisecond value is: 1761430500000L

c.select("data_type from information_schema.columns where table_name = 'test1' and column_name = 'ts'")
=>
TIMESTAMP WITH TIME ZONE

Most timestamps are correct, some can not be stored and will be mapped to a different timestamp.

The expectation is that for all written timestamps the millisecond-value (UTC since 1970-01-01) the value does not change after read.

Interestingly, if we try to insert the Timestamp without bindings, the result is different, but also not correct:

// add the above timestamp as ISO-Timestamp
c.execute("insert into test1 (ts) values ('2025-10-25T23:15:00Z')")

// read it back
c.select("* from test1")
you will get:
1761437700000 which is 2025-10-26T00:15:00Z in ISO format

Unfortunately this lack is crucial for time series evaluations. Our work - around is not to use TimestampTZ columns but a bigint to store the milliseconds directly.

[staticlibs]

Hi, thanks for the report! Can you provide a problematic example in Java, something like this one:

try (Connection conn = DriverManager.getConnection("jdbc:duckdb:");
    PreparedStatement ps = conn.prepareStatement("SELECT ?::TIMESTAMP")) {
    ps.setObject(1, LocalDateTime.of(2025, 10, 25,23, 15));
    try (ResultSet rs = ps.executeQuery()) {
        while (rs.next()) {
            LocalDateTime ldt = rs.getObject(1, LocalDateTime.class);
            System.out.println(ldt);
        }
    }
}

Timezone handling per JDBC spec is pretty complex, see details in #166. TIMESTAMP WITH TIME ZONE in DuckDB complicates it even more, because it does not store the timezone and it is pretty easy to accidentally apply the timezone shift twice in timestamps processing.

Our work - around is not to use TimestampTZ columns but a bigint to store the milliseconds directly.

This is the recommended approach for analytics workloads, when the application is not some generic BI, but can process timestamps properly on the client side. For example, when exporting a few tens GiBs into Parquet, one is unlikely interested in doing historic timezone lookup per row just because it is mandated by JDBC spec.

[blo-kisters-de]

Thank you for your hints. Here is some JAVA code to replicate the problem:
This will fail:

  try (Connection conn = DriverManager.getConnection("jdbc:duckdb:");
        PreparedStatement ps = conn.prepareStatement("SELECT ?::TIMESTAMPTZ")) {
        Long ts = 1743294600000L;
        OffsetDateTime odt = OffsetDateTime.ofInstant(Instant.ofEpochMilli(ts), GMT);
        ps.setObject(1, odt);
        try (ResultSet rs = ps.executeQuery()) {
            while (rs.next()) {
              OffsetDateTime odt1 = rs.getObject(1, OffsetDateTime.class);
                long ts1 = odt1.toInstant().toEpochMilli();
                if (ts1 - ts != 0) {
                  throw new Exception(String.format("Difference between input and output should be 0, actual %d", ts1 - ts));
                }
            }
        }
    }

and this, too:

    try (Connection conn = DriverManager.getConnection("jdbc:duckdb:");
        PreparedStatement ps = conn.prepareStatement("SELECT ?::TIMESTAMP")) {
        Long ts = 1743294600000L;
        Timestamp sqlts = new Timestamp(ts);
        ps.setObject(1, sqlts);
        try (ResultSet rs = ps.executeQuery()) {
            while (rs.next()) {
                LocalDateTime ldt1 = rs.getObject(1, LocalDateTime.class);
                long ts1 = ldt1.atZone(GMT).toInstant().toEpochMilli();
                if (ts1 - ts != 0) {
                  throw new Exception(String.format("Difference between input and output should be 0, actual %d", ts1 - ts));
                }
            }
        }
    }

The DuckDB-Appender has other problems:
Timestamptz is in this snippet ok. In my environment I have difference, I will investigate that later and give an example in JAVA.
In this snippet you can see that a written Timestamp with DuckDBAppender can be different after read.

   try (Connection conn = DriverManager.getConnection("jdbc:duckdb:")) {
        PreparedStatement ps = conn.prepareStatement("create table A (ts Timestamp, tsz Timestamptz)");
        ps.execute();
        Long ts = 1743294600000L;
        try (DuckDBAppender app = ((DuckDBConnection) conn).createAppender("", "A")) {
          app.beginRow();
          Timestamp sqlts = new Timestamp(ts);
          app.append(sqlts);
          // SQLTimestamp not supported for Timestamptz, use OffsetDateTime
          OffsetDateTime odt = OffsetDateTime.ofInstant(Instant.ofEpochMilli(ts), GMT);
          app.append(odt);
          app.endRow();

          app.beginRow();
          LocalDateTime ldt = LocalDateTime.ofInstant(Instant.ofEpochMilli(ts), GMT);
          app.append(ldt);
          app.append(odt);
          app.endRow();
          
          
          app.flush();
        }

        ps = conn.prepareStatement("SELECT ts, tsz from A");
        
        try (ResultSet rs = ps.executeQuery()) {
            StringBuilder sb = new StringBuilder();
            while (rs.next()) {
                Timestamp sqlts1 = rs.getObject(1, Timestamp.class);
                Timestamp sqlts1TZ = rs.getObject(2, Timestamp.class);
                long ts1 = sqlts1.getTime();
                long ts1TZ = sqlts1TZ.getTime();
                
                if (ts1 - ts != 0) {
                  sb.append(String.format("Difference between input and output should be 0 for column type Timestamp, actual %d%n", ts1-ts));
                }
                if (ts1TZ - ts != 0) {
                  sb.append(String.format("Difference between input and output should be 0 for column type TimestampTZ, actual %d%n", ts1TZ-ts));
                }
            }
            if (sb.length() > 0) {
              throw new Exception(sb.toString());
            }
        }
    }

[staticlibs]

@blo-kisters-de

Thanks for the Java examples!

This will fail:
and this, too:

You need to take into account timezone shift, that may or may not be applied depending how the date is fetched. Use ResultSet#getTimestamp(idx, cal) with a Calendar argument to have control over the timezone used (instead of a system-wide time zone).

In this snippet you can see that a written Timestamp with DuckDBAppender can be different after read.

I suggest using DuckDBAppender#appendEpochMicros(long) to insert and ResultSet#getTimestamp(idx, cal) to fetch.