Materialize/test/sqllogictest/timestamp.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, timestamp '2001-01-01') AS equal
FROM (
  VALUES
  ('week', '7d'),
  ('day', '1d'),
  ('hour', '1h'),
  ('minute', '1m'),
  ('second', '1s')
) intervals (str, interval),
(VALUES (timestamp '2020-02-29 15:44:17.71393')) 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, timestamp '2020-03-02') AS equal
FROM (
  VALUES
  ('week', '7d'),
  ('day', '1d'),
  ('hour', '1h'),
  ('minute', '1m'),
  ('second', '1s')
) intervals (str, interval),
(VALUES (timestamp '2020-02-29 15:44:17.71393')) 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 (timestamp '2020-02-11 15:44:17.71393')) ts (ts),
(VALUES (timestamp '2001-01-01')) origin (origin);
----
15␠days 2020-02-06␠00:00:00
2␠hours 2020-02-11␠14:00:00
10␠seconds 2020-02-11␠15:44:10
15␠minutes 2020-02-11␠15:30:00
1␠hour␠30␠minutes 2020-02-11␠15:00:00

# shift bins using the origin parameter:
query T
SELECT date_bin('5 min'::interval, timestamp '2020-02-01 01:01:01', timestamp '2020-02-01 00:02:30');
----
2020-02-01 00:57:30

# disallow > day intervals
query error timestamps cannot be binned into intervals containing months or years
SELECT date_bin('5 months'::interval, timestamp '2020-02-01 01:01:01', timestamp '2001-01-01');

query error timestamps cannot be binned into intervals containing months or years
SELECT date_bin('5 years'::interval,  timestamp '2020-02-01 01:01:01', timestamp '2001-01-01');

# disallow zero intervals
query error stride must be greater than zero
SELECT date_bin('0 days'::interval, timestamp '1970-01-01 01:00:00' , timestamp '1970-01-01 00:00:00');

# disallow negative intervals
query error stride must be greater than zero
SELECT date_bin('-2 days'::interval, timestamp '1970-01-01 01:00:00' , timestamp '1970-01-01 00:00:00');

# max stride caught
query error stride cannot exceed
SELECT date_bin('9223372037 s'::interval, timestamp '2020-02-01 01:01:01', timestamp '2020-02-01 00:02:30');

# max diff caught
query error source and origin must not differ more than 2\^63 nanoseconds
SELECT date_bin('1m', timestamp '2262-04-11 23:47:17');

# Test unintuitive day binning
query TT rowsort
SELECT
    "interval",
    date_bin("interval"::interval, timestamp '2020-02-27 15:44:17.71393', timestamp '2001-01-01')
FROM (
    VALUES
    ('1d'), ('2d'), ('3d'),
    ('4d'), ('5d'), ('6d'),
    ('7d'), ('8d'), ('9d')
) intervals ("interval");
----
1d 2020-02-27␠00:00:00
2d 2020-02-27␠00:00:00
3d 2020-02-27␠00:00:00
4d 2020-02-27␠00:00:00
5d 2020-02-26␠00:00:00
6d 2020-02-27␠00:00:00
7d 2020-02-24␠00:00:00
8d 2020-02-23␠00:00:00
9d 2020-02-24␠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, timestamp '1970-01-01') AS equal
FROM (
  VALUES
  ('15 days'),
  ('2 hours'),
  ('1 hour 30 minutes'),
  ('15 minutes'),
  ('10 seconds')
) intervals (interval),
(VALUES (timestamp '2020-02-29 15:44:17.71393')) ts (ts);
----
15␠days true
2␠hours true
10␠seconds true
15␠minutes true
1␠hour␠30␠minutes true

query T
SELECT '99999-01-01'::TIMESTAMP + '162143 y';
----
262142-01-01 00:00:00

query error timestamp out of range
SELECT '99999-01-01'::TIMESTAMP + '162144 y';

# date_bin_hopping
simple conn=mz_system,user=mz_system
ALTER SYSTEM SET enable_date_bin_hopping = true
----
COMPLETE 0

query T rowsort
SELECT * FROM date_bin_hopping('45s', '1m', '2001-01-01 00:01:20') ORDER BY 1;
----
2001-01-01 00:00:30+00
2001-01-01 00:01:15+00

statement ok
CREATE TABLE t (ts timestamp, v int);

statement ok
INSERT INTO t VALUES
  ('2021-01-01 01:05', 41),
  ('2021-01-01 01:07', 21),
  ('2021-01-01 01:09', 51),
  ('2021-01-01 01:11', 31),
  ('2021-01-01 01:13', 11),
  ('2021-01-01 01:17', 61);

# ts unix epoch origin
query TI rowsort
SELECT date_bin_hopping AS w, sum(v)
  FROM t,
  date_bin_hopping('5m', '10m', t.ts)
GROUP BY w
ORDER BY 1;
----
2021-01-01␠01:00:00 113
2021-01-01␠01:05:00 155
2021-01-01␠01:10:00 103
2021-01-01␠01:15:00 61

# tstz unix epoch origin
query TI rowsort
SELECT date_bin_hopping AS w, sum(v)
  FROM t,
  date_bin_hopping('5m', '10m', t.ts::timestamptz)
GROUP BY w
ORDER BY 1;
----
2021-01-01␠01:00:00+00 113
2021-01-01␠01:05:00+00 155
2021-01-01␠01:10:00+00 103
2021-01-01␠01:15:00+00 61

# ts
query TI rowsort
SELECT date_bin_hopping AS w, sum(v)
  FROM t,
  date_bin_hopping('5m', '10m', t.ts, '1970-01-01')
GROUP BY w
ORDER BY 1;
----
2021-01-01␠01:00:00 113
2021-01-01␠01:05:00 155
2021-01-01␠01:10:00 103
2021-01-01␠01:15:00 61

#tstz
query TI rowsort
SELECT date_bin_hopping AS w, sum(v)
  FROM t,
  date_bin_hopping('5m', '10m', t.ts::timestamptz, '1970-01-01')
GROUP BY w
ORDER BY 1;
----
2021-01-01␠01:00:00+00 113
2021-01-01␠01:05:00+00 155
2021-01-01␠01:10:00+00 103
2021-01-01␠01:15:00+00 61

statement ok
DROP TABLE t;

### to_char

statement ok
CREATE TABLE t (ts timestamp)

statement ok
INSERT INTO t VALUES
  ('1997-01-01 00:00:00'),
  ('2021-02-09 01:07:12'),
  ('2024-03-21 12:09:23'),
  ('2060-12-31 23:59:59.999999')

query T rowsort
SELECT to_char(ts, 'DAY Day day DY Dy dy MONTH Month month RM MON Mon mon') FROM t
----
FRIDAY    Friday    friday    FRI Fri fri DECEMBER  December  december  XII  DEC Dec dec
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
----
FRIDAY Friday friday DECEMBER December december XII
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,060 2060 060 60 0 21 4 12 53 366 31 6 2473825

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,060 2060 60 60 0 21 4 12 53 366 31 6 2473825

query T rowsort
SELECT to_char(ts, 'HH HH12 HH24 MI SS SSSS') FROM t
----
01 01 01 07 12 4032
11 11 23 59 59 86399
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 11: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
12--text--09--text--23
23--text--59--text--59

query T rowsort
SELECT to_char(ts, 'YYYYTH YYYYth Jth') FROM t
----
1997TH 1997th 2450450th
2021ST 2021st 2459255th
2024TH 2024th 2460391st
2060TH 2060th 2473825th

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
2060 A.D. 2060 a.d. 2060 ad 11:59:59 P.M. 11:59:59 p.m. 11:59:59 pm

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 369 5

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 369 5

query T rowsort
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 (tocharts[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
11:59:59
12:00:00
12:09:23
NULL

query T rowsort
SELECT to_char(ts, NULL) FROM t
----
NULL
NULL
NULL
NULL
NULL

query T
SELECT '0001-12-31 19:03:58 BC'::timestamp;
----
0001-12-31 19:03:58 BC

query T
SELECT '0001-12-31 19:03:58 EST BC'::timestamp;
----
0001-12-31 19:03:58 BC

query T
SELECT '0001-12-31 19:03:58 bc'::timestamp;
----
0001-12-31 19:03:58 BC

query T
SELECT '0001-12-31 19:03:58 BC '::timestamp;
----
0001-12-31 19:03:58 BC

query T
SELECT '0001-12-31 19:03:58BC '::timestamp;
----
0001-12-31 19:03:58 BC

query T
SELECT '0001-12-31 19:03:58 AD'::timestamp;
----
0001-12-31 19:03:58

query T
SELECT '0001-12-31 19:03:58 EST AD'::timestamp;
----
0001-12-31 19:03:58

query T
SELECT '0001-12-31 19:03:58 ad'::timestamp;
----
0001-12-31 19:03:58

query T
SELECT '0001-12-31 19:03:58 AD '::timestamp;
----
0001-12-31 19:03:58

query T
SELECT '0001-12-31 19:03:58AD '::timestamp;
----
0001-12-31 19:03:58

# Regression test for #9236 which mis-parsed timezones ending with 'AD' or 'BC'.
query T
SELECT '2020-12-21 18:53:49 Asia/Baghdad'::timestamp;
----
2020-12-21 18:53:49

# Regression test covering case where a characters lower is 1 byte and upper is 2 bytes.
# Incorrect failing case is a panic due to splitting a string in the middle of a char.
query error invalid input syntax for type timestamp: Invalid character at offset 19 in 2025-01-01 00:00:00ı: 'ı': "2025-01-01 00:00:00ı AD"
SELECT '2025-01-01 00:00:00ı AD'::timestamp;