testdata/workloads/functional-query/queries/QueryTest/parquet-int64-timestamps.test

====
---- QUERY
# Check that int64 timestamp columns can be read as TIMESTAMP.
set timezone=CET;
select * from int64_timestamps_plain;
---- TYPES
TIMESTAMP,TIMESTAMP,TIMESTAMP,TIMESTAMP
---- RESULTS
1986-06-26 02:00:00,1986-06-26 00:00:00,1986-06-26 02:00:00,1986-06-26 00:00:00
1986-12-05 01:00:00,1986-12-05 00:00:00,1986-12-05 01:00:00,1986-12-05 00:00:00
====
---- QUERY
# Check that int64 timestamp columns can be read as BIGINT.
select * from int64_bigints_plain;
---- TYPES
BIGINT,BIGINT,BIGINT,BIGINT
---- RESULTS
520128000000,520128000000,520128000000000,520128000000000
534124800000,534124800000,534124800000000,534124800000000
====
---- QUERY
# Check that the test tables are correctly filled with data.
select count(*)
from int64_timestamps_dict t join int64_bigints_dict b on t.id = b.id;
---- TYPES
BIGINT
---- RESULTS
72
====
---- QUERY
# List rows where one of the int64->timestamp conversion is incorrect.
set timezone=CET;
select *
from int64_timestamps_dict t join int64_bigints_dict b on t.id = b.id
where t.new_logical_milli_local != milliseconds_add("1970-01-01", b.new_logical_milli_local)
  or t.new_logical_milli_utc != from_utc_timestamp(milliseconds_add("1970-01-01", b.new_logical_milli_utc), "CET")
  or t.new_logical_micro_local != microseconds_add("1970-01-01", b.new_logical_micro_local)
  or t.new_logical_micro_utc != from_utc_timestamp(microseconds_add("1970-01-01", b.new_logical_micro_utc), "CET");
---- TYPES
INT,TIMESTAMP,TIMESTAMP,TIMESTAMP,TIMESTAMP,INT,BIGINT,BIGINT,BIGINT,BIGINT
---- RESULTS
# Expect empty result.
====
---- QUERY
# Test that the table is filled correctly.
set timezone=UTC;
select * from int64_timestamps_at_dst_changes;
---- TYPES
BIGINT,INT,TIMESTAMP,TIMESTAMP
---- RESULTS
42,1,1970-01-01 00:00:42,1970-01-01 00:00:42
1509237900,1,2017-10-29 00:45:00,2017-10-29 00:45:00
1509240600,1,2017-10-29 01:30:00,2017-10-29 01:30:00
1509237000,2,2017-10-29 00:30:00,2017-10-29 00:30:00
1509239700,2,2017-10-29 01:15:00,2017-10-29 01:15:00
1512520200,2,2017-12-06 00:30:00,2017-12-06 00:30:00
1509237900,3,2017-10-29 00:45:00,2017-10-29 00:45:00
1509238800,3,2017-10-29 01:00:00,2017-10-29 01:00:00
1509239400,3,2017-10-29 01:10:00,2017-10-29 01:10:00
====
---- QUERY
# Test filtering on timestamps that fall on the DST change. Stat filtering may skip this
# value if not implemented carefully.
set timezone=CET;
select count(*) from int64_timestamps_at_dst_changes
where millisutc = "2017-10-29 02:45:00" and microsutc = "2017-10-29 02:45:00";
---- TYPES
BIGINT
---- RESULTS
2
====
---- QUERY
set timezone=CET;
select count(*) from int64_timestamps_at_dst_changes
where millisutc = "2017-10-29 02:00:00";
---- TYPES
BIGINT
---- RESULTS
1
====
---- QUERY
set timezone=CET;
select count(*) from int64_timestamps_at_dst_changes
where microsutc = "2017-10-29 02:10:00";
---- TYPES
BIGINT
---- RESULTS
1
====
---- QUERY
set timezone=CET;
select * from int64_timestamps_nano
---- TYPES
BIGINT,TIMESTAMP,TIMESTAMP
---- RESULTS
0,1970-01-01 01:00:00,1970-01-01 00:00:00
-2147483648,1970-01-01 00:59:57.852516352,1969-12-31 23:59:57.852516352
2147483647,1970-01-01 01:00:02.147483647,1970-01-01 00:00:02.147483647
-9223372036854775808,1677-09-21 01:12:43.145224192,1677-09-21 00:12:43.145224192
9223372036854775807,2262-04-12 01:47:16.854775807,2262-04-11 23:47:16.854775807
====
---- QUERY
create table int96_nanos (ts timestamp) stored as parquet;
====
---- QUERY
# Insert edge values as "normal" int96 timestamps that can represent all values.
set parquet_timestamp_type=INT96_NANOS;
insert into int96_nanos values
("1400-01-01"),
("1677-09-21 00:12:43.145224191"),
("1677-09-21 00:12:43.145224192"),
("2019-01-18 00:00:00.000000001"),
("2019-01-18 00:00:00.000001"),
("2019-01-18 00:00:00.001"),
("2019-01-18 23:59:59.999"),
("2019-01-18 23:59:59.999999"),
("2019-01-18 23:59:59.999999999"),
("2262-04-11 23:47:16.854775807"),
("2262-04-11 23:47:16.854775808"),
("9999-12-31 23:59:59.999"),
("9999-12-31 23:59:59.999999"),
("9999-12-31 23:59:59.999999999");
====
---- QUERY
# Inserted values are rounded to milliseconds.
set parquet_timestamp_type=INT64_MILLIS;
create table int64_millis stored as parquet as select * from int96_nanos;
select * from int64_millis;
---- RESULTS
1400-01-01 00:00:00
1677-09-21 00:12:43.145000000
1677-09-21 00:12:43.145000000
2019-01-18 00:00:00
2019-01-18 00:00:00
2019-01-18 00:00:00.001000000
2019-01-18 23:59:59.999000000
2019-01-18 23:59:59.999000000
2019-01-18 23:59:59.999000000
2262-04-11 23:47:16.854000000
2262-04-11 23:47:16.854000000
9999-12-31 23:59:59.999000000
9999-12-31 23:59:59.999000000
9999-12-31 23:59:59.999000000
====
---- QUERY
# Inserted values are rounded to microseconds.
set parquet_timestamp_type=INT64_MICROS;
create table int64_micros stored as parquet as select * from int96_nanos;
select * from int64_micros;
---- RESULTS
1400-01-01 00:00:00
1677-09-21 00:12:43.145224000
1677-09-21 00:12:43.145224000
2019-01-18 00:00:00
2019-01-18 00:00:00.000001000
2019-01-18 00:00:00.001000000
2019-01-18 23:59:59.999000000
2019-01-18 23:59:59.999999000
2019-01-18 23:59:59.999999000
2262-04-11 23:47:16.854775000
2262-04-11 23:47:16.854775000
9999-12-31 23:59:59.999000000
9999-12-31 23:59:59.999999000
9999-12-31 23:59:59.999999000
====
---- QUERY
# Values outside the [1677-09-21 00:12:43.145224192 .. 2262-04-11 23:47:16.854775807]
# range are inserted as NULLs.
set parquet_timestamp_type=INT64_NANOS;
create table int64_nanos stored as parquet as select * from int96_nanos;
select * from int64_nanos;
---- RESULTS
NULL
NULL
1677-09-21 00:12:43.145224192
2019-01-18 00:00:00.000000001
2019-01-18 00:00:00.000001000
2019-01-18 00:00:00.001000000
2019-01-18 23:59:59.999000000
2019-01-18 23:59:59.999999000
2019-01-18 23:59:59.999999999
2262-04-11 23:47:16.854775807
NULL
NULL
NULL
NULL
====