set hive.parquet.write.int64.timestamp=true;
set time zone Asia/Singapore;

--store timestamps as strings for copying into different schemas
create table parquet_timestamp_staging (i int, s string);

insert into parquet_timestamp_staging
values
(0, '0001-01-01'),
(1, '1677-09-21 00:12:43.145224192'),
(2, '1969-12-31 23:59:59.99999999999999999'),
(3, '1970-01-01 00:00:00'),
(4, '2013-09-27 01:36:18.000000001'),
(5, '2018-01-02 13:14:15.678999'),
(6, '2262-04-11 23:47:16.854775807'),
(7, '9999-12-31 23:59:59.999999999999');

create table parquet_int64_timestamp (i int, ts timestamp) stored as parquet;

--test nanoseconds read/write
set hive.parquet.timestamp.time.unit=nanos;
insert into parquet_int64_timestamp select i, cast (s as timestamp) from parquet_timestamp_staging;
select * from parquet_int64_timestamp order by i;

--test microseconds read/write
set hive.parquet.timestamp.time.unit=micros;
insert into parquet_int64_timestamp select i + 10, cast (s as timestamp) from parquet_timestamp_staging;
select * from parquet_int64_timestamp order by i;

--test milliseconds read/write
set hive.parquet.timestamp.time.unit=millis;
insert into parquet_int64_timestamp select i + 20, cast (s as timestamp) from parquet_timestamp_staging;
select * from parquet_int64_timestamp order by i;


--time zone should not affect values, since timestamp is time zone agnostic
set time zone America/Buenos_Aires;

--test filters
select * from parquet_int64_timestamp where ts >  '1969-12-31 23:59:59.9'
                                            and
                                            ts <  '1970-01-01 00:00:00.0' order by i;
select * from parquet_int64_timestamp where ts <= '1970-01-01 00:00:00.0'
                                            and
                                            ts >= '1970-01-01 00:00:00.0' order by i;
select * from parquet_int64_timestamp where ts =  '1970-01-01 00:00:00.0' order by i;

select * from parquet_int64_timestamp where ts between cast ('1969-12-31 23:59:59.9' as timestamp) and
                                                       cast ('1970-01-01 00:00:00' as timestamp)
                                      order by i;