Working with Date, Time and Timestamp in JDBC

The main support for time types is implemented using `LocalTime` (when the value is within a day) and `Duration` to use the full range of values. `LocalDateTime` can be used for reading only.

### Using `java.sql.Time`[​](#using-java-sql-time "Direct link to using-java-sql-time")

Using `java.sql.Time` is limited to the `LocalTime` range. Internally, `java.sql.Time` is converted to a string literal. The value may be changed by using a Calendar parameter with `PreparedStatement#setTime()`.

### The `toTime` function[​](#totime-function "Direct link to totime-function")

Note

- `toTime` always requires `Date`, `DateTime`, or another similar type. It does not accept strings. Related issue: <https://github.com/ClickHouse/ClickHouse/issues/89896>
- It is aliased to [`toTimeWithFixedDate`](/docs/sql-reference/functions/date-time-functions#toTimeWithFixedDate).
- There is a timezone-related issue: <https://github.com/ClickHouse/ClickHouse/pull/90310>

## Timestamp[​](#timestamp "Direct link to Timestamp")

A timestamp is a specific point in time. For example, a Unix timestamp represents any point in time as a number of seconds relative to `1970-01-01 00:00:00` `UTC` (a negative number of seconds represents a timestamp before Unix time, and a positive number represents one after). This representation is easy to calculate and handle if the observer is in the `UTC` timezone or uses it over their local one.

### ClickHouse Timestamp types[​](#clickhouse-timestamp-types "Direct link to ClickHouse Timestamp types")

There are `DateTime` (32-bit integer, resolution is always seconds) and `DateTime64` (64-bit integer, resolution depends on definition) timestamp types in ClickHouse. Values are always stored as UTC timestamps. This means that when represented as numbers, no timezone conversion is applied.

### String representation and timezone behavior[​](#string-representation-and-timezone-behavior "Direct link to String representation and timezone behavior")

String representation has complexities:

- If no timezone is specified in the column definition and a string is passed on write, it will be converted from the server timezone to a UTC timestamp number. When a value is read from such a column, it will be converted from a UTC timestamp to a literal timestamp using the server or session timezone (a similar approach is applied to timestamp literals in expressions where the timezone is not defined explicitly).
- If a timezone is specified in the column definition, then only that timezone is used in all string conversions. This contradicts the logic when no timezone is specified, so it requires a good understanding of how data is written for each column in the query.
- If a date is passed as a string in a format that includes a timezone, then a conversion function is needed. Usually [`parseDateTimeBestEffort`](/docs/sql-reference/functions/type-conversion-functions#parseDateTimeBestEffort) is used.

### How the JDBC driver handles timestamps[​](#how-jdbc-driver-handles-timestamps "Direct link to How the JDBC driver handles timestamps")

In the JDBC driver, we convert timestamps to a numeric representation:

```
"fromUnixTimestamp64Nano(" + epochSeconds * 1_000_000_000L + nanos + ")"
```

This representation solves most conversion issues with timestamp values because it sends data to the server in a unified format. However, this approach requires a small adjustment in SQL statements, but it provides the simplest and most straightforward way to write timestamps to any column.

`DateTime` and `DateTime64` are read and stored on the client as `java.time.ZonedDateTime`, which helps convert such values to any other timezone (timezone information is preserved).

### Common pitfall with `toDateTime64`[​](#common-pitfall-todatetime64 "Direct link to common-pitfall-todatetime64")

The following code example looks correct but fails on the assertion:

```
String sql = "SELECT toDateTime64(?, 3)";
try (PreparedStatement stmt = conn.prepareStatement(sql)) {
    LocalDateTime localTs = LocalDateTime.parse("2021-01-01T01:34:56");
    stmt.setObject(1, localTs);
    try (ResultSet rs = stmt.executeQuery()) {
        rs.next();
        assertEquals(rs.getObject(1, LocalDateTime.class), localTs);
    }
}
```

This happens because `toDateTime64` uses the server timezone and doesn’t know about the source timezone.

## Conversion tables[​](#conversion-tables "Direct link to Conversion tables")

If a conversion pair is not mentioned in the tables below, then the conversion is not supported. For example, `Date` columns cannot be read as `java.sql.Timestamp` because there is no time part.
Driver doesn't convert integer values to any of date/time values. Calling `pstmt.setLong("timestamp", 1772132359L)` will result `1772132359` written as number to a server what will be treated as
UTC Unix timestamp in seconds.

### Writing values with `PreparedStatement#setObject`[​](#writing-values-setobject "Direct link to writing-values-setobject")

The following table shows how values are converted when set with `PreparedStatement#setObject(column, value)`:

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Class of `value` Conversion|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `java.time.LocalDate` Formatted as `YYYY-MM-DD`.| `java.sql.Date` Converted with the default calendar and formatted as `LocalDate` (`YYYY-MM-DD`).| `java.time.LocalTime` Formatted as `HH:mm:ss`.| `java.time.Duration` Formatted as `HHH:mm:ss`. Value can be negative.| `java.sql.Time` Converted with the default calendar and formatted as `LocalTime` (`HH:mm`).| `java.time.LocalDateTime` Converted to Unix timestamp in nanoseconds and wrapped with `fromUnixTimestamp64Nano`.| `java.time.ZonedDateTime` Converted to Unix timestamp in nanoseconds and wrapped with `fromUnixTimestamp64Nano`.| `java.sql.Timestamp` Converted to Unix timestamp in nanoseconds and wrapped with `fromUnixTimestamp64Nano`. | | | | | | | | | | | | | | | | | |

Note

The type of the column should be considered unknown. It is up to the application to decide what to pass to the prepared statement.

### Reading values with `ResultSet#getObject`[​](#reading-values-getobject "Direct link to reading-values-getobject")

The following table shows how values are converted when read with `ResultSet#getObject(column, class)`: