get time_stamp default timezone issue.

Ted-Jiang commented 1 month ago

Describe the bug

Steps to reproduce

system.query_log table column event_time find logic in: https://github.com/ClickHouse/clickhouse-java/blob/2562606b920eb64578be717c52879917b6212848/clickhouse-jdbc/src/main/java/com/clickhouse/jdbc/ClickHouseResultSet.java#L643-L645

Using getTimestamp(event_time) w ithout Calendar the result is 7 hours ahead the result in local table .

Server: utc , jdbc:MST

so i try to use getTimestamp(event_time, 'UTC') got the correct result.

the i find my setting use_server_time_zone true go into https://github.com/ClickHouse/clickhouse-java/blob/8b4e71b8e1da150844de033347787d6379dafe39/clickhouse-jdbc/src/main/java/com/clickhouse/jdbc/internal/ClickHouseConnectionImpl.java#L449-L452

then go to TimeZone.getDefaultRef() which load client timezone,

IMO, after set use_server_time_zone = true the defaultCalendar should keep with server side. Is there on purpose for something? 🤔 I am newbie to CK, if miss something plz feel free to correct me

Expected behaviour

Code example

Error log

Configuration

Environment

Client version: 0.4~0.6
Language version: java
OS: linux

ClickHouse server

ClickHouse Server version: 21
ClickHouse Server non-default settings, if any:
CREATE TABLE statements for tables involved:
```
SHOW CREATE TABLE system.query_log
```

Query id: fc93d3aa-0aca-4bd3-a19b-d2397c4e9b9a

┌─statement──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐ │ CREATE TABLE system.query_log ( type Enum8('QueryStart' = 1, 'QueryFinish' = 2, 'ExceptionBeforeStart' = 3, 'ExceptionWhileProcessing' = 4), event_date Date, event_time DateTime, event_time_microseconds DateTime64(6), query_start_time DateTime, query_start_time_microseconds DateTime64(6), query_duration_ms UInt64, read_rows UInt64, read_bytes UInt64, written_rows UInt64, written_bytes UInt64, result_rows UInt64, result_bytes UInt64, memory_usage UInt64, current_database String, query String, normalized_query_hash UInt64, query_kind LowCardinality(String), databases Array(LowCardinality(String)), tables Array(LowCardinality(String)), columns Array(LowCardinality(String)), projections Array(LowCardinality(String)), exception_code Int32, exception String, stack_trace String, is_initial_query UInt8, user String, query_id String, address IPv6, port UInt16, initial_user String, initial_query_id String, initial_address IPv6, initial_port UInt16, initial_query_start_time DateTime, initial_query_start_time_microseconds DateTime64(6), interface UInt8, os_user String, client_hostname String, client_name String, client_revision UInt32, client_version_major UInt32, client_version_minor UInt32, client_version_patch UInt32, http_method UInt8, http_user_agent String, http_referer String, forwarded_for String, quota_key String, revision UInt32, log_comment String, thread_ids Array(UInt64), ProfileEvents Map(String, UInt64), Settings Map(String, String), used_aggregate_functions Array(String), used_aggregate_function_combinators Array(String), used_database_engines Array(String), used_data_type_families Array(String), used_dictionaries Array(String), used_formats Array(String), used_functions Array(String), used_storages Array(String), used_table_functions Array(String), ProfileEvents.Names Array(String) ALIAS mapKeys(ProfileEvents), ProfileEvents.Values Array(UInt64) ALIAS mapValues(ProfileEvents), Settings.Names Array(String) ALIAS mapKeys(Settings), Settings.Values Array(String) ALIAS mapValues(Settings) ) ENGINE = MergeTree PARTITION BY toYYYYMMDD(event_time) ORDER BY event_time TTL event_time + toIntervalDay(3) SETTINGS index_granularity = 8192 │ └────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

1 rows in set. Elapsed: 0.006 sec


* Sample data for all these tables, use [clickhouse-obfuscator](https://github.com/ClickHouse/ClickHouse/blob/master/programs/obfuscator/Obfuscator.cpp#L42-L80) if necessary

chernser commented 1 month ago

Good day, @Ted-Jiang ! Calendar is in local timezone because date/time values are converted to a selected timezone (server in your case) Do you have an example showing what is working not as expected?

Thanks!

Ted-Jiang commented 1 month ago

@chernser Thanks for reply ! basically use different timezone call getTimeStamp return different epoch mills

Timestamp eventTime = rs.getTimestamp("event_time", Calendar.getInstance());
Timestamp eventTime2 = rs.getTimestamp("event_time",Calendar.getInstance(TimeZone.getTimeZone("UTC")));
log.info("eventTime={}, eventTimeutc={}", eventTime.getTime(), eventTime2.getTime());

In log file

03:35:49.615 INFO  [pool-3-thread-1] c.e.n.agent.ClickhouseClientWorker - eventTime=1721323698000, eventTimeutc=1721298498000
03:35:49.615 INFO  [pool-3-thread-1] c.e.n.agent.ClickhouseClientWorker - eventTime=1721323699000, eventTimeutc=1721298499000
03:35:49.615 INFO  [pool-3-thread-1] c.e.n.agent.ClickhouseClientWorker - eventTime=1721323699000, eventTimeutc=1721298499000
03:35:49.615 INFO  [pool-3-thread-1] c.e.n.agent.ClickhouseClientWorker - eventTime=1721323700000, eventTimeutc=1721298500000
03:35:49.615 INFO  [pool-3-thread-1] c.e.n.agent.ClickhouseClientWorker - eventTime=1721323700000, eventTimeutc=1721298500000

chernser commented 3 weeks ago

@Ted-Jiang Sorry for the delayed answer. You need to use just rs.getTimestamp() and server timezone will be used.

Currently you overriding the time with calendar timezone. Here is how JDBC spec describes usage for getTimestamp(index, Calendar)

/**
     * Retrieves the value of the designated column in the current row
     * of this {@code ResultSet} object as a {@code java.sql.Timestamp} object
     * in the Java programming language.
     * This method uses the given calendar to construct an appropriate millisecond
     * value for the timestamp if the underlying database does not store
     * timezone information.
     *

CH supports timezone and you would define column as DateTime('UTC')

ClickHouse / clickhouse-java