Materialize/test/sqllogictest/cockroach/time.slt

# Copyright 2015 - 2019 The Cockroach Authors. All rights reserved.
# Copyright Materialize, Inc. and contributors. All rights reserved.
#
# Use of this software is governed by the Business Source License
# included in the LICENSE file at the root of this repository.
#
# As of the Change Date specified in that file, in accordance with
# the Business Source License, use of this software will be governed
# by the Apache License, Version 2.0.
#
# This file is derived from the logic test suite in CockroachDB. The
# original file was retrieved on June 10, 2019 from:
#
#     https://github.com/cockroachdb/cockroach/blob/d2f7fbf5dd1fc1a099bbad790a2e1f7c60a66cc3/pkg/sql/logictest/testdata/logic_test/time
#
# The original source code is subject to the terms of the Apache
# 2.0 license, a copy of which can be found in the LICENSE file at the
# root of this repository.

mode cockroach

simple conn=mz_system,user=mz_system
ALTER SYSTEM SET unsafe_enable_table_keys = true
----
COMPLETE 0

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

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

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

query T
SELECT '00:00:00':::TIME;
----
0000-01-01 00:00:00 +0000 UTC

query T
SELECT '23:59:59.999999':::TIME;
----
0000-01-01 23:59:59.999999 +0000 UTC

query T
select ('24:00'::TIME)::STRING
----
24:00:00

query T
SELECT ('24:00:00'::TIME)::STRING
----
24:00:00

statement error could not parse
SELECT '124:00'::TIME;

statement error could not parse
SELECT '24:00:01'::TIME;

statement error could not parse
SELECT '24:00:00.001'::TIME;


# Timezone should be ignored.
query T
SELECT '12:00:00-08:00':::TIME;
----
0000-01-01 12:00:00 +0000 UTC

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

# Casting

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

query T
select '12:00:00':::STRING::TIME;
----
0000-01-01 12:00:00 +0000 UTC

query T
SELECT '12:00:00' COLLATE de::TIME;
----
0000-01-01 12:00:00 +0000 UTC

query T
SELECT '2017-01-01 12:00:00':::TIMESTAMP::TIME;
----
0000-01-01 12:00:00 +0000 UTC

query T
SELECT '2017-01-01 12:00:00-05':::TIMESTAMPTZ::TIME;
----
0000-01-01 12:00:00 +0000 UTC

query T
SELECT '12h':::INTERVAL::TIME;
----
0000-01-01 12:00:00 +0000 UTC

query T
SELECT '12:00:00':::TIME::INTERVAL;
----
12:00:00

query T
SELECT '12:00:00':::TIME::STRING;
----
12:00:00

# Comparison

query B
SELECT '12:00:00':::TIME = '12:00:00':::TIME
----
true

query B
SELECT '12:00:00':::TIME = '12:00:00.000000':::TIME
----
true

query B
SELECT '12:00:00':::TIME = '12:00:00.000001':::TIME
----
false

query B
SELECT '12:00:00':::TIME < '12:00:00.000001':::TIME
----
true

query B
SELECT '12:00:00':::TIME < '12:00:00':::TIME
----
false

query B
SELECT '12:00:00':::TIME < '11:59:59.999999':::TIME
----
false

query B
SELECT '12:00:00':::TIME > '11:59:59.999999':::TIME
----
true

query B
SELECT '12:00:00':::TIME > '12:00:00':::TIME
----
false

query B
SELECT '12:00:00':::TIME > '12:00:00.000001':::TIME
----
false

query B
SELECT '12:00:00':::TIME <= '12:00:00':::TIME
----
true

query B
SELECT '12:00:00':::TIME >= '12:00:00':::TIME
----
true

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

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

# Arithmetic

query T
SELECT '12:00:00':::TIME + '1s':::INTERVAL
----
0000-01-01 12:00:01 +0000 UTC

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

query T
SELECT '12:00:00':::TIME + '1d':::INTERVAL
----
0000-01-01 12:00:00 +0000 UTC

query T
SELECT '1s':::INTERVAL + '12:00:00':::TIME
----
0000-01-01 12:00:01 +0000 UTC

query T
SELECT '12:00:00':::TIME - '1s':::INTERVAL
----
0000-01-01 11:59:59 +0000 UTC

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

query T
SELECT '12:00:00':::TIME - '1d':::INTERVAL
----
0000-01-01 12:00:00 +0000 UTC

query T
SELECT '12:00:00':::TIME - '11:59:59':::TIME
----
00:00:01

query T
SELECT '11:59:59':::TIME - '12:00:00':::TIME
----
-00:00:01

query T
SELECT '2017-01-01':::DATE + '12:00:00':::TIME
----
2017-01-01 12:00:00 +0000 +0000

query T
SELECT '12:00:00':::TIME + '2017-01-01':::DATE
----
2017-01-01 12:00:00 +0000 +0000

query T
SELECT '2017-01-01':::DATE - '12:00:00':::TIME
----
2016-12-31 12:00:00 +0000 +0000

# Storage

statement ok
CREATE TABLE times (t time PRIMARY KEY)

statement ok
INSERT INTO times VALUES
  ('00:00:00'),
  ('00:00:00.000001'),
  ('11:59:59.999999'),
  ('12:00:00'),
  ('12:00:00.000001'),
  ('23:59:59.999999')

query T
SELECT * FROM times ORDER BY t
----
0000-01-01 00:00:00 +0000 UTC
0000-01-01 00:00:00.000001 +0000 UTC
0000-01-01 11:59:59.999999 +0000 UTC
0000-01-01 12:00:00 +0000 UTC
0000-01-01 12:00:00.000001 +0000 UTC
0000-01-01 23:59:59.999999 +0000 UTC

statement ok
CREATE TABLE arrays (times TIME[])

statement ok
INSERT INTO arrays VALUES
  (ARRAY[]),
  (ARRAY['00:00:00']),
  (ARRAY['00:00:00', '12:00:00.000001']),
  ('{13:00:00}'::TIME[])

query T rowsort
SELECT * FROM arrays
----
{}
{00:00:00}
{00:00:00,12:00:00.000001}
{13:00:00}

# Built-ins

query T
SELECT date_trunc('hour', time '12:01:02.345678')
----
12:00:00

query T
SELECT date_trunc('minute', time '12:01:02.345678')
----
12:01:00

query T
SELECT date_trunc('second', time '12:01:02.345678')
----
12:01:02

query T
SELECT date_trunc('millisecond', time '12:01:02.345678')
----
12:01:02.345

query T
SELECT date_trunc('microsecond', time '12:01:02.345678')
----
12:01:02.345678

query error pgcode 22023 date_trunc\(\): unsupported timespan: day
SELECT date_trunc('day', time '12:01:02.345')

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

query I
SELECT extract(minute from time '12:01:02.345678')
----
1

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

query I
SELECT extract(millisecond from time '12:01:02.345678')
----
345

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

query I
SELECT extract(epoch from time '12:00:00')
----
43200

query error pgcode 22023 extract\(\): unsupported timespan: day
SELECT extract(day from time '12:00:00')