get time_stamp default timezone issue.
Ted-Jiang opened this issue · 3 comments
Describe the bug
Steps to reproduce
system.query_log table column event_time
find logic in:
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
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 TABLEstatements 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 if necessary
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!
@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
@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')
Please see also this tests https://github.com/ClickHouse/clickhouse-java/blob/main/clickhouse-jdbc/src/test/java/com/clickhouse/jdbc/ClickHouseResultSetTest.java#L435C1-L436C1