SET datestyle = 'ISO'; -- Create servers for each engine. CREATE SERVER tz_bin_svr FOREIGN DATA WRAPPER clickhouse_fdw OPTIONS(driver 'binary'); CREATE USER MAPPING FOR CURRENT_USER SERVER tz_bin_svr; CREATE SERVER tz_http_svr FOREIGN DATA WRAPPER clickhouse_fdw OPTIONS(driver 'http'); CREATE USER MAPPING FOR CURRENT_USER SERVER tz_http_svr; CREATE SERVER tz_admin FOREIGN DATA WRAPPER clickhouse_fdw; CREATE USER MAPPING FOR CURRENT_USER SERVER tz_admin; CALL clickhouse_perform('tz_admin', 'DROP DATABASE IF EXISTS tz_test'); CALL clickhouse_perform('tz_admin', 'CREATE DATABASE tz_test'); CALL clickhouse_perform('tz_admin', $$ CREATE TABLE tz_test.ts ( id Int, server_ts DateTime, utc_ts DateTime('UTC'), nyc_ts DateTime('America/New_York'), lax_ts DateTime('America/Los_Angeles') ) ENGINE = MergeTree ORDER BY id $$); -- Insert some records, all times set to 10:00:00 UTC. CALL clickhouse_perform('tz_admin', $$ INSERT INTO tz_test.ts SELECT number, addMonths(toDateTime('2020-01-01 10:00:00', 'UTC'), number * 3), addMonths(toDateTime('2020-01-01 10:00:00', 'UTC'), number * 3), addMonths(toDateTime('2020-01-01 10:00:00', 'UTC'), number * 3), addMonths(toDateTime('2020-01-01 10:00:00', 'UTC'), number * 3) FROM numbers(0, 4) $$); -- Read as text under UTC, so every column shows the instant that was stored. SET session timezone = 'UTC'; SELECT * FROM clickhouse_query('tz_admin', $$ SELECT * FROM tz_test.ts ORDER BY id $$) AS t(id text, server_ts text, utc_ts text, nyc_ts text, lax_ts text); id | server_ts | utc_ts | nyc_ts | lax_ts ----+------------------------+------------------------+------------------------+------------------------ 0 | 2020-01-01 10:00:00+00 | 2020-01-01 10:00:00+00 | 2020-01-01 10:00:00+00 | 2020-01-01 10:00:00+00 1 | 2020-04-01 10:00:00+00 | 2020-04-01 10:00:00+00 | 2020-04-01 10:00:00+00 | 2020-04-01 10:00:00+00 2 | 2020-07-01 10:00:00+00 | 2020-07-01 10:00:00+00 | 2020-07-01 10:00:00+00 | 2020-07-01 10:00:00+00 3 | 2020-10-01 10:00:00+00 | 2020-10-01 10:00:00+00 | 2020-10-01 10:00:00+00 | 2020-10-01 10:00:00+00 (4 rows) CREATE SCHEMA tz_bin; IMPORT FOREIGN SCHEMA tz_test FROM SERVER tz_bin_svr INTO tz_bin; \d tz_bin.ts Foreign table "tz_bin.ts" Column | Type | Collation | Nullable | Default | FDW options -----------+--------------------------+-----------+----------+---------+------------- id | integer | | not null | | server_ts | timestamp with time zone | | not null | | utc_ts | timestamp with time zone | | not null | | nyc_ts | timestamp with time zone | | not null | | lax_ts | timestamp with time zone | | not null | | Server: tz_bin_svr FDW options: (database 'tz_test', table_name 'ts', engine 'MergeTree') CREATE SCHEMA tz_http; IMPORT FOREIGN SCHEMA tz_test FROM SERVER tz_http_svr INTO tz_http; \d tz_http.ts Foreign table "tz_http.ts" Column | Type | Collation | Nullable | Default | FDW options -----------+--------------------------+-----------+----------+---------+------------- id | integer | | not null | | server_ts | timestamp with time zone | | not null | | utc_ts | timestamp with time zone | | not null | | nyc_ts | timestamp with time zone | | not null | | lax_ts | timestamp with time zone | | not null | | Server: tz_http_svr FDW options: (database 'tz_test', table_name 'ts', engine 'MergeTree') SET session timezone = 'UTC'; SELECT * FROM tz_bin.ts ORDER BY id; id | server_ts | utc_ts | nyc_ts | lax_ts ----+------------------------+------------------------+------------------------+------------------------ 0 | 2020-01-01 10:00:00+00 | 2020-01-01 10:00:00+00 | 2020-01-01 10:00:00+00 | 2020-01-01 10:00:00+00 1 | 2020-04-01 10:00:00+00 | 2020-04-01 10:00:00+00 | 2020-04-01 10:00:00+00 | 2020-04-01 10:00:00+00 2 | 2020-07-01 10:00:00+00 | 2020-07-01 10:00:00+00 | 2020-07-01 10:00:00+00 | 2020-07-01 10:00:00+00 3 | 2020-10-01 10:00:00+00 | 2020-10-01 10:00:00+00 | 2020-10-01 10:00:00+00 | 2020-10-01 10:00:00+00 (4 rows) SELECT * FROM tz_http.ts ORDER BY id; id | server_ts | utc_ts | nyc_ts | lax_ts ----+------------------------+------------------------+------------------------+------------------------ 0 | 2020-01-01 10:00:00+00 | 2020-01-01 10:00:00+00 | 2020-01-01 10:00:00+00 | 2020-01-01 10:00:00+00 1 | 2020-04-01 10:00:00+00 | 2020-04-01 10:00:00+00 | 2020-04-01 10:00:00+00 | 2020-04-01 10:00:00+00 2 | 2020-07-01 10:00:00+00 | 2020-07-01 10:00:00+00 | 2020-07-01 10:00:00+00 | 2020-07-01 10:00:00+00 3 | 2020-10-01 10:00:00+00 | 2020-10-01 10:00:00+00 | 2020-10-01 10:00:00+00 | 2020-10-01 10:00:00+00 (4 rows) SET session timezone = 'America/New_York'; SELECT * FROM tz_bin.ts ORDER BY id; id | server_ts | utc_ts | nyc_ts | lax_ts ----+------------------------+------------------------+------------------------+------------------------ 0 | 2020-01-01 05:00:00-05 | 2020-01-01 05:00:00-05 | 2020-01-01 05:00:00-05 | 2020-01-01 05:00:00-05 1 | 2020-04-01 06:00:00-04 | 2020-04-01 06:00:00-04 | 2020-04-01 06:00:00-04 | 2020-04-01 06:00:00-04 2 | 2020-07-01 06:00:00-04 | 2020-07-01 06:00:00-04 | 2020-07-01 06:00:00-04 | 2020-07-01 06:00:00-04 3 | 2020-10-01 06:00:00-04 | 2020-10-01 06:00:00-04 | 2020-10-01 06:00:00-04 | 2020-10-01 06:00:00-04 (4 rows) SELECT * FROM tz_http.ts ORDER BY id; id | server_ts | utc_ts | nyc_ts | lax_ts ----+------------------------+------------------------+------------------------+------------------------ 0 | 2020-01-01 05:00:00-05 | 2020-01-01 05:00:00-05 | 2020-01-01 05:00:00-05 | 2020-01-01 05:00:00-05 1 | 2020-04-01 06:00:00-04 | 2020-04-01 06:00:00-04 | 2020-04-01 06:00:00-04 | 2020-04-01 06:00:00-04 2 | 2020-07-01 06:00:00-04 | 2020-07-01 06:00:00-04 | 2020-07-01 06:00:00-04 | 2020-07-01 06:00:00-04 3 | 2020-10-01 06:00:00-04 | 2020-10-01 06:00:00-04 | 2020-10-01 06:00:00-04 | 2020-10-01 06:00:00-04 (4 rows) SET session timezone = 'America/Los_Angeles'; SELECT * FROM tz_bin.ts ORDER BY id; id | server_ts | utc_ts | nyc_ts | lax_ts ----+------------------------+------------------------+------------------------+------------------------ 0 | 2020-01-01 02:00:00-08 | 2020-01-01 02:00:00-08 | 2020-01-01 02:00:00-08 | 2020-01-01 02:00:00-08 1 | 2020-04-01 03:00:00-07 | 2020-04-01 03:00:00-07 | 2020-04-01 03:00:00-07 | 2020-04-01 03:00:00-07 2 | 2020-07-01 03:00:00-07 | 2020-07-01 03:00:00-07 | 2020-07-01 03:00:00-07 | 2020-07-01 03:00:00-07 3 | 2020-10-01 03:00:00-07 | 2020-10-01 03:00:00-07 | 2020-10-01 03:00:00-07 | 2020-10-01 03:00:00-07 (4 rows) SELECT * FROM tz_http.ts ORDER BY id; id | server_ts | utc_ts | nyc_ts | lax_ts ----+------------------------+------------------------+------------------------+------------------------ 0 | 2020-01-01 02:00:00-08 | 2020-01-01 02:00:00-08 | 2020-01-01 02:00:00-08 | 2020-01-01 02:00:00-08 1 | 2020-04-01 03:00:00-07 | 2020-04-01 03:00:00-07 | 2020-04-01 03:00:00-07 | 2020-04-01 03:00:00-07 2 | 2020-07-01 03:00:00-07 | 2020-07-01 03:00:00-07 | 2020-07-01 03:00:00-07 | 2020-07-01 03:00:00-07 3 | 2020-10-01 03:00:00-07 | 2020-10-01 03:00:00-07 | 2020-10-01 03:00:00-07 | 2020-10-01 03:00:00-07 (4 rows) -- With parameters. Execute 6 times to get parameter passing to kick in. SET session timezone = 'UTC'; PREPARE prep_bin(int) AS SELECT * FROM tz_bin.ts WHERE id > $1; EXECUTE prep_bin(1); id | server_ts | utc_ts | nyc_ts | lax_ts ----+------------------------+------------------------+------------------------+------------------------ 2 | 2020-07-01 10:00:00+00 | 2020-07-01 10:00:00+00 | 2020-07-01 10:00:00+00 | 2020-07-01 10:00:00+00 3 | 2020-10-01 10:00:00+00 | 2020-10-01 10:00:00+00 | 2020-10-01 10:00:00+00 | 2020-10-01 10:00:00+00 (2 rows) EXECUTE prep_bin(1); id | server_ts | utc_ts | nyc_ts | lax_ts ----+------------------------+------------------------+------------------------+------------------------ 2 | 2020-07-01 10:00:00+00 | 2020-07-01 10:00:00+00 | 2020-07-01 10:00:00+00 | 2020-07-01 10:00:00+00 3 | 2020-10-01 10:00:00+00 | 2020-10-01 10:00:00+00 | 2020-10-01 10:00:00+00 | 2020-10-01 10:00:00+00 (2 rows) EXECUTE prep_bin(1); id | server_ts | utc_ts | nyc_ts | lax_ts ----+------------------------+------------------------+------------------------+------------------------ 2 | 2020-07-01 10:00:00+00 | 2020-07-01 10:00:00+00 | 2020-07-01 10:00:00+00 | 2020-07-01 10:00:00+00 3 | 2020-10-01 10:00:00+00 | 2020-10-01 10:00:00+00 | 2020-10-01 10:00:00+00 | 2020-10-01 10:00:00+00 (2 rows) EXECUTE prep_bin(1); id | server_ts | utc_ts | nyc_ts | lax_ts ----+------------------------+------------------------+------------------------+------------------------ 2 | 2020-07-01 10:00:00+00 | 2020-07-01 10:00:00+00 | 2020-07-01 10:00:00+00 | 2020-07-01 10:00:00+00 3 | 2020-10-01 10:00:00+00 | 2020-10-01 10:00:00+00 | 2020-10-01 10:00:00+00 | 2020-10-01 10:00:00+00 (2 rows) EXECUTE prep_bin(1); id | server_ts | utc_ts | nyc_ts | lax_ts ----+------------------------+------------------------+------------------------+------------------------ 2 | 2020-07-01 10:00:00+00 | 2020-07-01 10:00:00+00 | 2020-07-01 10:00:00+00 | 2020-07-01 10:00:00+00 3 | 2020-10-01 10:00:00+00 | 2020-10-01 10:00:00+00 | 2020-10-01 10:00:00+00 | 2020-10-01 10:00:00+00 (2 rows) EXECUTE prep_bin(1); id | server_ts | utc_ts | nyc_ts | lax_ts ----+------------------------+------------------------+------------------------+------------------------ 2 | 2020-07-01 10:00:00+00 | 2020-07-01 10:00:00+00 | 2020-07-01 10:00:00+00 | 2020-07-01 10:00:00+00 3 | 2020-10-01 10:00:00+00 | 2020-10-01 10:00:00+00 | 2020-10-01 10:00:00+00 | 2020-10-01 10:00:00+00 (2 rows) DEALLOCATE prep_bin; PREPARE prep_http(int) AS SELECT * FROM tz_http.ts WHERE id > $1; EXECUTE prep_http(1); id | server_ts | utc_ts | nyc_ts | lax_ts ----+------------------------+------------------------+------------------------+------------------------ 2 | 2020-07-01 10:00:00+00 | 2020-07-01 10:00:00+00 | 2020-07-01 10:00:00+00 | 2020-07-01 10:00:00+00 3 | 2020-10-01 10:00:00+00 | 2020-10-01 10:00:00+00 | 2020-10-01 10:00:00+00 | 2020-10-01 10:00:00+00 (2 rows) EXECUTE prep_http(1); id | server_ts | utc_ts | nyc_ts | lax_ts ----+------------------------+------------------------+------------------------+------------------------ 2 | 2020-07-01 10:00:00+00 | 2020-07-01 10:00:00+00 | 2020-07-01 10:00:00+00 | 2020-07-01 10:00:00+00 3 | 2020-10-01 10:00:00+00 | 2020-10-01 10:00:00+00 | 2020-10-01 10:00:00+00 | 2020-10-01 10:00:00+00 (2 rows) EXECUTE prep_http(1); id | server_ts | utc_ts | nyc_ts | lax_ts ----+------------------------+------------------------+------------------------+------------------------ 2 | 2020-07-01 10:00:00+00 | 2020-07-01 10:00:00+00 | 2020-07-01 10:00:00+00 | 2020-07-01 10:00:00+00 3 | 2020-10-01 10:00:00+00 | 2020-10-01 10:00:00+00 | 2020-10-01 10:00:00+00 | 2020-10-01 10:00:00+00 (2 rows) EXECUTE prep_http(1); id | server_ts | utc_ts | nyc_ts | lax_ts ----+------------------------+------------------------+------------------------+------------------------ 2 | 2020-07-01 10:00:00+00 | 2020-07-01 10:00:00+00 | 2020-07-01 10:00:00+00 | 2020-07-01 10:00:00+00 3 | 2020-10-01 10:00:00+00 | 2020-10-01 10:00:00+00 | 2020-10-01 10:00:00+00 | 2020-10-01 10:00:00+00 (2 rows) EXECUTE prep_http(1); id | server_ts | utc_ts | nyc_ts | lax_ts ----+------------------------+------------------------+------------------------+------------------------ 2 | 2020-07-01 10:00:00+00 | 2020-07-01 10:00:00+00 | 2020-07-01 10:00:00+00 | 2020-07-01 10:00:00+00 3 | 2020-10-01 10:00:00+00 | 2020-10-01 10:00:00+00 | 2020-10-01 10:00:00+00 | 2020-10-01 10:00:00+00 (2 rows) -- Diff results < 28.8, due to https://github.com/ClickHouse/ClickHouse/issues/85847 EXECUTE prep_http(1); id | server_ts | utc_ts | nyc_ts | lax_ts ----+------------------------+------------------------+------------------------+------------------------ 2 | 2020-07-01 10:00:00+00 | 2020-07-01 10:00:00+00 | 2020-07-01 10:00:00+00 | 2020-07-01 10:00:00+00 3 | 2020-10-01 10:00:00+00 | 2020-10-01 10:00:00+00 | 2020-10-01 10:00:00+00 | 2020-10-01 10:00:00+00 (2 rows) DEALLOCATE prep_http; -- Extract and truncate timestamptz in PostgreSQL TimeZone, not column zone. SET session timezone = 'America/New_York'; EXPLAIN (VERBOSE, COSTS OFF) SELECT date_trunc('day', lax_ts) AS d, date_part('hour', utc_ts) AS h, to_char(nyc_ts, 'HH24') AS c, to_char(utc_ts::date, 'YYYY-MM') AS m FROM tz_bin.ts GROUP BY d, h, c, m ORDER BY d; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan Output: (date_trunc('day'::text, lax_ts)), (date_part('hour'::text, utc_ts)), (to_char(nyc_ts, 'HH24'::text)), (to_char(((utc_ts)::date)::timestamp with time zone, 'YYYY-MM'::text)) Relations: Aggregate on (ts) Remote SQL: SELECT toStartOfDay(lax_ts, 'America/New_York'), toHour(utc_ts, 'America/New_York'), formatDateTime(nyc_ts, '%H', 'America/New_York'), formatDateTime(toDate32(utc_ts, 'America/New_York'), '%Y-%m') FROM tz_test.ts GROUP BY (toStartOfDay(lax_ts, 'America/New_York')), (toHour(utc_ts, 'America/New_York')), (formatDateTime(nyc_ts, '%H', 'America/New_York')), (formatDateTime(toDate32(utc_ts, 'America/New_York'), '%Y-%m')) ORDER BY toStartOfDay(lax_ts, 'America/New_York') ASC NULLS LAST (4 rows) SELECT date_trunc('day', lax_ts) AS d, date_part('hour', utc_ts) AS h, to_char(nyc_ts, 'HH24') AS c, to_char(utc_ts::date, 'YYYY-MM') AS m FROM tz_bin.ts GROUP BY d, h, c, m ORDER BY d; d | h | c | m ------------------------+---+----+--------- 2020-01-01 00:00:00-05 | 5 | 05 | 2020-01 2020-04-01 00:00:00-04 | 6 | 06 | 2020-04 2020-07-01 00:00:00-04 | 6 | 06 | 2020-07 2020-10-01 00:00:00-04 | 6 | 06 | 2020-10 (4 rows) SELECT date_trunc('day', lax_ts) AS d, date_part('hour', utc_ts) AS h, to_char(nyc_ts, 'HH24') AS c, to_char(utc_ts::date, 'YYYY-MM') AS m FROM tz_http.ts GROUP BY d, h, c, m ORDER BY d; d | h | c | m ------------------------+---+----+--------- 2020-01-01 00:00:00-05 | 5 | 05 | 2020-01 2020-04-01 00:00:00-04 | 6 | 06 | 2020-04 2020-07-01 00:00:00-04 | 6 | 06 | 2020-07 2020-10-01 00:00:00-04 | 6 | 06 | 2020-10 (4 rows) EXPLAIN (VERBOSE, COSTS OFF) SELECT id FROM tz_bin.ts WHERE extract(hour FROM lax_ts) = 5; QUERY PLAN ------------------------------------------------------------------------------------------ Foreign Scan on tz_bin.ts Output: id Remote SQL: SELECT id FROM tz_test.ts WHERE ((toHour(lax_ts, 'America/New_York') = 5)) (3 rows) SELECT id FROM tz_bin.ts WHERE extract(hour FROM lax_ts) = 5; id ---- 0 (1 row) SELECT id FROM tz_http.ts WHERE extract(hour FROM lax_ts) = 5; id ---- 0 (1 row) -- Wall clock 02:30 falls in Los Angeles DST gap, so must not pass through it. CALL clickhouse_perform('tz_admin', $$ CREATE TABLE tz_test.gap (ts DateTime('UTC')) ENGINE = MergeTree ORDER BY ts $$); CALL clickhouse_perform('tz_admin', $$ INSERT INTO tz_test.gap VALUES ('2020-03-08 02:30:00') $$); CREATE FOREIGN TABLE tz_bin.gap (ts timestamptz) SERVER tz_bin_svr OPTIONS (database 'tz_test'); SET session timezone = 'America/Los_Angeles'; EXPLAIN (VERBOSE, COSTS OFF) SELECT date_part('hour', ts AT TIME ZONE 'UTC') AS h, date_part('epoch', ts AT TIME ZONE 'UTC') AS e, to_char(ts AT TIME ZONE 'UTC', 'HH24:MI') AS c, (ts AT TIME ZONE 'UTC')::date AS d, (ts AT TIME ZONE 'UTC') AT TIME ZONE 'UTC' AS r FROM tz_bin.gap GROUP BY h, e, c, d, r; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan Output: (date_part('hour'::text, (ts AT TIME ZONE 'UTC'::text))), (date_part('epoch'::text, (ts AT TIME ZONE 'UTC'::text))), (to_char((ts AT TIME ZONE 'UTC'::text), 'HH24:MI'::text)), (((ts AT TIME ZONE 'UTC'::text))::date), (((ts AT TIME ZONE 'UTC'::text) AT TIME ZONE 'UTC'::text)) Relations: Aggregate on (gap) Remote SQL: SELECT toHour(ts, 'UTC'), toUnixTimestamp(toDateTime64(toString(ts, 'UTC'), 6, 'UTC')), formatDateTime(ts, '%H:%i', 'UTC'), toDate32(ts, 'UTC'), toDateTime64(toString(ts, 'UTC'), 6, 'UTC') FROM tz_test.gap GROUP BY (toHour(ts, 'UTC')), (toUnixTimestamp(toDateTime64(toString(ts, 'UTC'), 6, 'UTC'))), (formatDateTime(ts, '%H:%i', 'UTC')), (toDate32(ts, 'UTC')), (toDateTime64(toString(ts, 'UTC'), 6, 'UTC')) (4 rows) SELECT date_part('hour', ts AT TIME ZONE 'UTC') AS h, date_part('epoch', ts AT TIME ZONE 'UTC') AS e, to_char(ts AT TIME ZONE 'UTC', 'HH24:MI') AS c, (ts AT TIME ZONE 'UTC')::date AS d, (ts AT TIME ZONE 'UTC') AT TIME ZONE 'UTC' AS r FROM tz_bin.gap GROUP BY h, e, c, d, r; h | e | c | d | r ---+------------+-------+------------+------------------------ 2 | 1583634600 | 02:30 | 2020-03-08 | 2020-03-07 18:30:00-08 (1 row) -- Clean up. DROP USER MAPPING FOR CURRENT_USER SERVER tz_bin_svr; DROP SERVER tz_bin_svr CASCADE; NOTICE: drop cascades to 2 other objects DETAIL: drop cascades to foreign table tz_bin.ts drop cascades to foreign table tz_bin.gap DROP USER MAPPING FOR CURRENT_USER SERVER tz_http_svr; DROP SERVER tz_http_svr CASCADE; NOTICE: drop cascades to foreign table tz_http.ts