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 06:00:00+00 | 2020-07-01 03:00:00+00 3 | 2020-10-01 10:00:00+00 | 2020-10-01 10:00:00+00 | 2020-10-01 06:00:00+00 | 2020-10-01 03:00:00+00 (2 rows) DEALLOCATE prep_http; -- Clean up. DROP USER MAPPING FOR CURRENT_USER SERVER tz_bin_svr; DROP SERVER tz_bin_svr CASCADE; NOTICE: drop cascades to foreign table tz_bin.ts 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