Working with Date, Time and Timestamp in JDBC

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/integrations/language-clients/java/date_time_guide.md)

Date, Time and Timestamp require attention because there are several common problems related to them.
The most common problem is how to handle time zones. Another problem is string representation and how to use it.
Besides that, every database and driver has its own specifics and limitations.

This document aims to be a decision-making guide by describing tasks, giving implementation details and explaining problems.

## Timezones[​](#timezones "Direct link to Timezones")

We all know that timezones are hard to handle (daylight saving time, constant offset changes). But this section is about another problem linked to timezones: how they relate to timestamp string representation.

### How ClickHouse converts DateTime strings[​](#clickhouse-datetime-string-conversion "Direct link to How ClickHouse converts DateTime strings")

ClickHouse uses the following rules to convert `DateTime` string values:

- If a column is defined with a timezone (`DateTime64(9, ‘Asia/Tokyo’)`), then the string value will be treated as a timestamp in that timezone. `2026-01-01 13:00:00` will be `2026-01-01 04:00:00` in `UTC` time.
- If a column has no timezone definition, then only the server timezone is used. Important: the `session_timezone` setting has no effect. So if the server timezone is `UTC` and the session timezone is `America/Los_Angeles`, then `2026-01-01 13:00:00` will be written as `UTC` time.
- When a value is read from a column without a timezone definition, the `session_timezone` is used, or if not set, the server timezone. That is why reading timestamps as strings can be affected by `session_timezone`. There is nothing wrong with this, but it should be kept in mind.

### Writing timestamps across timezones[​](#writing-timestamps-across-timezones "Direct link to Writing timestamps across timezones")

Now let’s assume we have an application running in the `us-west` region with local timezone `UTC-8`, and we need to write a local timestamp `2026-01-01 02:00:00` which in `UTC` is `2026-01-01 10:00:00`:

- Writing it as a string requires converting it to the server timezone or column timezone.
- Writing it as a language-native time structure requires the driver to know the target timezone, but:
  - It is not always possible
  - The driver API is not well-designed for this
  - The only way is to describe what transformations will be performed so the application can compensate (or write a Unix timestamp as a number)

### Java and JDBC timestamp APIs[​](#java-and-jdbc-timestamp-apis "Direct link to Java and JDBC timestamp APIs")

Java and JDBC have different ways to set a timestamp:

1. Use the `Timestamp` class, which is really a Unix timestamp.
   1. When used with a `Calendar` object, it makes it possible to reinterpret the `Timestamp` in the calendar’s timezone.
   2. `Timestamp` has an internal calendar that is not very obvious.
2. Use the `LocalDateTime` class, which is easy to convert to any timezone, but there is no method allowing you to pass a target timezone.
3. Use the `ZonedDateTime` class, which helps with timezone conversion when writing to a `DateTime` without a timezone (because we know to use the server timezone).
   1. But writing a `ZonedDateTime` to a column with a defined timezone requires the user to compensate for the driver conversion.
4. Use `Long` to write Unix timestamp milliseconds.
5. Use `String` to do all conversions on the application side (which is not very portable).

Note

Prefer use of `java.time.ZoneId#of(java.lang.String)` when searching for a timezone by ID.
This method will throw an exception if the timezone is not found (`java.util.TimeZone#getTimeZone(java.lang.String)` will silently fall back to `GMT`).

The correct way to get the `Tokyo` timezone is:

`TimeZone.getTimeZone(ZoneId.of("Asia/Tokyo"))`

## Date[​](#date "Direct link to Date")

Dates are timezone-agnostic by nature. There are `Date` and `Date32` types to store dates. Both types use a number of days since Epoch (1970-01-01). `Date` uses only positive numbers of days, so its range ends on `2149-06-06`. `Date32` handles negative numbers of days to cover dates before `1970-01-01`, but its range is smaller (from `1900-01-01` to `2100-01-01`, where 0 is `1970-01-01`). ClickHouse sees `2026-01-01` as `2026-01-01` in any timezone, and there is no timezone parameter for column definitions.

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

In Java, the most suitable class to represent date values is `java.time.LocalDate`. The client uses this class to store the value of `Date` and `Date32` columns (reading `LocalDate.ofEpochDay((long)readUnsignedShortLE())`).

We recommend using `java.time.LocalDate` because it is not affected by timezone transformations and is part of the modern time API.

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

`LocalDate` was introduced in Java 8. Before that, `java.sql.Date` was used to write/read dates. Internally this class is a wrapper around an instant (a time value representing an absolute point in time). Because of this, `toString()` returns a different date depending on what timezone the JVM is in. It requires the driver to carefully construct values and requires the user to be aware of this.

### Calendar-based reinterpretation[​](#calendar-based-reinterpretation "Direct link to Calendar-based reinterpretation")

`java.sql.ResultSet` has a method for getting date values that accepts a `Calendar`, and there is a similar method in `java.sql.PreparedStatement`. This was designed to let the JDBC driver reinterpret a date value in the specified timezone. For example, the DB has value `2026-01-01` but the application wants to see this date as midnight in `Tokyo`. That means the returned `java.sql.Date` object will get a specific instant, and when converted to the local timezone it may be a different date because of the time difference. We can achieve the same with `LocalDate` by using `java.time.LocalDate#atStartOfDay(java.time.ZoneId)`.

The ClickHouse JDBC driver always returns a `java.sql.Date` object that points to the **local** date at midnight. In other words, if the date is `2026-01-01`, we mean `2026-01-01 12:00 AM` in the JVM timezone (the same behavior as PostgreSQL and MariaDB JDBC drivers).

## Time[​](#time "Direct link to Time")

Time values, like Date values, are timezone-agnostic in most cases. ClickHouse does no transformations of time literal values to any timezone — `’6:30’` is the same wherever it is read.

### ClickHouse Time types[​](#clickhouse-time-types "Direct link to ClickHouse Time types")

`Time` and `Time64` were introduced in `25.6`. Before that, the timestamp types `DateTime` and `DateTime64` were used instead (discussed later in this guide). `Time` is stored as a 32-bit integer number of seconds and is in the range `[-999:59:59, 999:59:59]`. `Time64` is encoded as an unsigned Decimal64 and stores different time units depending on precision. Common choices are 3 (milliseconds), 6 (microseconds), and 9 (nanoseconds). The precision value range is `[0, 9]`.

### Java type mapping[​](#java-type-mapping "Direct link to Java type mapping")

The client reads `Time` and `Time64` and stores them as `LocalDateTime`. This is done to support the negative time range (`LocalTime` doesn’t support it). In this case, the date part is the Epoch date `1970-01-01`, so negative values will be before this date.

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