LOAD 'chdb_hook'; /****************************************************************************/ -- Dates and times. CREATE TABLE datetimes ( ts TIMESTAMP NULL, tsn TIMESTAMP(3) NULL, tstz TIMESTAMPTZ NOT NULL, tstzn TIMESTAMPTZ(4) NOT NULL, date DATE NOT NULL, time TIME NOT NULL, timen TIME(3) NOT NULL, ttz TIMETZ NOT NULL, ttzn TIMETZ(2) NOT NULL, ival INTERVAL NOT NULL ); INSERT INTO datetimes VALUES ('2026-07-23 20:43:10', '2026-07-23 20:43:27', '2026-07-23 20:43:50+00', '2026-07-23 13:44:46-07', '2026-07-23', '13:45:15', '13:45:24', '13:45:35.306886-07', '13:45:49.17-07', '1 day'); ; -- Execute round-trip to all supported formats. Parquet, Arrow, ArrowStream, -- ORC, Avro, Protobuf, ProtobufList, MsgPack and BSONEachRow have no column -- type for a Time64, so those declare the time columns String. CREATE TABLE datetimes2 (LIKE datetimes INCLUDING ALL); \set from_table datetimes \set to_table datetimes2 \set output_file datetimes.tmp \i test/utils/round-trip-formats.sql \set ECHO errors t: TabSeparated t: TabSeparatedRaw t: TabSeparatedWithNames t: TabSeparatedWithNamesAndTypes t: TabSeparatedRawWithNames t: TabSeparatedRawWithNamesAndTypes t: CSV t: CSVWithNames t: CSVWithNamesAndTypes t: CustomSeparated t: CustomSeparatedWithNames t: CustomSeparatedWithNamesAndTypes t: Values t: JSON t: JSONColumns t: JSONColumnsWithMetadata t: JSONCompact t: JSONCompactColumns t: JSONEachRow t: JSONStringsEachRow t: JSONCompactEachRow t: JSONCompactStringsEachRow t: JSONCompactEachRowWithNames t: JSONCompactEachRowWithNamesAndTypes t: JSONCompactStringsEachRowWithNames t: JSONCompactStringsEachRowWithNamesAndTypes t: JSONObjectEachRow t: TSKV t: Native t: RowBinary t: RowBinaryWithNames t: RowBinaryWithNamesAndTypes t: Parquet t: Arrow t: ArrowStream t: ORC t: Avro t: Protobuf t: ProtobufList t: MsgPack t: BSONEachRow -- Add timestamps with sub-second precision. Protobuf truncates seconds so -- will fail. INSERT INTO datetimes VALUES ('2026-07-23 20:43:10.836612', '2026-07-23 20:43:27.363', '2026-07-23 20:43:50.944042+00', '2026-07-23 13:44:46.8445-07', '2026-07-23', '13:45:15.416013', '13:45:24.282', '13:45:35.306886-07', '13:45:49.17-07', '1 day'); \i test/utils/round-trip-formats.sql \set ECHO errors t: TabSeparated t: TabSeparatedRaw t: TabSeparatedWithNames t: TabSeparatedWithNamesAndTypes t: TabSeparatedRawWithNames t: TabSeparatedRawWithNamesAndTypes t: CSV t: CSVWithNames t: CSVWithNamesAndTypes t: CustomSeparated t: CustomSeparatedWithNames t: CustomSeparatedWithNamesAndTypes t: Values t: JSON t: JSONColumns t: JSONColumnsWithMetadata t: JSONCompact t: JSONCompactColumns t: JSONEachRow t: JSONStringsEachRow t: JSONCompactEachRow t: JSONCompactStringsEachRow t: JSONCompactEachRowWithNames t: JSONCompactEachRowWithNamesAndTypes t: JSONCompactStringsEachRowWithNames t: JSONCompactStringsEachRowWithNamesAndTypes t: JSONObjectEachRow t: TSKV t: Native t: RowBinary t: RowBinaryWithNames t: RowBinaryWithNamesAndTypes t: Parquet t: Arrow t: ArrowStream t: ORC t: Avro f: Protobuf f: ProtobufList t: MsgPack t: BSONEachRow -- Protobuf chokes on dates prior to 1970-01-01, so avoid them. https://github.com/ClickHouse/ClickHouse/issues/111860 INSERT INTO datetimes VALUES (NULL, NULL, '2299-12-31 23:59:59.999999Z', '1970-01-01 00:00:00Z', '1900-01-01', '00:00:00', '24:00:00', '00:00:00+1559', '24:00:00-1559', '-178000000 years'); \i test/utils/round-trip-formats.sql \set ECHO errors t: TabSeparated t: TabSeparatedRaw t: TabSeparatedWithNames t: TabSeparatedWithNamesAndTypes t: TabSeparatedRawWithNames t: TabSeparatedRawWithNamesAndTypes t: CSV t: CSVWithNames t: CSVWithNamesAndTypes t: CustomSeparated t: CustomSeparatedWithNames t: CustomSeparatedWithNamesAndTypes t: Values t: JSON t: JSONColumns t: JSONColumnsWithMetadata t: JSONCompact t: JSONCompactColumns t: JSONEachRow t: JSONStringsEachRow t: JSONCompactEachRow t: JSONCompactStringsEachRow t: JSONCompactEachRowWithNames t: JSONCompactEachRowWithNamesAndTypes t: JSONCompactStringsEachRowWithNames t: JSONCompactStringsEachRowWithNamesAndTypes t: JSONObjectEachRow t: TSKV t: Native t: RowBinary t: RowBinaryWithNames t: RowBinaryWithNamesAndTypes t: Parquet t: Arrow t: ArrowStream t: ORC t: Avro f: Protobuf f: ProtobufList t: MsgPack t: BSONEachRow /****************************************************************************/ -- Dates and time arrays. CREATE TABLE datetime_arrays ( ts TIMESTAMP[] NOT NULL, tsn TIMESTAMP(3)[] NOT NULL, tstz TIMESTAMPTZ[] NOT NULL, tstzn TIMESTAMPTZ(4)[] NOT NULL, date DATE[] NOT NULL, time TIME[] NOT NULL, timen TIME(3)[] NOT NULL, ttz TIMETZ[] NOT NULL, ttzn TIMETZ(2)[] NOT NULL, ival INTERVAL[] NOT NULL ); INSERT INTO datetime_arrays VALUES ('{2026-07-23 20:43:10.836612}', '{2026-07-23 20:43:27.363}', '{2026-07-23 20:43:50.944042+00}', '{2026-07-23 13:44:46.8445-07}', '{2026-07-23}', '{13:45:15.416013}', '{13:45:24.282}', '{13:45:35.306886-07}', '{13:45:49.17-07}', '{1 day}') , ('{1900-01-01 00:00:00, 2299-12-31 23:59:59.999999}', '{}', '{1900-01-01 00:00:00Z, 2299-12-31 23:59:59.999999Z}', '{NULL}', '{1900-01-01, 2299-12-31}', '{00:00:00, 24:00:00}', '{NULL}', '{00:00:00+1559, 24:00:00-1559}', '{09:23:23Z}', '{-178000000 years, NULL}') ; -- Execute round-trip to all supported formats. ORC reads a year 2299 timestamp -- back out of DateTime64's range and Protobuf overflows converting one, as -- above, and Protobuf has no null in a repeated field either. CREATE TABLE datetime_arrays2 (LIKE datetime_arrays INCLUDING ALL); \set from_table datetime_arrays \set to_table datetime_arrays2 \i test/utils/round-trip-formats.sql \set ECHO errors t: TabSeparated t: TabSeparatedRaw t: TabSeparatedWithNames t: TabSeparatedWithNamesAndTypes t: TabSeparatedRawWithNames t: TabSeparatedRawWithNamesAndTypes t: CSV t: CSVWithNames t: CSVWithNamesAndTypes t: CustomSeparated t: CustomSeparatedWithNames t: CustomSeparatedWithNamesAndTypes t: Values t: JSON t: JSONColumns t: JSONColumnsWithMetadata t: JSONCompact t: JSONCompactColumns t: JSONEachRow t: JSONStringsEachRow t: JSONCompactEachRow t: JSONCompactStringsEachRow t: JSONCompactEachRowWithNames t: JSONCompactEachRowWithNamesAndTypes t: JSONCompactStringsEachRowWithNames t: JSONCompactStringsEachRowWithNamesAndTypes t: JSONObjectEachRow t: TSKV t: Native t: RowBinary t: RowBinaryWithNames t: RowBinaryWithNamesAndTypes t: Parquet t: Arrow t: ArrowStream t: ORC t: Avro psql:test/utils/round-trip-formats.sql:402: ERROR: chdb: error finishing chDB query DETAIL: Code: 407. DB::Exception: Convert overflow. (DECIMAL_OVERFLOW) CONTEXT: query: INSERT INTO FUNCTION file({path:String}, {format:String}, {structure:String}) SETTINGS engine_file_truncate_on_insert=1 psql:test/utils/round-trip-formats.sql:402: STATEMENT: COPY "datetime_arrays" TO 'file:///tmp/datetimes.tmp' (format 'Protobuf'); f: Protobuf psql:test/utils/round-trip-formats.sql:421: ERROR: chdb: error finishing chDB query DETAIL: Code: 407. DB::Exception: Convert overflow. (DECIMAL_OVERFLOW) CONTEXT: query: INSERT INTO FUNCTION file({path:String}, {format:String}, {structure:String}) SETTINGS engine_file_truncate_on_insert=1 psql:test/utils/round-trip-formats.sql:421: STATEMENT: COPY "datetime_arrays" TO 'file:///tmp/datetimes.tmp' (format 'ProtobufList'); f: ProtobufList t: MsgPack t: BSONEachRow /****************************************************************************/ CREATE TABLE intervals ( y INTERVAL NOT NULL, q INTERVAL NOT NULL, mon INTERVAL NOT NULL, w INTERVAL NOT NULL, d INTERVAL NOT NULL, h INTERVAL NOT NULL, mi INTERVAL NOT NULL, s INTERVAL NOT NULL, ms INTERVAL NOT NULL, us INTERVAL NOT NULL, ns INTERVAL NOT NULL ); INSERT INTO intervals VALUES ('6 years', '9 months', '-5 months', '14 days', '-3 days', '4 hours', '-90 minutes', '2 sec', '0.25 sec', '-0.000001 sec', '1.000002 sec'); \set interval_structure 'y IntervalYear, q IntervalQuarter, mon IntervalMonth, w IntervalWeek, d IntervalDay, h IntervalHour, mi IntervalMinute, s IntervalSecond, ms IntervalMillisecond, us IntervalMicrosecond, ns IntervalNanosecond' CREATE TABLE intervals2 (LIKE intervals INCLUDING ALL); COPY intervals TO 'file:///tmp/datetimes.tmp' (format 'TabSeparated', structure :'interval_structure'); COPY intervals2 FROM 'file:///tmp/datetimes.tmp' (format 'TabSeparated', structure :'interval_structure'); SELECT count(*) AS intervals_mismatch FROM (SELECT * FROM intervals EXCEPT ALL SELECT * FROM intervals2) x; intervals_mismatch -------------------- 0 (1 row) -- Months mixed with days, or a value which doesn't fit destination interval type. CREATE TABLE misfits (iv INTERVAL NOT NULL); INSERT INTO misfits VALUES ('1 mon 1 day'); COPY misfits TO 'file:///tmp/datetimes.tmp' (format 'TabSeparated', structure 'iv IntervalMonth'); ERROR: chdb: interval does not fit IntervalMonth TRUNCATE misfits; INSERT INTO misfits VALUES ('1.5 days'); COPY misfits TO 'file:///tmp/datetimes.tmp' (format 'TabSeparated', structure 'iv IntervalDay'); ERROR: chdb: interval does not fit IntervalDay -- Reading same ticks truncates nanoseconds to microseconds in Postgres CREATE TABLE ticks (n INT8 NOT NULL); CREATE TABLE tocks (iv INTERVAL NOT NULL); INSERT INTO ticks VALUES (1500), (-1500); COPY ticks TO 'file:///tmp/datetimes.tmp' (format 'TabSeparated', structure 'n Int64'); COPY tocks FROM 'file:///tmp/datetimes.tmp' (format 'TabSeparated', structure 'n IntervalNanosecond'); SELECT iv FROM tocks ORDER BY iv; iv --------------------- @ 0.000001 secs ago @ 0.000001 secs (2 rows) TRUNCATE ticks; INSERT INTO ticks VALUES (9223372036854775807); COPY ticks TO 'file:///tmp/datetimes.tmp' (format 'TabSeparated', structure 'n Int64'); COPY tocks FROM 'file:///tmp/datetimes.tmp' (format 'TabSeparated', structure 'n IntervalYear'); ERROR: chdb: IntervalYear value out of range \set ECHO errors