Working with Date, Time and Timestamp in JDBC

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)`:

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| ClickHouse Data Type of `column` Value of `class` Conversion|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `Date` or `Date32` `java.time.LocalDate` DB value (number of days) converted to `LocalDate`.| `Date` or `Date32` `java.sql.Date` DB value (number of days) converted to `LocalDate` and then to `java.sql.Date` using local timezone midnight as the time part. If a calendar is used, its timezone will be used instead of the local one. Example: DB value `1970-01-10` → `LocalDate` is `1970-01-10`.| `Time` or `Time64` `java.time.LocalTime` DB value converted to `LocalDateTime` and then to `LocalTime`. This works only for time within a day.| `Time` or `Time64` `java.time.LocalDateTime` DB value converted to `LocalDateTime`.| `Time` or `Time64` `java.sql.Time` DB value converted to `LocalDateTime` and then to `java.sql.Time` using the default calendar. This works only for time within a day.| `Time` or `Time64` `java.time.Duration` DB value converted to `LocalDateTime` and then to `Duration`.| `DateTime` or `DateTime64` `java.time.LocalDateTime` DB value converted to `ZonedDateTime`, then to `LocalDateTime`.| `DateTime` or `DateTime64` `java.time.ZonedDateTime` DB value converted to `ZonedDateTime`.| `DateTime` or `DateTime64` `java.sql.Timestamp` DB value converted to `ZonedDateTime`, then to `java.sql.Timestamp` using the default timezone. | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |

### Using Calendar-based methods[​](#using-calendar-based-methods "Direct link to Using Calendar-based methods")

Use `ResultSet#getTime(column, calendar)` and `ResultSet#getDate(column, calendar)` if values were stored using `PreparedStatement#setTime(param, value, calendar)` and `PreparedStatement#setDate(param, value, calendar)` accordingly.

- [Timezones](#timezones)
  - [How ClickHouse converts DateTime strings](#clickhouse-datetime-string-conversion)- [Writing timestamps across timezones](#writing-timestamps-across-timezones)- [Java and JDBC timestamp APIs](#java-and-jdbc-timestamp-apis)- [Date](#date)
    - [Using `java.time.LocalDate`](#using-localdate)- [Using `java.sql.Date`](#using-java-sql-date)- [Calendar-based reinterpretation](#calendar-based-reinterpretation)- [Time](#time)
      - [ClickHouse Time types](#clickhouse-time-types)- [Java type mapping](#java-type-mapping)- [Using `java.sql.Time`](#using-java-sql-time)- [The `toTime` function](#totime-function)- [Timestamp](#timestamp)
        - [ClickHouse Timestamp types](#clickhouse-timestamp-types)- [String representation and timezone behavior](#string-representation-and-timezone-behavior)- [How the JDBC driver handles timestamps](#how-jdbc-driver-handles-timestamps)- [Common pitfall with `toDateTime64`](#common-pitfall-todatetime64)- [Conversion tables](#conversion-tables)
          - [Writing values with `PreparedStatement#setObject`](#writing-values-setobject)- [Reading values with `ResultSet#getObject`](#reading-values-getobject)- [Using Calendar-based methods](#using-calendar-based-methods)

Was this page helpful?