Materialize/test/sqllogictest/cockroach/extract.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 evaluation test suite in CockroachDB.
# The original file was retrieved on October 7, 2019 from:
#
#     https://github.com/cockroachdb/cockroach/blob/d2f7fbf5dd1fc1a099bbad790a2e1f7c60a66cc3/pkg/sql/sem/tree/testdata/eval/extract
#
# 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.

# Extract from dates.

skipif postgresql # literal coercion for dates is not supported
query I
SELECT extract(year FROM '2010-09-28')
----
2010

query I
SELECT extract(year FROM DATE '2010-09-28')
----
2010

query I
SELECT extract(month FROM DATE '2010-09-28')
----
9

query I
SELECT extract(day FROM DATE '2010-09-28')
----
28

skipif postgresql # dayofyear is not supported
query I
SELECT extract(dayofyear FROM DATE '2010-09-28')
----
271

skipif postgresql # week is not supported
query I
SELECT extract(week FROM DATE '2010-01-14')
----
2

skipif postgresql # dayofweek is not supported
query I
SELECT extract(dayofweek FROM DATE '2010-09-28')
----
2

skipif postgresql # quarter is not supported
query I
SELECT extract(quarter FROM DATE '2010-09-28')
----
3

# Extract from times. These don't currently work, because we don't support the
# TIME data type, which is distinct from TIMESTAMP and DATE.

skipif postgresql
query I
SELECT extract(hour FROM TIME '12:00:00')
----
12

skipif postgresql
query I
SELECT extract(minute FROM TIME '12:30:00')
----
30

skipif postgresql
query I
SELECT extract(second FROM TIME '12:00:30')
----
30

skipif postgresql
query I
SELECT extract(millisecond FROM TIME '12:00:00.123456')
----
123

skipif postgresql
query I
SELECT extract(microsecond FROM TIME '12:00:00.123456')
----
123456

# Extract from timestamps.

skipif postgresql # literal coercion for timestamps is not supported
query I
SELECT extract(year FROM '2010-09-28 12:13:14.1')
----
2010

query I
SELECT extract(year FROM TIMESTAMP '2010-09-28 12:13:14.1')
----
2010

query I
SELECT extract(month FROM TIMESTAMP '2010-09-28 12:13:14.1')
----
9

query I
SELECT extract(day FROM TIMESTAMP '2010-09-28 12:13:14.1')
----
28

skipif postgresql # dayofyear is not supported
query I
SELECT extract(dayofyear FROM TIMESTAMP '2010-09-28 12:13:14.1')
----
271

skipif postgresql # week is not supported
query I
SELECT extract(week FROM TIMESTAMP '2010-01-14 12:13:14.1')
----
2

skipif postgresql # dayofweek is not supported
query I
SELECT extract(dayofweek FROM TIMESTAMP '2010-09-28 12:13:14.1')
----
2

skipif postgresql # quarter is not supported
query I
SELECT extract(quarter FROM TIMESTAMP '2010-09-28 12:13:14.1')
----
3

query I
SELECT extract(hour FROM TIMESTAMP '2010-01-10 12:13:14.1')
----
12

query I
SELECT extract(minute FROM TIMESTAMP '2010-01-10 12:13:14.1')
----
13

query R
SELECT extract(second FROM TIMESTAMP '2010-01-10 12:13:14.1')
----
14.1

skipif postgresql # millisecond is not supported
query R
SELECT extract(millisecond FROM TIMESTAMP '2010-01-10 12:13:14.123456')
----
14123.456

skipif postgresql # microsecond is not supported
query I
SELECT extract(microsecond FROM TIMESTAMP '2010-01-10 12:13:14.123456')
----
123456

query I
SELECT extract(epoch FROM TIMESTAMP '2010-01-10 12:13:14.1')
----
1263125594

# Extract from intervals.

skipif postgresql # literal coercion for intervals is not supported
query I
SELECT extract(hour FROM '123m')
----
2

query I
SELECT extract(hour FROM INTERVAL '123' MINUTE)
----
2

query I
SELECT extract(minute FROM INTERVAL '23:10' MINUTE TO SECOND)
----
23

query R
SELECT extract(second FROM INTERVAL '10:20.30' MINUTE TO SECOND)
----
20.3

skipif postgresql # millisecond is not supported
query R
SELECT extract(millisecond FROM INTERVAL '20.3040' SECOND)
----
20304

query R
SELECT extract(epoch FROM INTERVAL '10:20.30' MINUTE TO SECOND)
----
620.3