# name: test/sql/function/timestamp/test_icu_datepart.test
# description: Test moving ICU date part functions
# group: [timestamp]

statement ok
SET default_null_order='nulls_first';

require icu

# Normalise the testing locale
statement ok
SET Calendar = 'gregorian';

statement ok
SET TimeZone = 'America/Los_Angeles';

statement ok
CREATE TABLE timestamps AS SELECT * FROM (VALUES
	('0044-03-13 (BC) 10:33:41.987654+01'::TIMESTAMPTZ, 'era'),
	('1962-07-31 12:20:48.123456+00'::TIMESTAMPTZ, 'epoch'),
	('2021-01-01 00:00:00+00'::TIMESTAMPTZ, 'year'),
	('2021-02-02 00:00:00+00'::TIMESTAMPTZ, 'month'),
	('2021-11-26 10:15:13.123456+00'::TIMESTAMPTZ, 'microsecond'),
	('2021-11-15 02:30:00-08'::TIMESTAMPTZ, 'hour'),
	('2021-11-15 02:30:00-07'::TIMESTAMPTZ, 'minute'),
	('2021-12-25 00:00:00+02'::TIMESTAMPTZ, 'day'),
	('infinity'::TIMESTAMPTZ, 'second'),
	('-infinity'::TIMESTAMPTZ, 'decade'),
	(NULL::TIMESTAMPTZ, 'century'),
) tbl(ts, part);

query II
SELECT year(ts), year(ts::TIMESTAMP) FROM timestamps;
----
44	-43
1962	1962
2020	2020
2021	2021
2021	2021
2021	2021
2021	2021
2021	2021
NULL	NULL
NULL	NULL
NULL	NULL

query II
SELECT month(ts), month(ts::TIMESTAMP) FROM timestamps;
----
3	3
7	7
12	12
2	2
11	11
11	11
11	11
12	12
NULL	NULL
NULL	NULL
NULL	NULL

query II
SELECT day(ts), day(ts::TIMESTAMP) FROM timestamps;
----
13	13
31	31
31	31
1	1
26	26
15	15
15	15
24	24
NULL	NULL
NULL	NULL
NULL	NULL

query II
SELECT decade(ts), decade(ts::TIMESTAMP) FROM timestamps;
----
4	-4
196	196
202	202
202	202
202	202
202	202
202	202
202	202
NULL	NULL
NULL	NULL
NULL	NULL

query II
SELECT century(ts), century(ts::TIMESTAMP) FROM timestamps;
----
-1	-1
20	20
21	21
21	21
21	21
21	21
21	21
21	21
NULL	NULL
NULL	NULL
NULL	NULL

query II
SELECT millennium(ts), millennium(ts::TIMESTAMP) FROM timestamps;
----
-1	-1
2	2
3	3
3	3
3	3
3	3
3	3
3	3
NULL	NULL
NULL	NULL
NULL	NULL

query II
SELECT microsecond(ts), microsecond(ts::TIMESTAMP) FROM timestamps;
----
43987654	43987654
48123456	48123456
0	0
0	0
13123456	13123456
0	0
0	0
0	0
NULL	NULL
NULL	NULL
NULL	NULL

query II
SELECT millisecond(ts), millisecond(ts::TIMESTAMP) FROM timestamps;
----
43987	43987
48123	48123
0	0
0	0
13123	13123
0	0
0	0
0	0
NULL	NULL
NULL	NULL
NULL	NULL

query II
SELECT second(ts), second(ts::TIMESTAMP) FROM timestamps;
----
43	43
48	48
0	0
0	0
13	13
0	0
0	0
0	0
NULL	NULL
NULL	NULL
NULL	NULL

query II
SELECT minute(ts), minute(ts::TIMESTAMP) FROM timestamps;
----
40	40
20	20
0	0
0	0
15	15
30	30
30	30
0	0
NULL	NULL
NULL	NULL
NULL	NULL

query II
SELECT hour(ts), hour(ts::TIMESTAMP) FROM timestamps;
----
1	1
5	5
16	16
16	16
2	2
2	2
1	1
14	14
NULL	NULL
NULL	NULL
NULL	NULL

# ICU uses the Julian calendar before the Gregorian Epoch.
query II
SELECT dayofweek(ts), dayofweek(ts::TIMESTAMP) FROM timestamps;
----
3	3
2	2
4	4
1	1
5	5
1	1
1	1
5	5
NULL	NULL
NULL	NULL
NULL	NULL

query II
SELECT isodow(ts), isodow(ts::TIMESTAMP) FROM timestamps;
----
3	3
2	2
4	4
1	1
5	5
1	1
1	1
5	5
NULL	NULL
NULL	NULL
NULL	NULL

query II
SELECT date_part('isodow', ts), date_part('isodow', ts::timestamp)
FROM range('1992-01-06'::timestamptz, '1992-01-13'::timestamptz, interval 1 day) as t(ts);
----
1	1
2	2
3	3
4	4
5	5
6	6
7	7

query II
SELECT date_part('dow', ts), date_part('dow', ts::timestamp)
FROM range('1992-01-05'::timestamptz, '1992-01-12'::timestamptz, interval 1 day) as t(ts);
----
0	0
1	1
2	2
3	3
4	4
5	5
6	6

query II
SELECT week(ts), week(ts::TIMESTAMP) FROM timestamps;
----
11	11
31	31
53	53
5	5
47	47
46	46
46	46
51	51
NULL	NULL
NULL	NULL
NULL	NULL

query II
SELECT dayofyear(ts), dayofyear(ts::TIMESTAMP) FROM timestamps;
----
72	72
212	212
366	366
32	32
330	330
319	319
319	319
358	358
NULL	NULL
NULL	NULL
NULL	NULL

query II
SELECT quarter(ts), quarter(ts::TIMESTAMP) FROM timestamps;
----
1	1
3	3
4	4
1	1
4	4
4	4
4	4
4	4
NULL	NULL
NULL	NULL
NULL	NULL

query II
SELECT yearweek(ts), yearweek(ts::TIMESTAMP) FROM timestamps;
----
-4311	-4311
196231	196231
202053	202053
202105	202105
202147	202147
202146	202146
202146	202146
202151	202151
NULL	NULL
NULL	NULL
NULL	NULL

query II
SELECT isoyear(ts), isoyear(ts::TIMESTAMP) FROM timestamps;
----
-43	-43
1962	1962
2020	2020
2021	2021
2021	2021
2021	2021
2021	2021
2021	2021
NULL	NULL
NULL	NULL
NULL	NULL

query II
SELECT epoch(ts), epoch(ts::TIMESTAMP) FROM timestamps;
----
-63517962378.012344	-63517990756.012344
-234185951.876544	-234211151.876544
1609459200.0	1609430400.0
1612224000.0	1612195200.0
1637921713.123456	1637892913.123456
1636972200.0	1636943400.0
1636968600.0	1636939800.0
1640383200.0	1640354400.0
NULL	NULL
NULL	NULL
NULL	NULL

query III
SELECT date_part(part, ts), date_part(part, ts::TIMESTAMP), part 
FROM timestamps
WHERE part NOT IN ('epoch', 'julian')
----
0	0	era
2020	2020	year
2	2	month
13123456	13123456	microsecond
2	2	hour
30	30	minute
24	24	day
NULL	NULL	second
NULL	NULL	decade
NULL	NULL	century

query I
SELECT ERA(ts) FROM timestamps;
----
0
1
1
1
1
1
1
1
NULL
NULL
NULL

query I
SELECT DATE_PART('era', ts) FROM timestamps;
----
0
1
1
1
1
1
1
1
NULL
NULL
NULL

# Beware the ides of March
query I
SELECT DATE_PART('julian', '0044-03-15 (BC) 12:00:00'::TIMESTAMPTZ);
----
1705428.5

query I
SELECT JULIAN(ts) FROM timestamps;
----
1705426.0699535608
2437877.2227792065
2459215.6666666665
2459247.6666666665
2459545.0939018917
2459534.1041666665
2459534.0625
2459573.5833333335
NULL
NULL
NULL

query I
SELECT DATE_PART('julian', ts) FROM timestamps;
----
1705426.0699535608
2437877.2227792065
2459215.6666666665
2459247.6666666665
2459545.0939018917
2459534.1041666665
2459534.0625
2459573.5833333335
NULL
NULL
NULL

query I
SELECT DATE_PART('timezone', ts) FROM timestamps;
----
-28378
-25200
-28800
-28800
-28800
-28800
-28800
-28800
NULL
NULL
NULL

query I
SELECT DATE_PART('timezone_hour', ts) FROM timestamps;
----
-7
-7
-8
-8
-8
-8
-8
-8
NULL
NULL
NULL

query I
SELECT DATE_PART('timezone_minute', ts) FROM timestamps;
----
-52
0
0
0
0
0
0
0
NULL
NULL
NULL

query I
SELECT DATE_PART('timezone', '2021-07-31 00:00:00-07'::TIMESTAMPTZ);
----
-25200

query I
SELECT DATE_PART('timezone_hour', '2021-07-31 00:00:00-07'::TIMESTAMPTZ);
----
-7

query I
SELECT DATE_PART('timezone_minute', '2021-07-31 00:00:00-07'::TIMESTAMPTZ);
----
0

# Multiple extractions
query IIIII
SELECT MAX(EXTRACT(YEAR FROM ts))
	, MAX(EXTRACT(MONTH FROM ts))
	, MAX(EXTRACT(DAY FROM ts))
	, MAX(EXTRACT(DECADE FROM ts))
	, MAX(EXTRACT(CENTURY FROM ts))
FROM timestamps
----
2021	12	31	202	21

# Aliases
query II
SELECT dayofmonth(ts), dayofmonth(ts::TIMESTAMP) FROM timestamps;
----
13	13
31	31
31	31
1	1
26	26
15	15
15	15
24	24
NULL	NULL
NULL	NULL
NULL	NULL

query II
SELECT weekday(ts), weekday(ts::TIMESTAMP) FROM timestamps;
----
3	3
2	2
4	4
1	1
5	5
1	1
1	1
5	5
NULL	NULL
NULL	NULL
NULL	NULL

query II
SELECT weekofyear(ts), weekofyear(ts::TIMESTAMP) FROM timestamps;
----
11	11
31	31
53	53
5	5
47	47
46	46
46	46
51	51
NULL	NULL
NULL	NULL
NULL	NULL

# Correctness: Compare date_part to named function
foreach partcode era millennium century decade year quarter month day hour minute second millisecond microsecond week weekday isodow dayofyear yearweek isoyear julian epoch timezone timezone_hour timezone_minute

query III
SELECT *
FROM (
	SELECT ts, DATE_PART('${partcode}', ts) AS p, ${partcode}(ts) AS f
	FROM timestamps
) tbl
WHERE p IS DISTINCT FROM f
----

endloop

#
# Struct parts
#

# Correctness: Compare struct values with scalar values
foreach partcode era millennium century decade year quarter month day hour minute second millisecond microsecond week weekday isodow doy yearweek isoyear julian epoch timezone timezone_hour timezone_minute

query III
SELECT ts, DATE_PART('${partcode}', ts) AS pc, DATE_PART(['${partcode}'], ts) AS st
FROM timestamps
WHERE pc IS DISTINCT FROM st['${partcode}'];
----

endloop

# Date parts
query II
SELECT DATE_PART(['era', 'year', 'month', 'day', 'julian'], ts), ts
FROM timestamps
ORDER BY 2
----
NULL	NULL
{'era': NULL, 'year': NULL, 'month': NULL, 'day': NULL, 'julian': NULL}	-infinity
{'era': 0, 'year': 44, 'month': 3, 'day': 13, 'julian': 1705426.0699535608}	0044-03-13 (BC) 01:40:43.987654-07:52
{'era': 1, 'year': 1962, 'month': 7, 'day': 31, 'julian': 2437877.2227792065}	1962-07-31 05:20:48.123456-07
{'era': 1, 'year': 2020, 'month': 12, 'day': 31, 'julian': 2459215.6666666665}	2020-12-31 16:00:00-08
{'era': 1, 'year': 2021, 'month': 2, 'day': 1, 'julian': 2459247.6666666665}	2021-02-01 16:00:00-08
{'era': 1, 'year': 2021, 'month': 11, 'day': 15, 'julian': 2459534.0625}	2021-11-15 01:30:00-08
{'era': 1, 'year': 2021, 'month': 11, 'day': 15, 'julian': 2459534.1041666665}	2021-11-15 02:30:00-08
{'era': 1, 'year': 2021, 'month': 11, 'day': 26, 'julian': 2459545.0939018917}	2021-11-26 02:15:13.123456-08
{'era': 1, 'year': 2021, 'month': 12, 'day': 24, 'julian': 2459573.5833333335}	2021-12-24 14:00:00-08
{'era': NULL, 'year': NULL, 'month': NULL, 'day': NULL, 'julian': NULL}	infinity

# Time parts
query II
SELECT DATE_PART(['hour', 'minute', 'microsecond'], ts), ts
FROM timestamps
ORDER BY 2
----
NULL	NULL
{'hour': NULL, 'minute': NULL, 'microsecond': NULL}	-infinity
{'hour': 1, 'minute': 40, 'microsecond': 43987654}	0044-03-13 (BC) 01:40:43.987654-07:52
{'hour': 5, 'minute': 20, 'microsecond': 48123456}	1962-07-31 05:20:48.123456-07
{'hour': 16, 'minute': 0, 'microsecond': 0}	2020-12-31 16:00:00-08
{'hour': 16, 'minute': 0, 'microsecond': 0}	2021-02-01 16:00:00-08
{'hour': 1, 'minute': 30, 'microsecond': 0}	2021-11-15 01:30:00-08
{'hour': 2, 'minute': 30, 'microsecond': 0}	2021-11-15 02:30:00-08
{'hour': 2, 'minute': 15, 'microsecond': 13123456}	2021-11-26 02:15:13.123456-08
{'hour': 14, 'minute': 0, 'microsecond': 0}	2021-12-24 14:00:00-08
{'hour': NULL, 'minute': NULL, 'microsecond': NULL}	infinity

# Year parts
query II
SELECT DATE_PART(['millennium', 'century', 'decade', 'quarter'], ts), ts
FROM timestamps
ORDER BY 2
----
NULL	NULL
{'millennium': NULL, 'century': NULL, 'decade': NULL, 'quarter': NULL}	-infinity
{'millennium': -1, 'century': -1, 'decade': 4, 'quarter': 1}	0044-03-13 (BC) 01:40:43.987654-07:52
{'millennium': 2, 'century': 20, 'decade': 196, 'quarter': 3}	1962-07-31 05:20:48.123456-07
{'millennium': 3, 'century': 21, 'decade': 202, 'quarter': 4}	2020-12-31 16:00:00-08
{'millennium': 3, 'century': 21, 'decade': 202, 'quarter': 1}	2021-02-01 16:00:00-08
{'millennium': 3, 'century': 21, 'decade': 202, 'quarter': 4}	2021-11-15 01:30:00-08
{'millennium': 3, 'century': 21, 'decade': 202, 'quarter': 4}	2021-11-15 02:30:00-08
{'millennium': 3, 'century': 21, 'decade': 202, 'quarter': 4}	2021-11-26 02:15:13.123456-08
{'millennium': 3, 'century': 21, 'decade': 202, 'quarter': 4}	2021-12-24 14:00:00-08
{'millennium': NULL, 'century': NULL, 'decade': NULL, 'quarter': NULL}	infinity

# Day parts
query II
SELECT DATE_PART(['weekday', 'isodow', 'doy'], ts), ts
FROM timestamps
ORDER BY 2
----
NULL	NULL
{'weekday': NULL, 'isodow': NULL, 'doy': NULL}	-infinity
{'weekday': 3, 'isodow': 3, 'doy': 72}	0044-03-13 (BC) 01:40:43.987654-07:52
{'weekday': 2, 'isodow': 2, 'doy': 212}	1962-07-31 05:20:48.123456-07
{'weekday': 4, 'isodow': 4, 'doy': 366}	2020-12-31 16:00:00-08
{'weekday': 1, 'isodow': 1, 'doy': 32}	2021-02-01 16:00:00-08
{'weekday': 1, 'isodow': 1, 'doy': 319}	2021-11-15 01:30:00-08
{'weekday': 1, 'isodow': 1, 'doy': 319}	2021-11-15 02:30:00-08
{'weekday': 5, 'isodow': 5, 'doy': 330}	2021-11-26 02:15:13.123456-08
{'weekday': 5, 'isodow': 5, 'doy': 358}	2021-12-24 14:00:00-08
{'weekday': NULL, 'isodow': NULL, 'doy': NULL}	infinity

# ISO parts
query II
SELECT DATE_PART(['isoyear', 'week', 'yearweek'], ts), ts
FROM timestamps
ORDER BY 2
----
NULL	NULL
{'isoyear': NULL, 'week': NULL, 'yearweek': NULL}	-infinity
{'isoyear': -43, 'week': 11, 'yearweek': -4311}	0044-03-13 (BC) 01:40:43.987654-07:52
{'isoyear': 1962, 'week': 31, 'yearweek': 196231}	1962-07-31 05:20:48.123456-07
{'isoyear': 2020, 'week': 53, 'yearweek': 202053}	2020-12-31 16:00:00-08
{'isoyear': 2021, 'week': 5, 'yearweek': 202105}	2021-02-01 16:00:00-08
{'isoyear': 2021, 'week': 46, 'yearweek': 202146}	2021-11-15 01:30:00-08
{'isoyear': 2021, 'week': 46, 'yearweek': 202146}	2021-11-15 02:30:00-08
{'isoyear': 2021, 'week': 47, 'yearweek': 202147}	2021-11-26 02:15:13.123456-08
{'isoyear': 2021, 'week': 51, 'yearweek': 202151}	2021-12-24 14:00:00-08
{'isoyear': NULL, 'week': NULL, 'yearweek': NULL}	infinity

# Miscellaneous parts
query II
SELECT DATE_PART(['epoch', 'second', 'timezone', 'timezone_hour', 'timezone_minute'], ts), ts
FROM timestamps
ORDER BY 2
----
NULL	NULL
{'epoch': NULL, 'second': NULL, 'timezone': NULL, 'timezone_hour': NULL, 'timezone_minute': NULL}	-infinity
{'epoch': -63517962378.012344, 'second': 43, 'timezone': -28378, 'timezone_hour': -7, 'timezone_minute': -52}	0044-03-13 (BC) 01:40:43.987654-07:52
{'epoch': -234185951.876544, 'second': 48, 'timezone': -25200, 'timezone_hour': -7, 'timezone_minute': 0}	1962-07-31 05:20:48.123456-07
{'epoch': 1609459200.0, 'second': 0, 'timezone': -28800, 'timezone_hour': -8, 'timezone_minute': 0}	2020-12-31 16:00:00-08
{'epoch': 1612224000.0, 'second': 0, 'timezone': -28800, 'timezone_hour': -8, 'timezone_minute': 0}	2021-02-01 16:00:00-08
{'epoch': 1636968600.0, 'second': 0, 'timezone': -28800, 'timezone_hour': -8, 'timezone_minute': 0}	2021-11-15 01:30:00-08
{'epoch': 1636972200.0, 'second': 0, 'timezone': -28800, 'timezone_hour': -8, 'timezone_minute': 0}	2021-11-15 02:30:00-08
{'epoch': 1637921713.123456, 'second': 13, 'timezone': -28800, 'timezone_hour': -8, 'timezone_minute': 0}	2021-11-26 02:15:13.123456-08
{'epoch': 1640383200.0, 'second': 0, 'timezone': -28800, 'timezone_hour': -8, 'timezone_minute': 0}	2021-12-24 14:00:00-08
{'epoch': NULL, 'second': NULL, 'timezone': NULL, 'timezone_hour': NULL, 'timezone_minute': NULL}	infinity

# Correctness: Compare struct values with scalar values
foreach partcode era millennium century decade year quarter month day hour minute second millisecond microsecond week weekday isodow doy yearweek isoyear julian epoch timezone timezone_hour timezone_minute

query III
SELECT ts, DATE_PART('${partcode}', ts) AS p, DATE_PART(['${partcode}'], ts) AS s
FROM timestamps
WHERE p IS DISTINCT FROM s['${partcode}'];
----

endloop

# Selective filtering (Issue #5342)
query II
SELECT ts, DATE_PART(['year', 'month', 'day'], ts) AS parts
FROM timestamps
WHERE part = 'day'
ORDER BY 1;
----
2021-12-24 14:00:00-08	{'year': 2021, 'month': 12, 'day': 24}

# Invalid parts
statement error
SELECT DATE_PART(['duck', 'minute', 'microsecond', 'timezone'], ts), ts
FROM timestamps
ORDER BY 2
----

# Duplicate parts
statement error
SELECT DATE_PART(['era', 'year', 'month', 'era'], ts), ts
FROM timestamps
ORDER BY 2
----

# Empty parts
statement error
SELECT DATE_PART([], ts) FROM timestamps;
----

# Null parts
statement error
SELECT DATE_PART(['year', NULL, 'month'], ts) FROM timestamps;
----

# Non-constant parts
statement error
WITH parts(p) AS (VALUES (['year', 'month', 'day']), (['hour', 'minute', 'microsecond']))
SELECT DATE_PART(p, ts) FROM parts, timestamps;
----

#  last_day
statement ok
CREATE TABLE februaries AS
SELECT ts::TIMESTAMPTZ AS ts
FROM (VALUES
	('1900-02-12'),
	('1992-02-12'),
	('2000-02-12')
) tbl(ts);

query TTT
SELECT ts, LAST_DAY(ts), LAST_DAY(ts::TIMESTAMP) FROM februaries;
----
1900-02-12 00:00:00-08	1900-02-28	1900-02-28
1992-02-12 00:00:00-08	1992-02-29	1992-02-29
2000-02-12 00:00:00-08	2000-02-29	2000-02-29

query TTT
SELECT ts, LAST_DAY(ts), LAST_DAY(ts::TIMESTAMP) FROM timestamps;
----
0044-03-13 (BC) 01:40:43.987654-07:52	0044-03-31 (BC)	0044-03-31 (BC)
1962-07-31 05:20:48.123456-07	1962-07-31	1962-07-31
2020-12-31 16:00:00-08	2020-12-31	2020-12-31
2021-02-01 16:00:00-08	2021-02-28	2021-02-28
2021-11-26 02:15:13.123456-08	2021-11-30	2021-11-30
2021-11-15 02:30:00-08	2021-11-30	2021-11-30
2021-11-15 01:30:00-08	2021-11-30	2021-11-30
2021-12-24 14:00:00-08	2021-12-31	2021-12-31
infinity	NULL	NULL
-infinity	NULL	NULL
NULL	NULL	NULL

# dayname/monthname
query II
SELECT dayname(ts), monthname(ts) from timestamps;
----
Wednesday	March
Tuesday	July
Thursday	December
Monday	February
Friday	November
Monday	November
Monday	November
Friday	December
NULL	NULL
NULL	NULL
NULL	NULL

#
# Fractional Time Zones
#

statement ok
SET TimeZone='Asia/Kathmandu';

query I
SELECT DATE_PART(['timezone', 'timezone_hour', 'timezone_minute'], '2021-12-22'::TIMESTAMPTZ);
----
{'timezone': 20700, 'timezone_hour': 5, 'timezone_minute': 45}

statement ok
SET TimeZone='Canada/Newfoundland';

query I
SELECT DATE_PART(['timezone', 'timezone_hour', 'timezone_minute'], '2021-12-22'::TIMESTAMPTZ);
----
{'timezone': -12600, 'timezone_hour': -3, 'timezone_minute': -30}


#
# 13 month Calendars
#

statement ok
SET Calendar = 'hebrew';

# Year 5872 (2021-2022) was a leap year
query III
SELECT year(ts), month(ts), monthname(ts)
FROM range('5872-01-01'::TIMESTAMPTZ, '5873-01-01'::TIMESTAMPTZ, INTERVAL 1 DAY) tbl(ts)
GROUP BY ALL
ORDER BY ALL;
----
5872	1	January
5872	2	February
5872	3	March
5872	4	April
5872	5	May
5872	7	July
5872	8	August
5872	9	September
5872	10	October
5872	11	November
5872	12	December
5872	13	Undecimber