# 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. mode cockroach statement ok CREATE TABLE dateish ( a DATE ) statement ok INSERT INTO dateish VALUES (DATE '2000-01-01'), (DATE '2019-12-31') query T rowsort SELECT a FROM dateish ---- 2000-01-01 2019-12-31 query T SELECT max(a) FROM dateish ---- 2019-12-31 query T SELECT min(a) FROM dateish ---- 2000-01-01 statement ok CREATE TABLE timestampish ( b timestamp ) statement ok INSERT INTO timestampish VALUES (TIMESTAMP '1969-06-01 10:10:10.410'), (TIMESTAMP '1997-02-03 11:12:59.9'), (TIMESTAMP '2020-01-01 1:2:3.789') query T rowsort SELECT b FROM timestampish ---- 1969-06-01 10:10:10.41 1997-02-03 11:12:59.9 2020-01-01 01:02:03.789 query T SELECT max(b) FROM timestampish ---- 2020-01-01 01:02:03.789 query T SELECT min(b) FROM timestampish ---- 1969-06-01 10:10:10.41 statement ok CREATE TABLE timeish ( b time ) statement ok INSERT INTO timeish VALUES (TIME '10:10:10.410'), (TIME '11:12:59.9'), (TIME '1:2:3.789') query T rowsort SELECT b FROM timeish ---- 10:10:10.41 11:12:59.9 01:02:03.789 query T SELECT max(b) FROM timeish ---- 11:12:59.9 query T SELECT min(b) FROM timeish ---- 01:02:03.789 query T SELECT INTERVAL '1' MONTH ---- 1 month query T SELECT INTERVAL '1' YEAR ---- 1 year query T SELECT INTERVAL '1-3' YEAR TO MONTH ---- 1 year 3 months query T SELECT INTERVAL '1' MINUTE ---- 00:01:00 query T SELECT INTERVAL '1 MIN' ---- 00:01:00 query T SELECT INTERVAL '1 MINS' ---- 00:01:00 query T SELECT INTERVAL '1 SECS' ---- 00:00:01 query T SELECT INTERVAL '1 SEC' ---- 00:00:01 statement ok CREATE TABLE iv_ish ( b interval ) statement ok INSERT INTO iv_ish VALUES (INTERVAL '1' YEAR), (INTERVAL '3' HOUR), (INTERVAL '4d 2h' DAY TO HOUR) query T rowsort SELECT * FROM iv_ish ---- 4 days 02:00:00 1 year 03:00:00 # Date-time literals query T SELECT DATE '2000-01-01' ---- 2000-01-01 query T SELECT DATE '2000 01-01' ---- 2000-01-01 query T SELECT DATE '2000 01 01' ---- 2000-01-01 query T SELECT DATE '20000101' ---- 2000-01-01 statement error SELECT DATE '2000-01 01' query T SELECT TIMESTAMP '2000-01-01 01:02:03' ---- 2000-01-01 01:02:03 query T SELECT TIMESTAMP '2000 01-01 01:02:03' ---- 2000-01-01 01:02:03 query T SELECT TIMESTAMP '2000 01 01 01:02:03' ---- 2000-01-01 01:02:03 query T SELECT TIMESTAMP '20010101 01:02:03'; ---- 2001-01-01 01:02:03 statement error SELECT TIMESTAMP '2000-01 01 01:02:03' # Date arithmetic with month intervals. query T SELECT DATE '2000-01-01' + INTERVAL '1' MONTH ---- 2000-02-01 00:00:00 # Test that DATE + INTERVAL addition works in both orders. query T SELECT INTERVAL '1' MONTH + DATE '2000-01-01' ---- 2000-02-01 00:00:00 query T SELECT DATE '2000-01-01' + INTERVAL '1' YEAR ---- 2001-01-01 00:00:00 query T SELECT DATE '2000-01-01' + INTERVAL '-1' MONTH ---- 1999-12-01 00:00:00 query T SELECT DATE '2000-01-01' - INTERVAL '1' MONTH ---- 1999-12-01 00:00:00 query T SELECT DATE '2000-01-01' - INTERVAL '-1' MONTH ---- 2000-02-01 00:00:00 query T SELECT DATE '2000-01-01' - INTERVAL '1' YEAR ---- 1999-01-01 00:00:00 query error operator does not exist: interval \- date SELECT INTERVAL '1' YEAR - DATE '2000-01-01' query T SELECT DATE '2000-01-01' + TIME '01:02:03' ---- 2000-01-01 01:02:03 # Date arithmetic with self. Should behave as interval. query T SELECT DATE '2019-02-03' - DATE '2019-01-01'; ---- 33 # Time arithmetic with intervals. query T SELECT TIME '01:02:03' + INTERVAL '04:05:06' ---- 05:07:09 query T SELECT TIME '04:05:06' - INTERVAL '01:02:03' ---- 03:03:03 query T SELECT TIME '01:02:03' - INTERVAL '04:05:06' ---- 20:56:57 query T SELECT TIME '04:05:06' + INTERVAL '-01:02:03' ---- 03:03:03 query T SELECT TIME '01:02:03' - INTERVAL '-04:05:06' ---- 05:07:09 # Time arithmetic with self. Should behave as interval query T SELECT TIME '04:05:06' - TIME '01:02:03' ---- 03:03:03 query T SELECT TIME '01:02:03' - TIME '04:05:06' ---- -03:03:03 # Timestamp arithmetic with month intervals. Should behave the same as DATE. query T SELECT TIMESTAMP '2000-01-01 00:00:00' + INTERVAL '1' YEAR ---- 2001-01-01 00:00:00 query T SELECT TIMESTAMP '2000-01-01 00:00:00' - INTERVAL '1' YEAR ---- 1999-01-01 00:00:00 query error operator does not exist: interval \- timestamp SELECT INTERVAL '1' YEAR - TIMESTAMP '2000-01-01 00:00:00' # Date arithmetic with duration intervals. query T SELECT DATE '2000-01-01' + INTERVAL '7' DAY ---- 2000-01-08 00:00:00 # Test that DATE + INTERVAL addition works in both orders. query T SELECT INTERVAL '7' DAY + DATE '2000-01-01' ---- 2000-01-08 00:00:00 query T SELECT INTERVAL '2 YRS 5 DAYS' ---- 2 years 5 days query T SELECT INTERVAL '2 YR 5 DAYS' ---- 2 years 5 days query T SELECT DATE '2000-01-01' + INTERVAL '7 5:4:3.2' DAY TO SECOND ---- 2000-01-08 05:04:03.2 query T SELECT DATE '2000-01-01' + INTERVAL '4' HOUR ---- 2000-01-01 04:00:00 query T SELECT DATE '2000-01-01' + INTERVAL '4 HR' ---- 2000-01-01 04:00:00 query T SELECT DATE '2000-01-01' + INTERVAL '3' MINUTE ---- 2000-01-01 00:03:00 query T SELECT DATE '2000-01-01' + INTERVAL '22' SECOND ---- 2000-01-01 00:00:22 query T SELECT DATE '2000-01-01' + INTERVAL '22.0044' SECOND ---- 2000-01-01 00:00:22.0044 query T SELECT DATE '2000-01-01' - INTERVAL '22' DAY ---- 1999-12-10 00:00:00 query T SELECT DATE '2000-01-01' - INTERVAL '22' SECOND ---- 1999-12-31 23:59:38 # Timestamp arithmetic with duration intervals. query T SELECT TIMESTAMP '2000-01-01 00:00:00' + INTERVAL '7' HOUR ---- 2000-01-01 07:00:00 # date and time comparisons after interval math query B SELECT DATE '2000-01-01' < DATE '1999-01-01' + INTERVAL '2' YEAR ---- true query B SELECT DATE '2000-01-01' > DATE '1999-01-01' + INTERVAL '2' YEAR ---- false query B SELECT DATE '2000-01-01' <= DATE '1999-01-01' + INTERVAL '2' YEAR ---- true query B SELECT DATE '2000-01-01' >= DATE '1999-01-01' + INTERVAL '2' YEAR ---- false query T SELECT DATE '2001-01-01' + INTERVAL '3' YEAR ---- 2004-01-01 00:00:00 # Check Comparisons query T SELECT * FROM dateish WHERE a <= DATE '1999-01-01' + INTERVAL '2' YEAR ---- 2000-01-01 query T SELECT * FROM dateish WHERE a < DATE '1999-01-01' + INTERVAL '2' YEAR ---- 2000-01-01 query T SELECT * FROM dateish WHERE a <= DATE '1999-12-31' + INTERVAL '2' DAY ---- 2000-01-01 query T SELECT * FROM dateish WHERE a = DATE '1999-12-31' + INTERVAL '1' DAY ---- 2000-01-01 # same as above, but inverted query T SELECT * FROM dateish WHERE a >= DATE '1999-01-01' + INTERVAL '2' YEAR ---- 2019-12-31 query T SELECT * FROM dateish WHERE a > DATE '1999-01-01' + INTERVAL '2' YEAR ---- 2019-12-31 query T SELECT * FROM dateish WHERE a >= DATE '1999-12-31' + INTERVAL '2' DAY ---- 2019-12-31 query T SELECT * FROM dateish WHERE a != DATE '1999-12-31' + INTERVAL '1' DAY ---- 2019-12-31 query T rowsort SELECT * FROM dateish WHERE a != DATE '1999-12-31' + INTERVAL '2' DAY ---- 2000-01-01 2019-12-31 # Comparisons with timestamps statement ok CREATE TABLE timestamp_compares ( c timestamp ) statement ok INSERT INTO timestamp_compares VALUES (TIMESTAMP '1999-12-31 01:01:01'), (TIMESTAMP '2009-01-01 09:09:09.9') query T SELECT c FROM timestamp_compares WHERE c = TIMESTAMP '2009-01-01 09:09:09.9' ---- 2009-01-01 09:09:09.9 query T SELECT c FROM timestamp_compares WHERE c = TIMESTAMP '2008-12-31 09:09:09.9' + INTERVAL '1' DAY ---- 2009-01-01 09:09:09.9 query T SELECT c FROM timestamp_compares WHERE c = TIMESTAMP '2008-12-01 09:09:09.9' + INTERVAL '1' MONTH ---- 2009-01-01 09:09:09.9 query T rowsort SELECT c FROM timestamp_compares WHERE c < TIMESTAMP '2008-12-31 09:09:09.9' + INTERVAL '2' MONTH ---- 1999-12-31 01:01:01 2009-01-01 09:09:09.9 # some inverses query T SELECT c FROM timestamp_compares WHERE c != TIMESTAMP '2009-01-01 09:09:09.9' ---- 1999-12-31 01:01:01 query T SELECT c FROM timestamp_compares WHERE c != TIMESTAMP '2008-12-31 09:09:09.9' + INTERVAL '1' DAY ---- 1999-12-31 01:01:01 query T SELECT c FROM timestamp_compares WHERE c != TIMESTAMP '2008-12-01 09:09:09.9' + INTERVAL '1' MONTH ---- 1999-12-31 01:01:01 query T rowsort SELECT c FROM timestamp_compares WHERE c > TIMESTAMP '2008-12-31 09:09:09.9' + INTERVAL '2' MONTH ---- statement ok CREATE TABLE timestampwithtzish ( t timestamp ) statement ok INSERT INTO timestampwithtzish VALUES (TIMESTAMP WITH TIME ZONE '1999-12-31 11:11:01+04:00'), (TIMESTAMP WITH TIME ZONE '1999-12-31 16:16:01+02:30'), (TIMESTAMP WITH TIME ZONE '1999-12-31 20:00:00-12'), (TIMESTAMP WITH TIME ZONE '1999-12-31 18:00:00+12') query T rowsort SELECT * FROM timestampwithtzish ---- 1999-12-31 06:00:00 1999-12-31 07:11:01 1999-12-31 13:46:01 2000-01-01 08:00:00 statement ok CREATE TABLE timestamptzish ( t timestamptz ) statement ok INSERT INTO timestamptzish VALUES (TIMESTAMPTZ '1999-12-31 11:11:01+04:00'), (TIMESTAMPTZ '1999-12-31 16:16:01+02:30'), (TIMESTAMPTZ '1999-12-31 20:00:00-12'), (TIMESTAMPTZ '1999-12-31 18:00:00+12') query T rowsort SELECT * FROM timestamptzish ---- 1999-12-31 06:00:00+00 1999-12-31 07:11:01+00 1999-12-31 13:46:01+00 2000-01-01 08:00:00+00 query T SELECT max(t) FROM timestamptzish ---- 2000-01-01 08:00:00+00 query T SELECT min(t) FROM timestamptzish ---- 1999-12-31 06:00:00+00 # Timestamptz arithmetic with month intervals. Should behave the same as DATE and timestamp query T SELECT TIMESTAMPTZ '2000-01-01 00:00:00-6' + INTERVAL '1' YEAR ---- 2001-01-01 06:00:00+00 query T SELECT TIMESTAMPTZ '2000-01-01 00:00:00-7' - INTERVAL '1' YEAR ---- 1999-01-01 07:00:00+00 query T SELECT TIMESTAMPTZ '2000-01-01 00:00:00-6' + INTERVAL '3' MONTH ---- 2000-04-01 06:00:00+00 query T SELECT TIMESTAMPTZ '2000-01-01 00:00:00-7' - INTERVAL '4' MONTH ---- 1999-09-01 07:00:00+00 query error operator does not exist: interval \- timestamp with time zone SELECT INTERVAL '1' YEAR - TIMESTAMPTZ '2000-01-01 00:00:00-4:00' # Timestamptz arithmetic with duration intervals. query T SELECT TIMESTAMPTZ '2000-01-01 00:00:00-04' + INTERVAL '7' HOUR ---- 2000-01-01 11:00:00+00 query T SELECT TIMESTAMPTZ '2000-01-01 00:00:00-04' + INTERVAL '3' MINUTE ---- 2000-01-01 04:03:00+00 query T SELECT INTERVAL '6' HOUR + TIMESTAMPTZ '2000-01-01 00:00:00-04' ---- 2000-01-01 10:00:00+00 query T SELECT TIMESTAMPTZ '2000-01-01 00:00:00-04' - INTERVAL '2' HOUR ---- 2000-01-01 02:00:00+00 query error operator does not exist: interval \- timestamp with time zone SELECT INTERVAL '2' HOUR - TIMESTAMPTZ '2000-01-01 00:00:00-04' query error operator does not exist: timestamp with time zone \* interval SELECT TIMESTAMPTZ '2000-01-01 00:00:00-04' * INTERVAL '2' HOUR query error operator does not exist: timestamp with time zone / interval SELECT TIMESTAMPTZ '2000-01-01 00:00:00-04' / INTERVAL '2' HOUR query error operator does not exist: timestamp with time zone \+ timestamp with time zone SELECT TIMESTAMPTZ '2000-01-01 00:00:00-04' + TIMESTAMPTZ '1999-01-01 00:00:00z' query error operator does not exist: timestamp with time zone \+ timestamp SELECT TIMESTAMPTZ '2000-01-01 00:00:00-04' + TIMESTAMP '1999-01-01 00:00:00' # Tests with comparison operators and timestamptz query B SELECT TIMESTAMPTZ '2000-01-01 00:00:00+01' < TIMESTAMPTZ '2000-01-01 00:00:00-04' ---- true query B SELECT TIMESTAMPTZ '2000-01-01 00:00:00+01' > TIMESTAMPTZ '2000-01-01 00:00:00-04' ---- false query B SELECT TIMESTAMPTZ '2000-01-01 00:00:00+01' <= TIMESTAMPTZ '2000-01-01 00:00:00-04' ---- true query B SELECT TIMESTAMPTZ '2000-01-01 00:00:00+01' >= TIMESTAMPTZ '2000-01-01 00:00:00-04' ---- false query B SELECT TIMESTAMPTZ '2000-01-01 02:00:00+01' = TIMESTAMPTZ '2000-01-01 01:00:00z' ---- true query B SELECT TIMESTAMPTZ '2000-01-01 02:00:00+01' >= TIMESTAMPTZ '2000-01-01 01:00:00z' ---- true query B SELECT TIMESTAMPTZ '2000-01-01 02:00:00+01' <= TIMESTAMPTZ '2000-01-01 01:00:00z' ---- true query B SELECT TIMESTAMPTZ '2000-01-01 02:00:00+01' != TIMESTAMPTZ '2000-01-01 01:00:00z' ---- false # Tests with comparison operators across different time types query B SELECT TIMESTAMP '2000-01-01 00:00:00' > DATE '2000-01-01' ---- false query B SELECT TIMESTAMPTZ '2000-01-01 00:00:00+4' > DATE '2000-01-01' ---- false query B SELECT DATE '2001-01-01' > TIMESTAMPTZ '2000-01-01 00:00:00+4' ---- true query B SELECT TIMESTAMPTZ '2000-01-01 00:00:00+4' > TIMESTAMP '2000-01-01 01:00:00' ---- false query B SELECT TIMESTAMP '2000-01-01 00:00:00' > TIMESTAMPTZ '2000-01-01 01:00:00+04' ---- true # Timestamptz comparisons after interval math query B SELECT TIMESTAMPTZ '2000-01-01 01:00:00+01' < TIMESTAMPTZ '2000-01-01 00:01:00z' + INTERVAL '1' HOUR ---- true query B SELECT TIMESTAMPTZ '2000-01-01 01:00:00+01' <= TIMESTAMPTZ '2000-01-01 00:01:00z' + INTERVAL '1' HOUR ---- true query B SELECT TIMESTAMPTZ '2000-01-01 01:00:00+01' > TIMESTAMPTZ '2000-01-01 00:01:00z' + INTERVAL '1' HOUR ---- false query B SELECT TIMESTAMPTZ '2000-01-01 01:00:00+01' >= TIMESTAMPTZ '2000-01-01 00:01:00z' + INTERVAL '1' HOUR ---- false query B SELECT TIMESTAMPTZ '2000-01-01 01:00:00+01' + INTERVAL '3' HOUR > TIMESTAMPTZ '2000-01-01 00:01:00z' + INTERVAL '1' HOUR ---- true query B SELECT TIMESTAMPTZ '2000-01-01 01:00:00+01' + INTERVAL '1' MINUTE != TIMESTAMPTZ '2000-01-01 00:01:00+01' + INTERVAL '1' HOUR ---- false # comparisons with timestamptz query T SELECT t FROM timestamptzish WHERE t = TIMESTAMPTZ '1999-12-31 13:46:01z' ---- 1999-12-31 13:46:01+00 query T SELECT t FROM timestamptzish WHERE t = TIMESTAMP '1999-12-31 13:46:01' ---- 1999-12-31 13:46:01+00 query T SELECT t FROM timestamptzish WHERE t = TIMESTAMPTZ '1999-12-31 9:46:01-04' ---- 1999-12-31 13:46:01+00 query T SELECT t FROM timestamptzish WHERE t > TIMESTAMPTZ '1999-12-31 9:46:01-04' ---- 2000-01-01 08:00:00+00 query T rowsort SELECT t FROM timestamptzish WHERE t >= TIMESTAMPTZ '1999-12-31 9:46:01-04' ---- 1999-12-31 13:46:01+00 2000-01-01 08:00:00+00 query T rowsort SELECT t FROM timestamptzish WHERE t < TIMESTAMPTZ '1999-12-31 9:46:01-04' ---- 1999-12-31 06:00:00+00 1999-12-31 07:11:01+00 query T rowsort SELECT t FROM timestamptzish WHERE t <= TIMESTAMPTZ '1999-12-31 9:46:01-04' ---- 1999-12-31 06:00:00+00 1999-12-31 07:11:01+00 1999-12-31 13:46:01+00 query T rowsort SELECT t FROM timestamptzish WHERE t > TIMESTAMPTZ '1999-12-31 9:46:01-04' - INTERVAL '12' HOUR ---- 1999-12-31 06:00:00+00 1999-12-31 07:11:01+00 1999-12-31 13:46:01+00 2000-01-01 08:00:00+00 # Tests now() and current_timestamp() query B SELECT now() > timestamp '2015-06-13 00:00:00' ---- true query B SELECT now() + INTERVAL '100' HOUR > now() ---- true query B SELECT current_timestamp() > TIMESTAMP '2016-06-13 00:00:00' ---- true query B SELECT current_timestamp > TIMESTAMP '2016-06-13 00:00:00' ---- true statement ok CREATE VIEW logical_timestamp_view(ts) AS SELECT mz_now() # Equivalent to running `SELECT mz_now()` directly. If there are # other objects in the same time domain as the view, they will dictate what is # returned. Otherwise it defaults to the epoch millisecond timeline. query T SELECT ts < 18446744073709551615 FROM logical_timestamp_view ---- true statement ok CREATE VIEW now_view AS SELECT now() AS ts query B SELECT ts > TIMESTAMP '2016-06-13 00:00:00' FROM now_view ---- true query T SELECT (DATE '2000-01-01')::text ---- 2000-01-01 query T SELECT (TIMESTAMP '2000-01-01 00:00:00')::text ---- 2000-01-01 00:00:00 query T SELECT (TIMESTAMPTZ '2000-01-01 00:00:00-6')::text ---- 2000-01-01 06:00:00+00 query T SELECT (INTERVAL '1-3' YEAR TO MONTH)::text ---- 1 year 3 months query RRRRR SELECT EXTRACT(HOUR from TIME '11:12:42.666'), EXTRACT(MINUTE from TIME '11:12:42.666'), EXTRACT(SECOND from TIME '11:12:42.666'), EXTRACT(MILLISECONDS from TIME '11:12:42.666'), EXTRACT(MICROSECONDS from TIME '11:12:42.666') ---- 11 12 42.666 42666 42666000 query RRRRR SELECT date_part('HOUR', TIME '11:12:42.666'), date_part('MINUTE', TIME '11:12:42.666'), date_part('SECOND', TIME '11:12:42.666'), date_part('MILLISECONDS', TIME '11:12:42.666'), date_part('MICROSECONDS', TIME '11:12:42.666') ---- 11 12 42.666 42666 42666000 query error unit 'millennium' not supported for type time SELECT EXTRACT(MILLENNIUM from TIME '11:12:42.666') query error unit 'century' not supported for type time SELECT EXTRACT(CENTURY from TIME '11:12:42.666') query error unit 'decade' not supported for type time SELECT EXTRACT(DECADE from TIME '11:12:42.666') query error unit 'year' not supported for type time SELECT EXTRACT(YEAR from TIME '11:12:42.666') query error unit 'quarter' not supported for type time SELECT EXTRACT(QUARTER from TIME '11:12:42.666') query error unit 'month' not supported for type time SELECT EXTRACT(MONTH from TIME '11:12:42.666') query error unit 'week' not supported for type time SELECT EXTRACT(WEEK from TIME '11:12:42.666') query error unit 'day' not supported for type time SELECT EXTRACT(DAY from TIME '11:12:42.666') query error unit 'dow' not supported for type time SELECT EXTRACT(DOW from TIME '11:12:42.666') query error unit 'doy' not supported for type time SELECT EXTRACT(DOY from TIME '11:12:42.666') query error unit 'isodow' not supported for type time SELECT EXTRACT(ISODOW from TIME '11:12:42.666') query error unit 'isodoy' not supported for type time SELECT EXTRACT(ISODOY from TIME '11:12:42.666') query error unit 'month' not supported for type time SELECT date_part('MONTH', TIME '11:12:42.666') query RR SELECT EXTRACT(DOW FROM TIMESTAMP '1999-12-26 00:00:00'), EXTRACT(DOW FROM TIMESTAMP '2000-01-01 00:00:00') ---- 0 6 query RR SELECT EXTRACT(ISODOW FROM TIMESTAMP '1999-12-26 00:00:00'), EXTRACT(ISODOW FROM TIMESTAMP '2000-01-01 00:00:00') ---- 7 6 query RRRRRRRRRRRRRRRRR SELECT EXTRACT(EPOCH FROM TIMESTAMP '2019-11-26 15:56:46.241150'), EXTRACT(MILLENNIUM FROM TIMESTAMP '2019-11-26 15:56:46.241150'), EXTRACT(CENTURY FROM TIMESTAMP '2019-11-26 15:56:46.241150'), EXTRACT(DECADE FROM TIMESTAMP '2019-11-26 15:56:46.241150'), EXTRACT(YEAR FROM TIMESTAMP '2019-11-26 15:56:46.241150'), EXTRACT(QUARTER FROM TIMESTAMP '2019-11-26 15:56:46.241150'), EXTRACT(WEEK FROM TIMESTAMP '2019-11-26 15:56:46.241150'), EXTRACT(MONTH FROM TIMESTAMP '2019-11-26 15:56:46.241150'), EXTRACT(HOUR FROM TIMESTAMP '2019-11-26 15:56:46.241150'), EXTRACT(DAY FROM TIMESTAMP '2019-11-26 15:56:46.241150'), EXTRACT(DOW FROM TIMESTAMP '2019-11-26 15:56:46.241150'), EXTRACT(DOY FROM TIMESTAMP '2019-11-26 15:56:46.241150'), EXTRACT(ISODOW FROM TIMESTAMP '2019-11-26 15:56:46.241150'), EXTRACT(MINUTE FROM TIMESTAMP '2019-11-26 15:56:46.241150'), EXTRACT(SECOND FROM TIMESTAMP '2019-11-26 15:56:46.241150'), EXTRACT(MS FROM TIMESTAMP '2019-11-26 15:56:46.241150'), EXTRACT(US FROM TIMESTAMP '2019-11-26 15:56:46.241150') ---- 1574783806.24115 3 21 201 2019 4 48 11 15 26 2 330 2 56 46.24115 46241.15 46241150 query RRRRRRRRRRRRRRRRR SELECT date_part('EPOCH', TIMESTAMP '2019-11-26 15:56:46.241150'), date_part('MILLENNIUM', TIMESTAMP '2019-11-26 15:56:46.241150'), date_part('CENTURY', TIMESTAMP '2019-11-26 15:56:46.241150'), date_part('DECADE', TIMESTAMP '2019-11-26 15:56:46.241150'), date_part('YEAR', TIMESTAMP '2019-11-26 15:56:46.241150'), date_part('QUARTER', TIMESTAMP '2019-11-26 15:56:46.241150'), date_part('WEEK', TIMESTAMP '2019-11-26 15:56:46.241150'), date_part('MONTH', TIMESTAMP '2019-11-26 15:56:46.241150'), date_part('HOUR', TIMESTAMP '2019-11-26 15:56:46.241150'), date_part('DAY', TIMESTAMP '2019-11-26 15:56:46.241150'), date_part('DOW', TIMESTAMP '2019-11-26 15:56:46.241150'), date_part('DOY', TIMESTAMP '2019-11-26 15:56:46.241150'), date_part('ISODOW', TIMESTAMP '2019-11-26 15:56:46.241150'), date_part('MINUTE', TIMESTAMP '2019-11-26 15:56:46.241150'), date_part('SECOND', TIMESTAMP '2019-11-26 15:56:46.241150'), date_part('MS', TIMESTAMP '2019-11-26 15:56:46.241150'), date_part('US', TIMESTAMP '2019-11-26 15:56:46.241150') ---- 1574783806.24115 3 21 201 2019 4 48 11 15 26 2 330 2 56 46.24115 46241.15 46241150 query RRRRRRRRRRRR SELECT EXTRACT(QUARTER FROM DATE '2000-01-01'), EXTRACT(QUARTER FROM DATE '2000-02-03'), EXTRACT(QUARTER FROM DATE '2000-03-05'), EXTRACT(QUARTER FROM DATE '2000-04-07'), EXTRACT(QUARTER FROM DATE '2000-05-09'), EXTRACT(QUARTER FROM DATE '2000-06-11'), EXTRACT(QUARTER FROM DATE '2000-07-13'), EXTRACT(QUARTER FROM DATE '2000-08-15'), EXTRACT(QUARTER FROM DATE '2000-09-17'), EXTRACT(QUARTER FROM DATE '2000-10-19'), EXTRACT(QUARTER FROM DATE '2000-11-21'), EXTRACT(QUARTER FROM DATE '2000-12-24') ---- 1 1 1 2 2 2 3 3 3 4 4 4 query RRRRRRRR SELECT EXTRACT(MILLENNIUM FROM DATE '2001-01-01'), EXTRACT(MILLENNIUM FROM DATE '2000-01-01'), EXTRACT(MILLENNIUM FROM DATE '1999-01-01'), EXTRACT(MILLENNIUM FROM DATE '1001-01-01'), EXTRACT(MILLENNIUM FROM DATE '1000-01-01'), EXTRACT(MILLENNIUM FROM DATE '0001-01-01'), EXTRACT(MILLENNIUM FROM DATE '0001-01-01' - INTERVAL '1'SECOND), EXTRACT(MILLENNIUM FROM DATE '0001-01-01' - INTERVAL '1000 YEAR 1 SECOND') ---- 3 2 2 2 1 1 -1 -2 query RRRRRRRR SELECT EXTRACT(CENTURY FROM DATE '2001-01-01'), EXTRACT(CENTURY FROM DATE '2000-01-01'), EXTRACT(CENTURY FROM DATE '1999-01-01'), EXTRACT(CENTURY FROM DATE '1001-01-01'), EXTRACT(CENTURY FROM DATE '1000-01-01'), EXTRACT(CENTURY FROM DATE '0001-01-01'), EXTRACT(CENTURY FROM DATE '0001-01-01' - INTERVAL '1'SECOND), EXTRACT(CENTURY FROM DATE '0001-01-01' - INTERVAL '100 YEAR 1 SECOND') ---- 21 20 20 11 10 1 -1 -2 query RRRRRRRR SELECT EXTRACT(DECADE FROM DATE '2001-01-01'), EXTRACT(DECADE FROM DATE '2000-01-01'), EXTRACT(DECADE FROM DATE '1999-01-01'), EXTRACT(DECADE FROM DATE '0001-01-01'), EXTRACT(DECADE FROM DATE '0001-01-01' - INTERVAL '1'SECOND), EXTRACT(DECADE FROM DATE '0001-01-01' - INTERVAL '1 YEAR 1 SECOND'), EXTRACT(DECADE FROM DATE '0001-01-01' - INTERVAL '10 YEAR 1 SECOND'), EXTRACT(DECADE FROM DATE '0001-01-01' - INTERVAL '11 YEAR 1 SECOND') ---- 200 200 199 0 0 -1 -1 -2 query RR SELECT EXTRACT(WEEK FROM DATE '2000-01-01'), EXTRACT(WEEK FROM DATE '2000-01-08') ---- 52 1 query RR SELECT EXTRACT(DOY FROM DATE '2000-01-01'), EXTRACT(DOY FROM DATE '2000-12-31') ---- 1 366 query error unit 'hour' not supported for type date SELECT EXTRACT(HOUR FROM DATE '2000-12-31') query error unit 'seconds' not supported for type date SELECT EXTRACT(SECOND FROM DATE '2000-12-31') query error unit 'milliseconds' not supported for type date SELECT EXTRACT(MILLISECOND FROM DATE '2000-12-31') query error unit 'microseconds' not supported for type date SELECT EXTRACT(MICROSECOND FROM DATE '2000-12-31') query RRRR SELECT date_part('HOUR', DATE '2000-12-31'), date_part('SECOND', DATE '2000-12-31'), date_part('MILLISECOND', DATE '2000-12-31'), date_part('MICROSECOND', DATE '2000-12-31') ---- 0 0 0 0 query RR SELECT EXTRACT(EPOCH from INTERVAL '-1' MINUTE), EXTRACT(MINUTE from INTERVAL '-1' MINUTE) ---- -60 -1 query RR SELECT EXTRACT(EPOCH from INTERVAL '1' YEAR), EXTRACT(EPOCH from INTERVAL '1' MONTH) * 12 ---- 31557600 31104000 query RR SELECT EXTRACT(MILLISECOND from INTERVAL '72.345678'SECOND), EXTRACT(MICROSECOND from INTERVAL '72.345678'SECOND) ---- 12345.678 12345678 query RRR SELECT EXTRACT(DECADE from INTERVAL '39'YEAR), EXTRACT(CENTURY from INTERVAL '399'YEAR), EXTRACT(MILLENNIUM from INTERVAL '3999'YEAR) ---- 3 3 3 query RR SELECT EXTRACT(MONTH from INTERVAL '-13'MONTH), EXTRACT(MONTH from INTERVAL '15'MONTH) ---- -1 3 query RRRRRRRRRRR SELECT date_part('EPOCH', INTERVAL '-1' MINUTE), date_part('MINUTE', INTERVAL '-1' MINUTE), date_part('EPOCH', INTERVAL '1' YEAR), date_part('EPOCH', INTERVAL '1' MONTH) * 12, date_part('MILLISECOND', INTERVAL '72.345678'SECOND), date_part('MICROSECOND', INTERVAL '72.345678'SECOND), date_part('DECADE', INTERVAL '39'YEAR), date_part('CENTURY', INTERVAL '399'YEAR), date_part('MILLENNIUM', INTERVAL '3999'YEAR), date_part('MONTH', INTERVAL '-13'MONTH), date_part('MONTH', INTERVAL '15'MONTH) ---- -60 -1 31557600 31104000 12345.678 12345678 3 3 3 -1 3 query T SELECT to_char(TIMESTAMPTZ '1997-02-03 11:12:59.9', 'YYYY-MM-DD HH24:MI:SS.MS TZ') ---- 1997-02-03 11:12:59.900 UTC # Test that fractional milliseconds are not rounded, which matches Postgres's # behavior. query T SELECT to_char(TIMESTAMPTZ '1997-02-03 11:12:59.7777', 'YYYY-MM-DD HH24:MI:SS.MS TZ') ---- 1997-02-03 11:12:59.777 UTC # Test a degenerate pattern that contains no field specifiers. query T SELECT to_char(TIMESTAMP '2000-01-01', 'no patterns at all') ---- no patterns at all query T SELECT to_timestamp(-1) ---- 1969-12-31 23:59:59+00 query T SELECT to_timestamp(0) ---- 1970-01-01 00:00:00+00 query T SELECT to_timestamp(946684800) ---- 2000-01-01 00:00:00+00 query T SELECT to_timestamp(1262349296.7890123) ---- 2010-01-01 12:34:56.789012+00 query T SELECT to_timestamp(1.999999999) ---- 1970-01-01 00:00:02+00 query error timestamp out of range SELECT to_timestamp('inf'::double) query error timestamp cannot be NaN SELECT to_timestamp('nan'::double) # Negative timestamps are not allowed statement error SELECT TIMESTAMP '-2000-01-01 1:2:3'; statement error SELECT DATE '-2000-01-01'; statement error SELECT TIMESTAMP '-2000-01 1:2:3'; statement error SELECT DATE '-2000-01 1:2:3'; # Test string to datetime types query T SELECT '2007-02-01'::date ---- 2007-02-01 query T SELECT '-1-2 3 -4:5:6.7'::interval; ---- -1 years -2 months +3 days -04:05:06.7 query T SELECT '01:23:45'::time; ---- 01:23:45 query T SELECT '2007-02-01 15:04:05'::timestamp; ---- 2007-02-01 15:04:05 query T SELECT '2007-02-01 15:04:05+06'::timestamptz; ---- 2007-02-01 09:04:05+00 # Test datetime types to string query T SELECT (date '2007-02-01')::text ---- 2007-02-01 query T SELECT (interval '-1-2 3 -4:5:6.7')::text; ---- -1 years -2 months +3 days -04:05:06.7 query T SELECT (time '01:23:45')::text; ---- 01:23:45 query T SELECT (timestamp '2007-02-01 15:04:05')::text; ---- 2007-02-01 15:04:05 query T SELECT (timestamptz '2007-02-01 15:04:05+06')::text; ---- 2007-02-01 09:04:05+00 # Test special date-timme inputs from Postgres query T SELECT 'epoch'::timestamp ---- 1970-01-01 00:00:00 query T SELECT 'epoch'::timestamptz ---- 1970-01-01 00:00:00+00 query T SELECT 'epoch'::date ---- 1970-01-01 # Test ISO-formatted timestamps query T SELECT TIMESTAMP '2007-02-01T15:04:05' ---- 2007-02-01 15:04:05 query T SELECT TIMESTAMPTZ '2007-02-01T15:04:05+00' ---- 2007-02-01 15:04:05+00 query T SELECT TIMESTAMPTZ '20070201 T 15:04:05+00'; ---- 2007-02-01 15:04:05+00 query T SELECT TIMESTAMPTZ '20070201T15:04:05+00'; ---- 2007-02-01 15:04:05+00 query T SELECT DATE '2007-02-01T15:04:05+00' ---- 2007-02-01 query T SELECT DATE '2007-02-01 T 15:04:05+00' ---- 2007-02-01 query T SELECT DATE '2007-02-01 T 15:04:05+00' ---- 2007-02-01 statement error invalid input syntax for type date: unknown units X: "2007-02-01X15:04:05" SELECT DATE '2007-02-01X15:04:05' statement error invalid input syntax for type date: unknown units TT: "2007-02-01TT15:04:05" SELECT DATE '2007-02-01TT15:04:05' statement error invalid input syntax for type date: Cannot determine format of all parts: "2007-02-01 T T 15:04:05" SELECT DATE '2007-02-01 T T 15:04:05' statement error invalid input syntax for type date: Invalid timezone string \(T\): 'T' is not a valid timezone. Failed to parse T at token index 0 SELECT DATE '2007-02-01 T ' # Test casting time to interval & vice versa query T SELECT time '01:02:03.04'::interval; ---- 01:02:03.04 query T SELECT interval '01:02:03.04'::time; ---- 01:02:03.04 query T SELECT interval '-01:02:03.04'::time; ---- 22:57:56.96 query T SELECT interval '-3 days -2 hours'::time; ---- 22:00:00 # Test using date as a column name. query T SELECT date FROM (SELECT column1 AS date FROM (VALUES ('2020-01-01'))) ---- 2020-01-01 # Arbitrary punctuation as delimiter query T SELECT '"2020-03-17 ~02:36:56~"'::timestamp; ---- 2020-03-17 02:36:56 query T SELECT '"2020!03-17 #?~T~02:36:56#"'::timestamp; ---- 2020-03-17 02:36:56 query T SELECT '"2020!03-17 #?~T~02:36:56#+00"'::timestamp; ---- 2020-03-17 02:36:56 query error invalid input syntax for type timestamp: have unprocessed tokens 56 select TIMESTAMP '"2020-03-17 ~02:36:~56~"'; query T SELECT '"2020!03-17 #?~T~02:36:56#+00~"'::timestamp with time zone; ---- 2020-03-17 02:36:56+00 query T SELECT '"2020!03-17 #?~T~02:36:56# + 00 '::timestamp with time zone; ---- 2020-03-17 02:36:56+00 query T SELECT '"2020!03-17 #?~T~02:36:56# + 00 ? '::timestamp with time zone; ---- 2020-03-17 02:36:56+00 query T SELECT '"2020!03-17 #?~T~02:36:56# # + 00 ? '::timestamp with time zone; ---- 2020-03-17 02:36:56+00 query T SELECT '"2020!03-17 #?~T~02:36:56# # + 00 ?#'::timestamp with time zone; ---- 2020-03-17 02:36:56+00 query T SELECT '\"\"2024-02-13 17:01:58.37848+00\"\"\"'::timestamp with time zone; ---- 2024-02-13 17:01:58.37848+00 # : is not prohibited query T SELECT '"?!?2024-02-13 17:01:58.37848 ?+ 00 : '::timestamp with time zone; ---- 2024-02-13 17:01:58.37848+00 # + is prohibited after numerals query error invalid input syntax for type timestamp with time zone: have unprocessed tokens \+ 0: "\\"\?!\?2024\-02\-13 17:01:58\.37848 \?\+ 00 \+ " SELECT '"?!?2024-02-13 17:01:58.37848 ?+ 00 + '::timestamp with time zone; # - is prohibited after numerals query error invalid input syntax for type timestamp with time zone: have unprocessed tokens \+ 0: "\\"\?!\?2024\-02\-13 17:01:58\.37848 \?\+ 00 \- " SELECT '"?!?2024-02-13 17:01:58.37848 ?+ 00 - '::timestamp with time zone; # no space between numerals query error invalid input syntax for type timestamp with time zone: Cannot parse timezone offset \+ 0 0: "\\"\?!\?2024\-02\-13 17:01:58\.37848\+ 0 0" SELECT '"?!?2024-02-13 17:01:58.37848+ 0 0'::timestamp with time zone; # no non-space characters between + or - and the numerals query error invalid input syntax for type timestamp with time zone: Cannot parse timezone offset \+ \? 00: "\\"\?!\?2024\-02\-13 17:01:58\.37848\+ \? 00" SELECT '"?!?2024-02-13 17:01:58.37848+ ? 00'::timestamp with time zone; # Regression for database-issues#1933. These match postgres. query TTT select '9::60'::time, '9:59:60'::time, '9::59.999999'::time ---- 09:01:00 10:00:00 09:00:59.999999 # TODO: Postgres returns 09:01:00 for this. query T select '9::59.999999999'::time ---- 09:00:59.999999 # TODO: Postgres supports this as 09:01:00.1. statement error invalid input syntax for type time: NANOSECOND select '9::60.1'::time # BC years are properly formatted # Using INTERVAL to work around the parser not supporting BC identifiers yet query T select '0001-02-24'::date - interval '1 YEAR' ---- 0001-02-24 00:00:00 BC query T select ('0001-02-24'::date - interval '1 YEAR')::date ---- 0001-02-24 BC query T select ('0001-01-01'::date - interval '1 DAY')::date ---- 0001-12-31 BC query error "-2147483648" interval out of range select '0001-02-24'::date - interval '-2147483648 MONTHS' query T select '0001-02-24 03:04:05'::timestamp - interval '1 YEAR' ---- 0001-02-24 03:04:05 BC query T select '0001-02-24 03:04:05.6789'::timestamp - interval '1 YEAR' ---- 0001-02-24 03:04:05.6789 BC query T select '0001-02-24 03:04:05.6789 +00:00'::timestamptz - interval '1 YEAR' ---- 0001-02-24 03:04:05.6789+00 BC query error "-178956970 years -8 months" interval out of range select '0001-02-24 03:04:05.6789'::timestamp - interval '-2147483648 MONTHS' query error "-178956970 years -8 months" interval out of range select '0001-02-24 03:04:05.6789 +00:00'::timestamptz - interval '-2147483648 MONTHS' # Infinity dates not supported. query error invalid input syntax for type date select 'infinity'::date # We don't support BC parsing or 6 digit dates, so do some jank. Additionally, # we don't yet support the `date - int` operation and have to use `date - # interval` instead, which produces a `timestamp`, and thus we aren't able to even # express the full range of date values. # Lowest date we could support, but timestamps don't. # select ('0001-01-01'::date - '4713years 1months 7days')::date query T select ('0001-01-01'::date - '1721389days'::interval)::date ---- 4714-12-31 BC query T select ('0001-01-01'::date + '262141years 11months 30days'::interval)::date ---- 262142-12-31 # Out of range for both dates and timestamps, but timestamp triggers first. query error timestamp out of range select ('0001-01-01'::date - '4713years 1months 8days')::date query error timestamp out of range select ('0001-01-01'::date + '262141years 11months 30days')::date + '1day' query II select ('0001-01-01'::date - '1721389days'::interval)::date - ('0001-01-01'::date + '262141years 11months 30days'::interval)::date, ('0001-01-01'::date + '262141years 11months 30days'::interval)::date - ('0001-01-01'::date - '1721389days'::interval)::date ---- -97466787 97466787 query error timestamp out of range SELECT to_timestamp(9223372036854775808::float8); query error timestamp out of range SELECT to_timestamp(-9223372036854775809::float8); # Negative fractional timestamp query T SELECT to_timestamp(-0.1::float8); ---- 1969-12-31 23:59:59.9+00 # Regression test for https://github.com/MaterializeInc/database-issues/issues/6002 query T SELECT now() + null; ---- NULL # Test timestamp precision. query error precision for type timestamp or timestamptz must be between 0 and 6 SELECT '1970-01-01T00:00:00.666666'::timestamp(-1); query error precision for type timestamp or timestamptz must be between 0 and 6 SELECT '1970-01-01T00:00:00.666666'::timestamp(7); query T SELECT '1970-01-01T00:00:00.666666666'::timestamp(0); ---- 1970-01-01 00:00:01 query T SELECT '1970-01-01T00:00:00.666666666'::timestamp(1); ---- 1970-01-01 00:00:00.7 query T SELECT '1970-01-01T00:00:00.666666666'::timestamp(2); ---- 1970-01-01 00:00:00.67 query T SELECT '1970-01-01T00:00:00.666666666'::timestamp(3); ---- 1970-01-01 00:00:00.667 query T SELECT '1970-01-01T00:00:00.666666666'::timestamp(4); ---- 1970-01-01 00:00:00.6667 query T SELECT '1970-01-01T00:00:00.666666666'::timestamp(5); ---- 1970-01-01 00:00:00.66667 query T SELECT '1970-01-01T00:00:00.666666666'::timestamp(6); ---- 1970-01-01 00:00:00.666667 query T SELECT '1970-01-01T00:00:00.666666666'::timestamptz(0); ---- 1970-01-01 00:00:01+00 query T SELECT '1970-01-01T00:00:00.666666666'::timestamptz(1); ---- 1970-01-01 00:00:00.7+00 query T SELECT '1970-01-01T00:00:00.666666666'::timestamptz(2); ---- 1970-01-01 00:00:00.67+00 query T SELECT '1970-01-01T00:00:00.666666666'::timestamptz(3); ---- 1970-01-01 00:00:00.667+00 query T SELECT '1970-01-01T00:00:00.666666666'::timestamptz(4); ---- 1970-01-01 00:00:00.6667+00 query T SELECT '1970-01-01T00:00:00.666666666'::timestamptz(5); ---- 1970-01-01 00:00:00.66667+00 query T SELECT '1970-01-01T00:00:00.666666666'::timestamptz(6); ---- 1970-01-01 00:00:00.666667+00 query T SELECT '1970-01-01T00:00:00.666666666'::timestamp::timestamp(3); ---- 1970-01-01 00:00:00.667 query T SELECT '1970-01-01T00:00:00.666666666'::timestamptz::timestamptz(3); ---- 1970-01-01 00:00:00.667+00 query T SELECT '1970-01-01T00:00:00.666666666'::timestamp::timestamptz(3); ---- 1970-01-01 00:00:00.667+00 query T SELECT '1970-01-01T00:00:00.666666666'::timestamptz::timestamp(3); ---- 1970-01-01 00:00:00.667 query T SELECT '1970-01-01T00:00:00.666666666'::date::timestamptz(3); ---- 1970-01-01 00:00:00+00 query T SELECT '1970-01-01T00:00:00.666666666'::date::timestamp(3); ---- 1970-01-01 00:00:00 query T SELECT '1970-01-01T00:00:00.123456789'::timestamp(6) - '1970-01-01T00:00:00.123456789'::timestamp(3); ---- 00:00:00.000457 query T SELECT '1970-01-01T00:00:00.123456789'::timestamptz(6) - '1970-01-01T00:00:00.123456789'::timestamptz(3); ---- 00:00:00.000457 query T SELECT date_bin('5 microseconds'::interval, '1970-01-01T00:00:00.123456789'::timestamp(6), '1970-01-01T00:00:00.123456789'::timestamp(3)); ---- 1970-01-01 00:00:00.123455 query T SELECT date_bin('5 milliseconds'::interval, '1970-01-01T00:00:00.123456789'::timestamptz(6), '1970-01-01T00:00:00.123456789'::timestamptz(3)); ---- 1970-01-01 00:00:00.123+00 query T SELECT age('1970-01-01T00:00:00.123456789'::timestamp(6), '1970-01-01T00:00:00.123456789'::timestamp(3)); ---- 00:00:00.000457 query T SELECT age('1970-01-01T00:00:00.123456789'::timestamptz(3), '1970-01-01T00:00:00.123456789'::timestamptz(0)); ---- 00:00:00.123 query T SELECT timestamptz(0) '95143-12-31 23:59:59.123456789+06'; ---- 95143-12-31 17:59:59+00 query T SELECT timestamp(0) with time zone '95143-12-31 23:59:59.123456789'; ---- 95143-12-31 23:59:59+00 query T SELECT timestamp(0) without time zone '95143-12-31 23:59:59.123456789+06'; ---- 95143-12-31 23:59:59 query T SELECT timestamptz '95141-12-31 23:59:59.123456789+06'; ---- 95141-12-31 17:59:59.123457+00 query T SELECT timestamp(0) '95143-12-31 23:59:59.123456789+06'; ---- 95143-12-31 23:59:59 query T SELECT timestamp '95143-12-31 23:59:59.123456789+06'; ---- 95143-12-31 23:59:59.123457 query B SELECT timestamptz(0) '95143-12-31 23:59:59+06' = timestamptz(0) '95143-12-31 23:59:59.123456789+06'; ---- true query B SELECT timestamptz '95143-12-31 23:59:59.123456789+06' = timestamptz(6) '95143-12-31 23:59:59.123456789+06'; ---- true query B SELECT timestamp(0) '95143-12-31 17:59:59.123+00' = timestamp(0) '95143-12-31 17:59:59.123456789+00'; ---- true query B SELECT timestamp '95143-12-31 17:59:59.123456789+00' = timestamp(6) '95143-12-31 17:59:59.123456789+00'; ---- true query B SELECT timestamp(5) '95143-12-31 17:59:59.1235+00' = timestamp(4) '95143-12-31 17:59:59.123456789+00'; ---- true query error precision for type timestamp or timestamptz must be between 0 and 6 SELECT timestamp(-1) '95143-12-31 23:59:59.123456789+06'; query error precision for type timestamp or timestamptz must be between 0 and 6 SELECT timestamp(-1) with time zone '95143-12-31 23:59:59.123456789+06'; query error precision for type timestamp or timestamptz must be between 0 and 6 SELECT '95143-12-31 23:59:59.123456789+06'::timestamp(-1); query error precision for type timestamp or timestamptz must be between 0 and 6 SELECT timestamptz(-1) '95143-12-31 23:59:59.123456789+06'; # Note: we regressed the error messages for the following # See query error Expected end of statement SELECT timestamp(10000000000000000000) '95143-12-31 23:59:59.123456789+06'; query error Expected end of statement SELECT timestamptz(10000000000000000000) '95143-12-31 23:59:59.123456789+06'; query error Expected end of statement SELECT timestamp(9223372036854775808) '95143-12-31 23:59:59.123456789+06'; # checking we are not converting between the same precision again query T multiline EXPLAIN RAW PLAN FOR SELECT '95143-12-31 23:59:59.123456789+06'::timestamptz(3)::timestamptz(3); ---- Map (text_to_timestamp_with_time_zone("95143-12-31 23:59:59.123456789+06")) Constant - () Target cluster: quickstart EOF query T multiline EXPLAIN RAW PLAN FOR SELECT timestamp(3) '95143-12-31 23:59:59.123456789+06'::timestamp(3); ---- Map (text_to_timestamp("95143-12-31 23:59:59.123456789+06")) Constant - () Target cluster: quickstart EOF query T multiline EXPLAIN RAW PLAN FOR SELECT timestamp(3) '95143-12-31 23:59:59.123456789+06'::timestamp(6); ---- Map (adjust_timestamp_precision(text_to_timestamp("95143-12-31 23:59:59.123456789+06"))) Constant - () Target cluster: quickstart EOF statement ok CREATE table t (timestamp string); statement ok INSERT INTO t VALUES('2012-03-05'); query T SELECT * from t ORDER BY timestamp; ---- 2012-03-05 query T SELECT * from t ORDER BY t.timestamp; ---- 2012-03-05 query T SELECT timestamp::timestamp from t ORDER BY t.timestamp; ---- 2012-03-05 00:00:00