| ==== |
| ---- 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 |
| ==== |
| ---- QUERY |
| # IMPALA-10426: invalid timestamps should be NULLs when inserted. |
| create table invalid_timestamps (ts timestamp) stored as parquet; |
| insert into invalid_timestamps |
| values ('1021-01-14 11:36:44.916732000'), ('2021-02-34 11:36:44.916732000'); |
| select * from invalid_timestamps; |
| ---- RESULTS |
| NULL |
| NULL |
| ==== |
| ---- QUERY |
| truncate table invalid_timestamps; |
| set parquet_timestamp_type=INT64_MILLIS; |
| insert into invalid_timestamps |
| values ('1021-01-14 11:36:44.916732000'), ('2021-02-34 11:36:44.916732000'); |
| select * from invalid_timestamps; |
| ---- RESULTS |
| NULL |
| NULL |
| ==== |
| ---- QUERY |
| truncate table invalid_timestamps; |
| set parquet_timestamp_type=INT64_MICROS; |
| insert into invalid_timestamps |
| values ('1021-01-14 11:36:44.916732000'), ('2021-02-34 11:36:44.916732000'); |
| select * from invalid_timestamps; |
| ---- RESULTS |
| NULL |
| NULL |
| ==== |
| ---- QUERY |
| truncate table invalid_timestamps; |
| set parquet_timestamp_type=INT64_NANOS; |
| insert into invalid_timestamps |
| values ('1021-01-14 11:36:44.916732000'), ('2021-02-34 11:36:44.916732000'); |
| select * from invalid_timestamps; |
| ---- RESULTS |
| NULL |
| NULL |
| ==== |