--! qt:dataset:src
drop table date_udf;
drop table date_udf_string;
drop table date_udf_flight;

create table date_udf (d date);
create table date_udf_string (d string);
from src
  insert overwrite table date_udf 
    select '2011-05-06' limit 1
  insert overwrite table date_udf_string
    select '2011-05-06' limit 1;

create table date_udf_flight (
  ORIGIN_CITY_NAME string,
  DEST_CITY_NAME string,
  FL_DATE date,
  ARR_DELAY float,
  FL_NUM int
);
LOAD DATA LOCAL INPATH '../../data/files/flights_tiny.txt.1' OVERWRITE INTO TABLE date_udf_flight;

-- Test UDFs with date input
select unix_timestamp(cast(d as timestamp with local time zone)), unix_timestamp(d), year(d), month(d), day(d), dayofmonth(d),
    weekofyear(d), to_date(d)
  from date_udf;

select date_add(d, 5), date_sub(d, 10)
  from date_udf;

select datediff(d, d), datediff(d, '2002-03-21'), datediff('2002-03-21', d),
    datediff(cast ('2002-03-21 00:00:00' as timestamp), d),
    datediff(d, cast ('2002-03-21 00:00:00' as timestamp))
  from date_udf;

-- Test UDFs with string input
select unix_timestamp(d), year(d), month(d), day(d), dayofmonth(d), 
    weekofyear(d), to_date(d)
  from date_udf_string;

select date_add(d, 5), date_sub(d, 10)  from date_udf_string;

select
    datediff(d, d),
    datediff(d, '2002-03-21'),
    datediff(d, date '2002-03-21'),
    datediff('2002-03-21', d),
    datediff(date '2002-03-21', d),
    datediff('2002-03-21 00:00:00', d),
    datediff(timestamp '2002-03-21 00:00:00', d),
    datediff(d, '2002-03-21 00:00:00'),
    datediff(d, timestamp '2002-03-21 00:00:00'),
    datediff('2002-03-21 08:01:59', d),
    datediff(timestamp '2002-03-21 08:01:59', d),
    datediff(d, '2002-03-21 08:01:59'),
    datediff(d, timestamp '2002-03-21 08:01:59')
  from date_udf_string;

select 
    to_utc_timestamp(date '1970-01-01', 'America/Los_Angeles'),
    from_utc_timestamp(date '1970-01-01', 'America/Los_Angeles'),
    to_utc_timestamp(date '2013-06-19', 'America/Los_Angeles'),
    from_utc_timestamp(date '2013-06-19', 'America/Los_Angeles')
  from date_udf;

-- should all be true
select 
    to_utc_timestamp(date '1970-01-01', 'America/Los_Angeles') = to_utc_timestamp(timestamp('1970-01-01 00:00:00'), 'America/Los_Angeles'),
    from_utc_timestamp(date '1970-01-01', 'America/Los_Angeles') = from_utc_timestamp(timestamp('1970-01-01 00:00:00'), 'America/Los_Angeles'),
    to_utc_timestamp(date '2013-06-19', 'America/Los_Angeles') = to_utc_timestamp(timestamp('2013-06-19 00:00:00'), 'America/Los_Angeles'),
    from_utc_timestamp(date '2013-06-19', 'America/Los_Angeles') = from_utc_timestamp(timestamp('2013-06-19 00:00:00'), 'America/Los_Angeles')
  from date_udf;

-- Aggregation functions (min/max)
select min(fl_date) from date_udf_flight;
select max(fl_date) from date_udf_flight;

create external table testdatediff(datetimecol string) stored as orc;
insert into testdatediff values ('2019-09-09T10:45:49+02:00'),('2019-07-24');

select datetimecol from testdatediff where datediff(cast(current_timestamp as string), datetimecol)>183;
select cast(datetimecol as date), datetimecol from testdatediff;

drop table testdatediff;
drop table date_udf;
drop table date_udf_string;
drop table date_udf_flight;