drop table `date_test`;
drop table `timestamp_test`;
drop table `timestamptz_test`;

create table `date_test` (`mydate1` date);

insert into `date_test` VALUES
  (cast('2011-01-01 01:01:01.123' as timestamp with local time zone)),
  (cast('2011-01-01 01:01:01.123 Europe/Rome' as timestamp with local time zone)),
  (cast('2011-01-01 01:01:01.123 GMT-05:00' as timestamp with local time zone)),
  (cast('2011-01-01 01:01:01.12345678912' as timestamp with local time zone)),
  (cast('2011-01-01 01:01:01.12345678912 Europe/Rome' as timestamp with local time zone)),
  (cast('2011-01-01 01:01:01.12345678912 GMT-05:00' as timestamp with local time zone)),
  (cast('2011-01-01 01:01:01.12345678912 xyz' as timestamp with local time zone));

create table `timestamp_test` (`mydate1` timestamp);

insert into `timestamp_test` VALUES
  (cast('2011-01-01 01:01:01.123' as timestamp with local time zone)),
  (cast('2011-01-01 01:01:01.123 Europe/Rome' as timestamp with local time zone)),
  (cast('2011-01-01 01:01:01.123 GMT-05:00' as timestamp with local time zone)),
  (cast('2011-01-01 01:01:01.12345678912' as timestamp with local time zone)),
  (cast('2011-01-01 01:01:01.12345678912 Europe/Rome' as timestamp with local time zone)),
  (cast('2011-01-01 01:01:01.12345678912 GMT-05:00' as timestamp with local time zone)),
  (cast('2011-01-01 01:01:01.12345678912 xyz' as timestamp with local time zone));

create table `timestamptz_test` (`mydate1` timestamp with local time zone);

insert into `timestamptz_test` VALUES
  (cast('2011-01-01 01:01:01.123' as timestamp with local time zone)),
  (cast('2011-01-01 01:01:01.123 Europe/Rome' as timestamp with local time zone)),
  (cast('2011-01-01 01:01:01.123 GMT-05:00' as timestamp with local time zone)),
  (cast('2011-01-01 01:01:01.12345678912' as timestamp with local time zone)),
  (cast('2011-01-01 01:01:01.12345678912 Europe/Rome' as timestamp with local time zone)),
  (cast('2011-01-01 01:01:01.12345678912 GMT-05:00' as timestamp with local time zone)),
  (cast('2011-01-01 01:01:01.12345678912 xyz' as timestamp with local time zone));

select * from `date_test`;
select * from `timestamp_test`;
select * from `timestamptz_test`;

set time zone Europe/Rome;

select * from `date_test`;
select * from `timestamp_test`;
select * from `timestamptz_test`;

set hive.local.time.zone=America/Los_Angeles;

select * from `date_test`;
select * from `timestamp_test`;
select * from `timestamptz_test`;

set time  zone GMT-07:00;

select * from `date_test`;
select * from `timestamp_test`;
select * from `timestamptz_test`;

select extract(year from `mydate1`) from `timestamptz_test`;
select extract(quarter from `mydate1`) from `timestamptz_test`;
select extract(month from `mydate1`) from `timestamptz_test`;
select extract(day from `mydate1`) from `timestamptz_test`;
select extract(hour from `mydate1`) from `timestamptz_test`;
select extract(minute from `mydate1`) from `timestamptz_test`;
select extract(second from `mydate1`) from `timestamptz_test`;

select cast(`mydate1` as date) from `timestamptz_test`;
select cast(`mydate1` as timestamp with local time zone) from `date_test`;
select cast(`mydate1` as timestamp) from `timestamptz_test`;
select cast(`mydate1` as timestamp with local time zone) from `timestamp_test`;

select `mydate1` from `timestamptz_test` group by `mydate1`;
select a.`mydate1` as c1, b.`mydate1` as c2
from `timestamptz_test` a join `timestamptz_test` b
on a.`mydate1` = b.`mydate1`;

create table `timestamptz_test2` (`mydate1` timestamp with local time zone, `item` string, `price` double);
insert into `timestamptz_test2` VALUES
  ('2011-01-01 01:01:01.123', 'laptop 1', 9.2),
  ('2011-01-01 01:01:01.123', 'mouse 1', 3.1),
  ('2011-01-01 01:01:01.123 Europe/Rome', 'keyboard 1', 4.2),
  ('2011-01-01 01:01:01.123 GMT-05:00', 'keyboard 2', 3.9),
  ('2011-01-01 01:01:01.123 GMT-05:00', 'keyboard 3', 3.99),
  ('2011-01-01 01:01:01.12345678912', 'mouse 2', 4.594),
  ('2011-01-01 01:01:01.12345678912 Europe/Rome', 'laptop 2', 10),
  ('2011-01-01 01:01:01.12345678912 Europe/Rome', 'hdmi', 1.25),
  ('2011-01-01 01:01:01.12345678912 GMT-05:00', 'pin', null),
  ('2011-01-01 01:01:01.12345678912 xyz', 'cable', 0.0);
select `item`, `price`,
rank() over (partition by `mydate1` order by `price`) as r
from `timestamptz_test2`;
select `item`, `price`,
rank() over (partition by cast(`mydate1` as date) order by `price`) as r
from `timestamptz_test2`;