timetz

# LogicTest: default parallel-stmts distsql

# Note that the odd '0000-01-01 hh:mi:ss +0000 +0000' result format is an
# artifact of how pq displays TIMETZs.

query T
SELECT '12:00:00':::TIMETZ;
----
0000-01-01 12:00:00 +0000 +0000

query T
SELECT '12:00:00.456':::TIMETZ;
----
0000-01-01 12:00:00.456 +0000 +0000

query T
SELECT '12:00:00.456789':::TIMETZ;
----
0000-01-01 12:00:00.456789 +0000 +0000

query T
SELECT '12:00:00.456789+00':::TIMETZ;
----
0000-01-01 12:00:00.456789 +0000 +0000

query T
SELECT '12:00:00.456789-07':::TIMETZ;
----
0000-01-01 19:00:00.456789 +0000 +0000

query T
SELECT '23:59:59.999999-10':::TIMETZ;
----
0000-01-01 09:59:59.999999 +0000 +0000

statement error could not parse
SELECT '24:00:00':::TIMETZ;

query T
SELECT TIMETZ '12:00:00-07';
----
0000-01-01 19:00:00 +0000 +0000

# Casting

query T
SELECT '12:00:00-07'::TIMETZ;
----
0000-01-01 19:00:00 +0000 +0000

query T
select '12:00:00-07':::STRING::TIMETZ;
----
0000-01-01 19:00:00 +0000 +0000

query T
select '22:00:00-07':::STRING::TIMETZ;
----
0000-01-01 05:00:00 +0000 +0000

query T
SELECT '09:00:00.456-07' COLLATE de::TIMETZ;
----
0000-01-01 16:00:00.456 +0000 +0000

query T
SELECT '2017-01-01 12:00:00-07':::TIMESTAMPTZ::TIMETZ;
----
0000-01-01 19:00:00 +0000 +0000

query T
select '12:00:00-07:00'::TIMETZ::STRING;
----
12:00:00-07:00

# Comparison

query B
select '12:00:00+00':::TIMETZ = '12:00:00+00':::TIMETZ
----
true

query B
select '12:00:00-06':::TIMETZ = '12:00:00-07':::TIMETZ
----
false

query B
select '12:00:00+00':::TIMETZ >= '12:00:00+00':::TIMETZ
----
true

query B
select '12:00:00+00':::TIMETZ <= '12:00:00+00':::TIMETZ
----
true

query B
SELECT '12:00:00+01:00':::TIMETZ < '11:59:59.999999+00':::TIMETZ
----
true

query B
SELECT '12:00:00+01:00':::TIMETZ < '11:59:59.999999+02':::TIMETZ
----
false

query B
SELECT '12:00:00+01:00':::TIMETZ > '11:59:59.999999+02':::TIMETZ
----
true

query B
SELECT '12:00:00-05':::TIMETZ IN ('12:00:00');
----
false

query B
SELECT '12:00:00-05':::TIMETZ IN ('12:00:00-05');
----
true

query B
SELECT '12:00:00-05':::TIMETZ IN ('12:00:00-07');
----
false

query B
SELECT '12:00:00-05':::TIMETZ IN ('11:00:00-06');
----
true

# Arithmetic

query T
SELECT '12:00:00-01':::TIMETZ + '1s':::INTERVAL
----
0000-01-01 13:00:01 +0000 +0000

query T
SELECT '23:59:59+00':::TIMETZ + '1s':::INTERVAL
----
0000-01-01 00:00:00 +0000 +0000

query T
SELECT '12:00:00-07':::TIMETZ + '1d':::INTERVAL
----
0000-01-01 19:00:00 +0000 +0000

query T
SELECT '1s':::INTERVAL + '12:00:00+03':::TIMETZ
----
0000-01-01 09:00:01 +0000 +0000

query T
SELECT '12:00:00-07':::TIMETZ - '1s':::INTERVAL
----
0000-01-01 18:59:59 +0000 +0000

query T
SELECT '12:00:00-07':::TIMETZ - '1d':::INTERVAL
----
0000-01-01 19:00:00 +0000 +0000

query T
SELECT '12:00:00-07':::TIMETZ - '11:59:59':::TIME
----
0000-01-01 07:00:01 +0000 +0000

query T
SELECT '11:59:59+00':::TIMETZ - '12:00:00':::TIME
----
0000-01-01 23:59:59 +0000 +0000

query T
SELECT '00:00:59+07':::TIMETZ + '12:00:00':::TIME
----
0000-01-01 05:00:59 +0000 +0000

query T
SELECT '2017-01-01':::DATE + '12:00:00-03':::TIMETZ
----
2017-01-01 15:00:00 +0000 +0000

query T
SELECT '12:00:00+03':::TIMETZ + '2017-01-01':::DATE
----
2017-01-01 09:00:00 +0000 +0000

# Storage

statement ok
CREATE TABLE timetzs (t timetz PRIMARY KEY)

statement ok
INSERT INTO timetzs VALUES
  ('00:00:00-07'),
  ('00:00:00.000001+06'),
  ('11:59:59.999999+10'),
  ('12:00:00-05'),
  ('12:00:00.000001-05'),
  ('23:59:59.999999+00')

query T
SELECT * FROM timetzs ORDER BY t
----
0000-01-01 18:00:00.000001 +0000 +0000
0000-01-01 01:59:59.999999 +0000 +0000
0000-01-01 07:00:00 +0000 +0000
0000-01-01 17:00:00 +0000 +0000
0000-01-01 17:00:00.000001 +0000 +0000
0000-01-01 23:59:59.999999 +0000 +0000

statement ok
CREATE TABLE tzarrays (timetzs TIMETZ[])

statement ok
INSERT INTO tzarrays VALUES
  (ARRAY[]),
  (ARRAY['00:00:00-07']),
  (ARRAY['00:00:00-07', '12:00:00.000001-07']),
  ('{13:00:00-07}'::TIMETZ[])

query T rowsort
SELECT * FROM tzarrays
----
{}
{07:00:00+00:00}
{07:00:00+00:00,19:00:00.000001+00:00}
{20:00:00+00:00}

# Built-ins

query I
SELECT extract(hour from timetz '12:01:02.345678-07')
----
12

query I
SELECT extract(minute from timetz '12:01:02.345678+03')
----
1

query I
SELECT extract(second from timetz '12:01:02.345678-06')
----
2

query I
SELECT extract(millisecond from timetz '12:01:02.345678+00')
----
345

query I
SELECT extract(microsecond from timetz '12:01:02.345678-05')
----
345678

query I
SELECT extract(epoch from timetz '12:00:00+04')
----
43200