# 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 # bin_date simple conn=mz_system,user=mz_system ALTER SYSTEM SET enable_binary_date_bin = true ---- COMPLETE 0 # case 1: AD dates, origin < input query TTT rowsort SELECT str, "interval", date_trunc(str, ts) = date_bin("interval"::interval, ts, timestamptz '2001-01-01') AS equal FROM ( VALUES ('week', '7d'), ('day', '1d'), ('hour', '1h'), ('minute', '1m'), ('second', '1s') ) intervals (str, interval), (VALUES (timestamptz '2020-02-29 15:44:17.71393+00')) ts (ts); ---- day 1d true hour 1h true week 7d true minute 1m true second 1s true # case 3: AD dates, origin > input query TTT rowsort SELECT str, "interval", date_trunc(str, ts) = date_bin("interval"::interval, ts, timestamptz '2020-03-02') AS equal FROM ( VALUES ('week', '7d'), ('day', '1d'), ('hour', '1h'), ('minute', '1m'), ('second', '1s') ) intervals (str, interval), (VALUES (timestamptz '2020-02-29 15:44:17.71393+00')) ts (ts); ---- day 1d true hour 1h true week 7d true minute 1m true second 1s true # bin timestamps into arbitrary intervals query TT rowsort SELECT "interval", date_bin("interval"::interval, ts, origin) FROM ( VALUES ('15 days'), ('2 hours'), ('1 hour 30 minutes'), ('15 minutes'), ('10 seconds') ) intervals (interval), (VALUES (timestamptz '2020-02-11 15:44:17.71393+00')) ts (ts), (VALUES (timestamptz '2001-01-01')) origin (origin); ---- 15␠days 2020-02-06␠00:00:00+00 2␠hours 2020-02-11␠14:00:00+00 10␠seconds 2020-02-11␠15:44:10+00 15␠minutes 2020-02-11␠15:30:00+00 1␠hour␠30␠minutes 2020-02-11␠15:00:00+00 # shift bins using the origin parameter: query T SELECT date_bin('5 min'::interval, timestamptz '2020-02-01 01:01:01+00', timestamptz '2020-02-01 00:02:30+00'); ---- 2020-02-01 00:57:30+00 # disallow > day intervals query error timestamps cannot be binned into intervals containing months or years SELECT date_bin('5 months'::interval, timestamptz '2020-02-01 01:01:01+00', timestamptz '2001-01-01'); query error timestamps cannot be binned into intervals containing months or years SELECT date_bin('5 years'::interval, timestamptz '2020-02-01 01:01:01+00', timestamptz '2001-01-01'); # disallow zero intervals query error stride must be greater than zero SELECT date_bin('0 days'::interval, timestamptz '1970-01-01 01:00:00+00' , timestamptz '1970-01-01 00:00:00+00'); # disallow negative intervals query error stride must be greater than zero SELECT date_bin('-2 days'::interval, timestamptz '1970-01-01 01:00:00+00' , timestamptz '1970-01-01 00:00:00+00'); # max stride caught query error stride cannot exceed SELECT date_bin('9223372037 s'::interval, timestamptz '2020-02-01 01:01:01+00', timestamptz '2020-02-01 00:02:30+00'); # max diff caught query error source and origin must not differ more than 2\^63 nanoseconds SELECT date_bin('1m', timestamptz '2262-04-11 23:47:17+00'); # Test unintuitive day binning query TT rowsort SELECT "interval", date_bin("interval"::interval, timestamptz '2020-02-27 15:44:17.71393+00', timestamptz '2001-01-01') FROM ( VALUES ('1d'), ('2d'), ('3d'), ('4d'), ('5d'), ('6d'), ('7d'), ('8d'), ('9d') ) intervals ("interval"); ---- 1d 2020-02-27␠00:00:00+00 2d 2020-02-27␠00:00:00+00 3d 2020-02-27␠00:00:00+00 4d 2020-02-27␠00:00:00+00 5d 2020-02-26␠00:00:00+00 6d 2020-02-27␠00:00:00+00 7d 2020-02-24␠00:00:00+00 8d 2020-02-23␠00:00:00+00 9d 2020-02-24␠00:00:00+00 # Test that binary extension behaves as expected query TT rowsort SELECT "interval", date_bin("interval"::interval, ts) = date_bin("interval"::interval, ts, timestamptz '1970-01-01') AS equal FROM ( VALUES ('15 days'), ('2 hours'), ('1 hour 30 minutes'), ('15 minutes'), ('10 seconds') ) intervals (interval), (VALUES (timestamptz '2020-02-29 15:44:17.71393+00')) ts (ts); ---- 15␠days true 2␠hours true 10␠seconds true 15␠minutes true 1␠hour␠30␠minutes true query error timestamp out of range select timezone('1 day'::interval, '1-12-31'::timestamptz+'262141 years'::interval) ### to_char statement ok CREATE TABLE t (ts timestamp with time zone) statement ok INSERT INTO t VALUES ('1997-01-01 00:00:00 GMT'), ('2021-02-09 01:07:12 UTC'), ('2024-03-21 12:09:23 +0000'), ('2060-12-31 23:59:59.999999 -0800') query T rowsort SELECT to_char(ts, 'DAY Day day DY Dy dy MONTH Month month RM MON Mon mon') FROM t ---- SATURDAY Saturday saturday SAT Sat sat JANUARY January january I JAN Jan jan THURSDAY Thursday thursday THU Thu thu MARCH March march III MAR Mar mar TUESDAY Tuesday tuesday TUE Tue tue FEBRUARY February february II FEB Feb feb WEDNESDAY Wednesday wednesday WED Wed wed JANUARY January january I JAN Jan jan query T rowsort SELECT to_char(ts, 'FMDAY FMDay FMday FMMONTH FMMonth FMmonth FMRM') FROM t ---- SATURDAY Saturday saturday JANUARY January january I THURSDAY Thursday thursday MARCH March march III TUESDAY Tuesday tuesday FEBRUARY February february II WEDNESDAY Wednesday wednesday JANUARY January january I query T rowsort SELECT to_char(ts, 'Y,YYY YYYY YYY YY Y CC Q MM WW DDD DD D J') FROM t ---- 1,997 1997 997 97 7 20 1 01 01 001 01 4 2450450 2,021 2021 021 21 1 21 1 02 06 040 09 3 2459255 2,024 2024 024 24 4 21 1 03 12 081 21 5 2460391 2,061 2061 061 61 1 21 1 01 01 001 01 7 2473826 query T rowsort SELECT to_char(ts, 'FMY,YYY FMYYYY FMYYY FMYY FMY FMCC FMQ FMMM FMWW FMDDD FMDD FMD FMJ') FROM t ---- 1,997 1997 997 97 7 20 1 1 1 1 1 4 2450450 2,021 2021 21 21 1 21 1 2 6 40 9 3 2459255 2,024 2024 24 24 4 21 1 3 12 81 21 5 2460391 2,061 2061 61 61 1 21 1 1 1 1 1 7 2473826 query T rowsort SELECT to_char(ts, 'HH HH12 HH24 MI SS SSSS') FROM t ---- 01 01 01 07 12 4032 07 07 07 59 59 28799 12 12 00 00 00 0 12 12 12 09 23 43763 query T rowsort SELECT to_char(ts, '"HH:MI:SS is" HH:MI:SS "\"text between quote marks\""') FROM t ---- HH:MI:SS is 01:07:12 "text between quote marks" HH:MI:SS is 07:59:59 "text between quote marks" HH:MI:SS is 12:00:00 "text between quote marks" HH:MI:SS is 12:09:23 "text between quote marks" query T rowsort SELECT to_char(ts, 'HH24--text--MI--text--SS') FROM t ---- 00--text--00--text--00 01--text--07--text--12 07--text--59--text--59 12--text--09--text--23 query T rowsort SELECT to_char(ts, 'YYYYTH YYYYth Jth') FROM t ---- 1997TH 1997th 2450450th 2021ST 2021st 2459255th 2024TH 2024th 2460391st 2061ST 2061st 2473826th query T rowsort SELECT to_char(ts, 'YYYY A.D. YYYY a.d. YYYY bc HH:MI:SS P.M. HH:MI:SS p.m. HH:MI:SS pm') FROM t ---- 1997 A.D. 1997 a.d. 1997 ad 12:00:00 A.M. 12:00:00 a.m. 12:00:00 am 2021 A.D. 2021 a.d. 2021 ad 01:07:12 A.M. 01:07:12 a.m. 01:07:12 am 2024 A.D. 2024 a.d. 2024 ad 12:09:23 P.M. 12:09:23 p.m. 12:09:23 pm 2061 A.D. 2061 a.d. 2061 ad 07:59:59 A.M. 07:59:59 a.m. 07:59:59 am query T rowsort SELECT to_char(ts, 'IYYY IYY IY I IW IDDD ID') FROM t ---- 1997 997 97 7 01 003 3 2021 021 21 1 06 037 2 2024 024 24 4 12 081 4 2060 060 60 0 53 370 6 query T rowsort SELECT to_char(ts, 'FMIYYY FMIYY FMIY FMI FMIW FMIDDD FMID') FROM t ---- 1997 997 97 7 1 3 3 2021 21 21 1 6 37 2 2024 24 24 4 12 81 4 2060 60 60 0 53 370 6 query T SELECT to_char(ts, 'FF1 FF2 FF3 FF4 FF5 FF6 ff1 ff2 ff3 ff4 ff5 ff6 MS US') FROM ( VALUES ('2018-11-02 12:34:56'::timestamp), ('2018-11-02 12:34:56.78'), ('2018-11-02 12:34:56.78901'), ('2018-11-02 12:34:56.78901234') ) d(ts) ---- FF1 FF2 FF3 FF4 FF5 FF6 ff1 ff2 ff3 ff4 ff5 ff6 000 000000 FF1 FF2 FF3 FF4 FF5 FF6 ff1 ff2 ff3 ff4 ff5 ff6 780 780000 FF1 FF2 FF3 FF4 FF5 FF6 ff1 ff2 ff3 ff4 ff5 ff6 789 789010 FF1 FF2 FF3 FF4 FF5 FF6 ff1 ff2 ff3 ff4 ff5 ff6 789 789012 # Verify that the format string gets precompiled. query T multiline EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR SELECT to_char(ts, 'HH:MI:SS') FROM t ---- Explained Query: Project (#1) Map (tochartstz[HH:MI:SS](#0{ts})) ReadStorage materialize.public.t Source materialize.public.t Target cluster: quickstart EOF # Verify handling of NULL arguments. statement ok INSERT INTO t VALUES (NULL) query T rowsort SELECT to_char(ts, 'HH:MI:SS') FROM t ---- 01:07:12 07:59:59 12:00:00 12:09:23 NULL query T SELECT to_char(ts, NULL) FROM t ---- NULL NULL NULL NULL NULL