Materialize/test/sqllogictest/timestamptz.slt

# 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