# name: test/sql/function/timestamp/test_date_part.test
# description: Test the DATE_PART function for TIMESTAMPs
# group: [timestamp]

statement ok
SET default_null_order='nulls_first';

statement ok
PRAGMA enable_verification

statement ok
CREATE TABLE timestamps(ts TIMESTAMP)

statement ok
INSERT INTO timestamps VALUES
	('-infinity'),
	('1962-07-31 12:20:48.123456'),
	('1969-01-01 01:03:20.45432'),
	('1992-01-01 01:01:01.400'),
	('1992-01-01 01:01:02.200'),
	('1992-01-01 01:01:02.400'),
	('1993-08-14 08:22:33'),
	('1993-08-14 08:22:33.42'),
	('2001-04-20 14:42:11.0'),
	('2001-04-20 14:42:11.123'),
	('2004-01-31 12:00:00.000050'),
	('2004-01-31 12:00:00.050'),
	('2004-02-01 12:00:00.000050'),
	('2004-02-01 12:00:00.050'),
	('2004-02-29 13:05:47.123456'),
	('2008-01-01 00:00:01.5'),
	('2008-01-01 00:00:01.594'),
	('2008-01-01 00:00:01.794'),
	('2008-01-01 00:00:01.88926'),
	('2008-01-01 00:00:01.894'),
	('2008-01-01 00:00:01.98926'),
	('2008-01-01 00:00:01.99926'),
	('2008-01-01 00:00:11.1'),
	('2019-01-06 04:03:02.123456'),
	('2019-01-06 04:03:02.5'),
	('2020-01-01 00:00:01.88926'),
	('2020-12-31 21:25:58.745232'),
	('2021-04-15 14:55:17.915'),
	('2021-04-15 14:55:17.915000'),
	('2021-05-02 12:11:49.5'),
	('2021-12-01 13:54:48.123456'),
	('2022-01-01 00:00:41'),
	('infinity'),
	(NULL)

# Century and millennia rules
statement ok
CREATE TABLE millennia AS SELECT * FROM (VALUES
	('1001-03-15 (BC) 20:38:40'::TIMESTAMP),
	('0044-03-15 (BC) 20:38:40'::TIMESTAMP),
	('0998-02-16 20:38:40'::TIMESTAMP),
	('1998-02-16 20:38:40'::TIMESTAMP),
	('2001-02-16 20:38:40'::TIMESTAMP)
) tbl(ts);

query II
SELECT ts, DATE_PART('millennium', ts) FROM millennia;
----
1001-03-15 (BC) 20:38:40	-2
0044-03-15 (BC) 20:38:40	-1
0998-02-16 20:38:40	1
1998-02-16 20:38:40	2
2001-02-16 20:38:40	3


query II
SELECT ts, DATE_PART('century', ts) FROM millennia;
----
1001-03-15 (BC) 20:38:40	-11
0044-03-15 (BC) 20:38:40	-1
0998-02-16 20:38:40	10
1998-02-16 20:38:40	20
2001-02-16 20:38:40	21

# ISO Year
query II
SELECT DATE_PART('isoyear', ts), ts
FROM timestamps
ORDER BY 2;
----
NULL	NULL
NULL	-infinity
1962	1962-07-31 12:20:48.123456
1969	1969-01-01 01:03:20.45432
1992	1992-01-01 01:01:01.4
1992	1992-01-01 01:01:02.2
1992	1992-01-01 01:01:02.4
1993	1993-08-14 08:22:33
1993	1993-08-14 08:22:33.42
2001	2001-04-20 14:42:11
2001	2001-04-20 14:42:11.123
2004	2004-01-31 12:00:00.00005
2004	2004-01-31 12:00:00.05
2004	2004-02-01 12:00:00.00005
2004	2004-02-01 12:00:00.05
2004	2004-02-29 13:05:47.123456
2008	2008-01-01 00:00:01.5
2008	2008-01-01 00:00:01.594
2008	2008-01-01 00:00:01.794
2008	2008-01-01 00:00:01.88926
2008	2008-01-01 00:00:01.894
2008	2008-01-01 00:00:01.98926
2008	2008-01-01 00:00:01.99926
2008	2008-01-01 00:00:11.1
2019	2019-01-06 04:03:02.123456
2019	2019-01-06 04:03:02.5
2020	2020-01-01 00:00:01.88926
2020	2020-12-31 21:25:58.745232
2021	2021-04-15 14:55:17.915
2021	2021-04-15 14:55:17.915
2021	2021-05-02 12:11:49.5
2021	2021-12-01 13:54:48.123456
2021	2022-01-01 00:00:41
NULL	infinity

# 2022-01-03 is the first day of ISO-2022
query II
SELECT DATE_PART('isoyear', ts), ts
FROM generate_series('2021-12-26'::TIMESTAMP, '2022-01-12'::TIMESTAMP, INTERVAL 1 DAY) tbl(ts);
----
2021	2021-12-26 00:00:00
2021	2021-12-27 00:00:00
2021	2021-12-28 00:00:00
2021	2021-12-29 00:00:00
2021	2021-12-30 00:00:00
2021	2021-12-31 00:00:00
2021	2022-01-01 00:00:00
2021	2022-01-02 00:00:00
2022	2022-01-03 00:00:00
2022	2022-01-04 00:00:00
2022	2022-01-05 00:00:00
2022	2022-01-06 00:00:00
2022	2022-01-07 00:00:00
2022	2022-01-08 00:00:00
2022	2022-01-09 00:00:00
2022	2022-01-10 00:00:00
2022	2022-01-11 00:00:00
2022	2022-01-12 00:00:00

# Julian Days
query II
SELECT DATE_PART('julian', ts), ts
FROM timestamps
ORDER BY 2;
----
NULL	NULL
NULL	-infinity
2437877.5144458734	1962-07-31 12:20:48.123456
2440223.0439867396	1969-01-01 01:03:20.45432
2448623.042377315	1992-01-01 01:01:01.4
2448623.042386574	1992-01-01 01:01:02.2
2448623.042388889	1992-01-01 01:01:02.4
2449214.3489930555	1993-08-14 08:22:33
2449214.3489979166	1993-08-14 08:22:33.42
2452020.612627315	2001-04-20 14:42:11
2452020.6126287384	2001-04-20 14:42:11.123
2453036.5000000005	2004-01-31 12:00:00.00005
2453036.500000579	2004-01-31 12:00:00.05
2453037.5000000005	2004-02-01 12:00:00.00005
2453037.500000579	2004-02-01 12:00:00.05
2453065.5456842994	2004-02-29 13:05:47.123456
2454467.0000173612	2008-01-01 00:00:01.5
2454467.000018449	2008-01-01 00:00:01.594
2454467.000020764	2008-01-01 00:00:01.794
2454467.0000218665	2008-01-01 00:00:01.88926
2454467.0000219215	2008-01-01 00:00:01.894
2454467.0000230237	2008-01-01 00:00:01.98926
2454467.0000231396	2008-01-01 00:00:01.99926
2454467.000128472	2008-01-01 00:00:11.1
2458490.168774577	2019-01-06 04:03:02.123456
2458490.168778935	2019-01-06 04:03:02.5
2458850.0000218665	2020-01-01 00:00:01.88926
2459215.893041033	2020-12-31 21:25:58.745232
2459320.621735127	2021-04-15 14:55:17.915
2459320.621735127	2021-04-15 14:55:17.915
2459337.5082118055	2021-05-02 12:11:49.5
2459550.579723651	2021-12-01 13:54:48.123456
2459581.0004745373	2022-01-01 00:00:41
NULL	infinity

# 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 epoch timezone timezone_hour timezone_minute julian

query III
SELECT ts, DATE_PART('${partcode}', ts) AS p, ${partcode}(ts) AS f
FROM timestamps
WHERE p <> f
----

endloop

# Cast injection for DOUBLE types
foreach tstype TIMESTAMP TIMESTAMP_S TIMESTAMP_MS TIMESTAMP_NS

query I
SELECT EXTRACT(EPOCH FROM ${tstype} '1992-09-20 11:30:00.0');
----
716988600.0

query I
SELECT EXTRACT(JULIAN FROM ${tstype} '1992-09-20 00:00:00.0');
----
2448886.0

endloop


#
# Structs
#

# 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 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 <> s['${partcode}'];
----

endloop

# Date parts
query II
SELECT ts::DATE AS d, DATE_PART(['year', 'month', 'day'], ts) AS parts
FROM timestamps
ORDER BY 1;
----
NULL	NULL
-infinity	{'year': NULL, 'month': NULL, 'day': NULL}
1962-07-31	{'year': 1962, 'month': 7, 'day': 31}
1969-01-01	{'year': 1969, 'month': 1, 'day': 1}
1992-01-01	{'year': 1992, 'month': 1, 'day': 1}
1992-01-01	{'year': 1992, 'month': 1, 'day': 1}
1992-01-01	{'year': 1992, 'month': 1, 'day': 1}
1993-08-14	{'year': 1993, 'month': 8, 'day': 14}
1993-08-14	{'year': 1993, 'month': 8, 'day': 14}
2001-04-20	{'year': 2001, 'month': 4, 'day': 20}
2001-04-20	{'year': 2001, 'month': 4, 'day': 20}
2004-01-31	{'year': 2004, 'month': 1, 'day': 31}
2004-01-31	{'year': 2004, 'month': 1, 'day': 31}
2004-02-01	{'year': 2004, 'month': 2, 'day': 1}
2004-02-01	{'year': 2004, 'month': 2, 'day': 1}
2004-02-29	{'year': 2004, 'month': 2, 'day': 29}
2008-01-01	{'year': 2008, 'month': 1, 'day': 1}
2008-01-01	{'year': 2008, 'month': 1, 'day': 1}
2008-01-01	{'year': 2008, 'month': 1, 'day': 1}
2008-01-01	{'year': 2008, 'month': 1, 'day': 1}
2008-01-01	{'year': 2008, 'month': 1, 'day': 1}
2008-01-01	{'year': 2008, 'month': 1, 'day': 1}
2008-01-01	{'year': 2008, 'month': 1, 'day': 1}
2008-01-01	{'year': 2008, 'month': 1, 'day': 1}
2019-01-06	{'year': 2019, 'month': 1, 'day': 6}
2019-01-06	{'year': 2019, 'month': 1, 'day': 6}
2020-01-01	{'year': 2020, 'month': 1, 'day': 1}
2020-12-31	{'year': 2020, 'month': 12, 'day': 31}
2021-04-15	{'year': 2021, 'month': 4, 'day': 15}
2021-04-15	{'year': 2021, 'month': 4, 'day': 15}
2021-05-02	{'year': 2021, 'month': 5, 'day': 2}
2021-12-01	{'year': 2021, 'month': 12, 'day': 1}
2022-01-01	{'year': 2022, 'month': 1, 'day': 1}
infinity	{'year': NULL, 'month': NULL, 'day': NULL}

query II
SELECT ts::DATE AS d, DATE_PART(['year', 'month', 'day'], ts) AS parts
FROM millennia
ORDER BY 1;
----
1001-03-15 (BC)	{'year': -1000, 'month': 3, 'day': 15}
0044-03-15 (BC)	{'year': -43, 'month': 3, 'day': 15}
0998-02-16	{'year': 998, 'month': 2, 'day': 16}
1998-02-16	{'year': 1998, 'month': 2, 'day': 16}
2001-02-16	{'year': 2001, 'month': 2, 'day': 16}

# Year parts
query II
SELECT ts::DATE AS d, DATE_PART(['era', 'millennium', 'century', 'decade', 'quarter'], ts) AS parts
FROM timestamps
ORDER BY 1;
----
NULL	NULL
-infinity	{'era': NULL, 'millennium': NULL, 'century': NULL, 'decade': NULL, 'quarter': NULL}
1962-07-31	{'era': 1, 'millennium': 2, 'century': 20, 'decade': 196, 'quarter': 3}
1969-01-01	{'era': 1, 'millennium': 2, 'century': 20, 'decade': 196, 'quarter': 1}
1992-01-01	{'era': 1, 'millennium': 2, 'century': 20, 'decade': 199, 'quarter': 1}
1992-01-01	{'era': 1, 'millennium': 2, 'century': 20, 'decade': 199, 'quarter': 1}
1992-01-01	{'era': 1, 'millennium': 2, 'century': 20, 'decade': 199, 'quarter': 1}
1993-08-14	{'era': 1, 'millennium': 2, 'century': 20, 'decade': 199, 'quarter': 3}
1993-08-14	{'era': 1, 'millennium': 2, 'century': 20, 'decade': 199, 'quarter': 3}
2001-04-20	{'era': 1, 'millennium': 3, 'century': 21, 'decade': 200, 'quarter': 2}
2001-04-20	{'era': 1, 'millennium': 3, 'century': 21, 'decade': 200, 'quarter': 2}
2004-01-31	{'era': 1, 'millennium': 3, 'century': 21, 'decade': 200, 'quarter': 1}
2004-01-31	{'era': 1, 'millennium': 3, 'century': 21, 'decade': 200, 'quarter': 1}
2004-02-01	{'era': 1, 'millennium': 3, 'century': 21, 'decade': 200, 'quarter': 1}
2004-02-01	{'era': 1, 'millennium': 3, 'century': 21, 'decade': 200, 'quarter': 1}
2004-02-29	{'era': 1, 'millennium': 3, 'century': 21, 'decade': 200, 'quarter': 1}
2008-01-01	{'era': 1, 'millennium': 3, 'century': 21, 'decade': 200, 'quarter': 1}
2008-01-01	{'era': 1, 'millennium': 3, 'century': 21, 'decade': 200, 'quarter': 1}
2008-01-01	{'era': 1, 'millennium': 3, 'century': 21, 'decade': 200, 'quarter': 1}
2008-01-01	{'era': 1, 'millennium': 3, 'century': 21, 'decade': 200, 'quarter': 1}
2008-01-01	{'era': 1, 'millennium': 3, 'century': 21, 'decade': 200, 'quarter': 1}
2008-01-01	{'era': 1, 'millennium': 3, 'century': 21, 'decade': 200, 'quarter': 1}
2008-01-01	{'era': 1, 'millennium': 3, 'century': 21, 'decade': 200, 'quarter': 1}
2008-01-01	{'era': 1, 'millennium': 3, 'century': 21, 'decade': 200, 'quarter': 1}
2019-01-06	{'era': 1, 'millennium': 3, 'century': 21, 'decade': 201, 'quarter': 1}
2019-01-06	{'era': 1, 'millennium': 3, 'century': 21, 'decade': 201, 'quarter': 1}
2020-01-01	{'era': 1, 'millennium': 3, 'century': 21, 'decade': 202, 'quarter': 1}
2020-12-31	{'era': 1, 'millennium': 3, 'century': 21, 'decade': 202, 'quarter': 4}
2021-04-15	{'era': 1, 'millennium': 3, 'century': 21, 'decade': 202, 'quarter': 2}
2021-04-15	{'era': 1, 'millennium': 3, 'century': 21, 'decade': 202, 'quarter': 2}
2021-05-02	{'era': 1, 'millennium': 3, 'century': 21, 'decade': 202, 'quarter': 2}
2021-12-01	{'era': 1, 'millennium': 3, 'century': 21, 'decade': 202, 'quarter': 4}
2022-01-01	{'era': 1, 'millennium': 3, 'century': 21, 'decade': 202, 'quarter': 1}
infinity	{'era': NULL, 'millennium': NULL, 'century': NULL, 'decade': NULL, 'quarter': NULL}

query II
SELECT ts::DATE AS d, DATE_PART(['era', 'millennium', 'century', 'decade', 'quarter'], ts) AS parts
FROM millennia
ORDER BY 1;
----
1001-03-15 (BC)	{'era': 0, 'millennium': -2, 'century': -11, 'decade': -100, 'quarter': 1}
0044-03-15 (BC)	{'era': 0, 'millennium': -1, 'century': -1, 'decade': -4, 'quarter': 1}
0998-02-16	{'era': 1, 'millennium': 1, 'century': 10, 'decade': 99, 'quarter': 1}
1998-02-16	{'era': 1, 'millennium': 2, 'century': 20, 'decade': 199, 'quarter': 1}
2001-02-16	{'era': 1, 'millennium': 3, 'century': 21, 'decade': 200, 'quarter': 1}

# Day parts
query II
SELECT ts::DATE AS d, DATE_PART(['weekday', 'isodow','doy', 'julian'], ts) AS parts
FROM timestamps
ORDER BY ts;
----
NULL	NULL
-infinity	{'weekday': NULL, 'isodow': NULL, 'doy': NULL, 'julian': NULL}
1962-07-31	{'weekday': 2, 'isodow': 2, 'doy': 212, 'julian': 2437877.5144458734}
1969-01-01	{'weekday': 3, 'isodow': 3, 'doy': 1, 'julian': 2440223.0439867396}
1992-01-01	{'weekday': 3, 'isodow': 3, 'doy': 1, 'julian': 2448623.042377315}
1992-01-01	{'weekday': 3, 'isodow': 3, 'doy': 1, 'julian': 2448623.042386574}
1992-01-01	{'weekday': 3, 'isodow': 3, 'doy': 1, 'julian': 2448623.042388889}
1993-08-14	{'weekday': 6, 'isodow': 6, 'doy': 226, 'julian': 2449214.3489930555}
1993-08-14	{'weekday': 6, 'isodow': 6, 'doy': 226, 'julian': 2449214.3489979166}
2001-04-20	{'weekday': 5, 'isodow': 5, 'doy': 110, 'julian': 2452020.612627315}
2001-04-20	{'weekday': 5, 'isodow': 5, 'doy': 110, 'julian': 2452020.6126287384}
2004-01-31	{'weekday': 6, 'isodow': 6, 'doy': 31, 'julian': 2453036.5000000005}
2004-01-31	{'weekday': 6, 'isodow': 6, 'doy': 31, 'julian': 2453036.500000579}
2004-02-01	{'weekday': 0, 'isodow': 7, 'doy': 32, 'julian': 2453037.5000000005}
2004-02-01	{'weekday': 0, 'isodow': 7, 'doy': 32, 'julian': 2453037.500000579}
2004-02-29	{'weekday': 0, 'isodow': 7, 'doy': 60, 'julian': 2453065.5456842994}
2008-01-01	{'weekday': 2, 'isodow': 2, 'doy': 1, 'julian': 2454467.0000173612}
2008-01-01	{'weekday': 2, 'isodow': 2, 'doy': 1, 'julian': 2454467.000018449}
2008-01-01	{'weekday': 2, 'isodow': 2, 'doy': 1, 'julian': 2454467.000020764}
2008-01-01	{'weekday': 2, 'isodow': 2, 'doy': 1, 'julian': 2454467.0000218665}
2008-01-01	{'weekday': 2, 'isodow': 2, 'doy': 1, 'julian': 2454467.0000219215}
2008-01-01	{'weekday': 2, 'isodow': 2, 'doy': 1, 'julian': 2454467.0000230237}
2008-01-01	{'weekday': 2, 'isodow': 2, 'doy': 1, 'julian': 2454467.0000231396}
2008-01-01	{'weekday': 2, 'isodow': 2, 'doy': 1, 'julian': 2454467.000128472}
2019-01-06	{'weekday': 0, 'isodow': 7, 'doy': 6, 'julian': 2458490.168774577}
2019-01-06	{'weekday': 0, 'isodow': 7, 'doy': 6, 'julian': 2458490.168778935}
2020-01-01	{'weekday': 3, 'isodow': 3, 'doy': 1, 'julian': 2458850.0000218665}
2020-12-31	{'weekday': 4, 'isodow': 4, 'doy': 366, 'julian': 2459215.893041033}
2021-04-15	{'weekday': 4, 'isodow': 4, 'doy': 105, 'julian': 2459320.621735127}
2021-04-15	{'weekday': 4, 'isodow': 4, 'doy': 105, 'julian': 2459320.621735127}
2021-05-02	{'weekday': 0, 'isodow': 7, 'doy': 122, 'julian': 2459337.5082118055}
2021-12-01	{'weekday': 3, 'isodow': 3, 'doy': 335, 'julian': 2459550.579723651}
2022-01-01	{'weekday': 6, 'isodow': 6, 'doy': 1, 'julian': 2459581.0004745373}
infinity	{'weekday': NULL, 'isodow': NULL, 'doy': NULL, 'julian': NULL}

query I
SELECT DATE_PART(['weekday', 'isodow', 'doy', 'julian'], '2008-01-01 00:00:01.894'::TIMESTAMP) AS parts
----
{'weekday': 2, 'isodow': 2, 'doy': 1, 'julian': 2454467.0000219215}

query II
SELECT ts::DATE AS d, DATE_PART(['weekday', 'isodow', 'doy', 'julian'], ts) AS parts
FROM millennia
ORDER BY 1;
----
1001-03-15 (BC)	{'weekday': 6, 'isodow': 6, 'doy': 74, 'julian': 1355891.8601851852}
0044-03-15 (BC)	{'weekday': 5, 'isodow': 5, 'doy': 74, 'julian': 1705428.8601851852}
0998-02-16	{'weekday': 5, 'isodow': 5, 'doy': 47, 'julian': 2085619.8601851852}
1998-02-16	{'weekday': 1, 'isodow': 1, 'doy': 47, 'julian': 2450861.860185185}
2001-02-16	{'weekday': 5, 'isodow': 5, 'doy': 47, 'julian': 2451957.860185185}

# ISO parts
query II
SELECT ts::DATE AS d, DATE_PART(['isoyear', 'week', 'yearweek'], ts) AS parts
FROM timestamps
ORDER BY 1;
----
NULL	NULL
-infinity	{'isoyear': NULL, 'week': NULL, 'yearweek': NULL}
1962-07-31	{'isoyear': 1962, 'week': 31, 'yearweek': 196231}
1969-01-01	{'isoyear': 1969, 'week': 1, 'yearweek': 196901}
1992-01-01	{'isoyear': 1992, 'week': 1, 'yearweek': 199201}
1992-01-01	{'isoyear': 1992, 'week': 1, 'yearweek': 199201}
1992-01-01	{'isoyear': 1992, 'week': 1, 'yearweek': 199201}
1993-08-14	{'isoyear': 1993, 'week': 32, 'yearweek': 199332}
1993-08-14	{'isoyear': 1993, 'week': 32, 'yearweek': 199332}
2001-04-20	{'isoyear': 2001, 'week': 16, 'yearweek': 200116}
2001-04-20	{'isoyear': 2001, 'week': 16, 'yearweek': 200116}
2004-01-31	{'isoyear': 2004, 'week': 5, 'yearweek': 200405}
2004-01-31	{'isoyear': 2004, 'week': 5, 'yearweek': 200405}
2004-02-01	{'isoyear': 2004, 'week': 5, 'yearweek': 200405}
2004-02-01	{'isoyear': 2004, 'week': 5, 'yearweek': 200405}
2004-02-29	{'isoyear': 2004, 'week': 9, 'yearweek': 200409}
2008-01-01	{'isoyear': 2008, 'week': 1, 'yearweek': 200801}
2008-01-01	{'isoyear': 2008, 'week': 1, 'yearweek': 200801}
2008-01-01	{'isoyear': 2008, 'week': 1, 'yearweek': 200801}
2008-01-01	{'isoyear': 2008, 'week': 1, 'yearweek': 200801}
2008-01-01	{'isoyear': 2008, 'week': 1, 'yearweek': 200801}
2008-01-01	{'isoyear': 2008, 'week': 1, 'yearweek': 200801}
2008-01-01	{'isoyear': 2008, 'week': 1, 'yearweek': 200801}
2008-01-01	{'isoyear': 2008, 'week': 1, 'yearweek': 200801}
2019-01-06	{'isoyear': 2019, 'week': 1, 'yearweek': 201901}
2019-01-06	{'isoyear': 2019, 'week': 1, 'yearweek': 201901}
2020-01-01	{'isoyear': 2020, 'week': 1, 'yearweek': 202001}
2020-12-31	{'isoyear': 2020, 'week': 53, 'yearweek': 202053}
2021-04-15	{'isoyear': 2021, 'week': 15, 'yearweek': 202115}
2021-04-15	{'isoyear': 2021, 'week': 15, 'yearweek': 202115}
2021-05-02	{'isoyear': 2021, 'week': 17, 'yearweek': 202117}
2021-12-01	{'isoyear': 2021, 'week': 48, 'yearweek': 202148}
2022-01-01	{'isoyear': 2021, 'week': 52, 'yearweek': 202152}
infinity	{'isoyear': NULL, 'week': NULL, 'yearweek': NULL}

query II
SELECT ts::DATE AS d, DATE_PART(['isoyear', 'week', 'yearweek'], ts) AS parts
FROM millennia
ORDER BY 1;
----
1001-03-15 (BC)	{'isoyear': -1000, 'week': 11, 'yearweek': -100011}
0044-03-15 (BC)	{'isoyear': -43, 'week': 11, 'yearweek': -4311}
0998-02-16	{'isoyear': 998, 'week': 7, 'yearweek': 99807}
1998-02-16	{'isoyear': 1998, 'week': 8, 'yearweek': 199808}
2001-02-16	{'isoyear': 2001, 'week': 7, 'yearweek': 200107}

# Time parts
query II
SELECT ts, DATE_PART(['hour', 'minute', 'microsecond'], ts) AS parts
FROM timestamps
ORDER BY 1;
----
NULL	NULL
-infinity	{'hour': NULL, 'minute': NULL, 'microsecond': NULL}
1962-07-31 12:20:48.123456	{'hour': 12, 'minute': 20, 'microsecond': 48123456}
1969-01-01 01:03:20.45432	{'hour': 1, 'minute': 3, 'microsecond': 20454320}
1992-01-01 01:01:01.4	{'hour': 1, 'minute': 1, 'microsecond': 1400000}
1992-01-01 01:01:02.2	{'hour': 1, 'minute': 1, 'microsecond': 2200000}
1992-01-01 01:01:02.4	{'hour': 1, 'minute': 1, 'microsecond': 2400000}
1993-08-14 08:22:33	{'hour': 8, 'minute': 22, 'microsecond': 33000000}
1993-08-14 08:22:33.42	{'hour': 8, 'minute': 22, 'microsecond': 33420000}
2001-04-20 14:42:11	{'hour': 14, 'minute': 42, 'microsecond': 11000000}
2001-04-20 14:42:11.123	{'hour': 14, 'minute': 42, 'microsecond': 11123000}
2004-01-31 12:00:00.00005	{'hour': 12, 'minute': 0, 'microsecond': 50}
2004-01-31 12:00:00.05	{'hour': 12, 'minute': 0, 'microsecond': 50000}
2004-02-01 12:00:00.00005	{'hour': 12, 'minute': 0, 'microsecond': 50}
2004-02-01 12:00:00.05	{'hour': 12, 'minute': 0, 'microsecond': 50000}
2004-02-29 13:05:47.123456	{'hour': 13, 'minute': 5, 'microsecond': 47123456}
2008-01-01 00:00:01.5	{'hour': 0, 'minute': 0, 'microsecond': 1500000}
2008-01-01 00:00:01.594	{'hour': 0, 'minute': 0, 'microsecond': 1594000}
2008-01-01 00:00:01.794	{'hour': 0, 'minute': 0, 'microsecond': 1794000}
2008-01-01 00:00:01.88926	{'hour': 0, 'minute': 0, 'microsecond': 1889260}
2008-01-01 00:00:01.894	{'hour': 0, 'minute': 0, 'microsecond': 1894000}
2008-01-01 00:00:01.98926	{'hour': 0, 'minute': 0, 'microsecond': 1989260}
2008-01-01 00:00:01.99926	{'hour': 0, 'minute': 0, 'microsecond': 1999260}
2008-01-01 00:00:11.1	{'hour': 0, 'minute': 0, 'microsecond': 11100000}
2019-01-06 04:03:02.123456	{'hour': 4, 'minute': 3, 'microsecond': 2123456}
2019-01-06 04:03:02.5	{'hour': 4, 'minute': 3, 'microsecond': 2500000}
2020-01-01 00:00:01.88926	{'hour': 0, 'minute': 0, 'microsecond': 1889260}
2020-12-31 21:25:58.745232	{'hour': 21, 'minute': 25, 'microsecond': 58745232}
2021-04-15 14:55:17.915	{'hour': 14, 'minute': 55, 'microsecond': 17915000}
2021-04-15 14:55:17.915	{'hour': 14, 'minute': 55, 'microsecond': 17915000}
2021-05-02 12:11:49.5	{'hour': 12, 'minute': 11, 'microsecond': 49500000}
2021-12-01 13:54:48.123456	{'hour': 13, 'minute': 54, 'microsecond': 48123456}
2022-01-01 00:00:41	{'hour': 0, 'minute': 0, 'microsecond': 41000000}
infinity	{'hour': NULL, 'minute': NULL, 'microsecond': NULL}

query II
SELECT ts, DATE_PART(['hour', 'minute', 'microsecond'], ts) AS parts
FROM millennia
ORDER BY 1;
----
1001-03-15 (BC) 20:38:40	{'hour': 20, 'minute': 38, 'microsecond': 40000000}
0044-03-15 (BC) 20:38:40	{'hour': 20, 'minute': 38, 'microsecond': 40000000}
0998-02-16 20:38:40	{'hour': 20, 'minute': 38, 'microsecond': 40000000}
1998-02-16 20:38:40	{'hour': 20, 'minute': 38, 'microsecond': 40000000}
2001-02-16 20:38:40	{'hour': 20, 'minute': 38, 'microsecond': 40000000}

# Miscellaneous parts
query II
SELECT ts, DATE_PART(['epoch', 'timezone', 'timezone_hour', 'timezone_minute'], ts) AS parts
FROM timestamps
ORDER BY 1;
----
NULL	NULL
-infinity	{'epoch': NULL, 'timezone': NULL, 'timezone_hour': NULL, 'timezone_minute': NULL}
1962-07-31 12:20:48.123456	{'epoch': -234185951.876544, 'timezone': 0, 'timezone_hour': 0, 'timezone_minute': 0}
1969-01-01 01:03:20.45432	{'epoch': -31532199.54568, 'timezone': 0, 'timezone_hour': 0, 'timezone_minute': 0}
1992-01-01 01:01:01.4	{'epoch': 694227661.4, 'timezone': 0, 'timezone_hour': 0, 'timezone_minute': 0}
1992-01-01 01:01:02.2	{'epoch': 694227662.2, 'timezone': 0, 'timezone_hour': 0, 'timezone_minute': 0}
1992-01-01 01:01:02.4	{'epoch': 694227662.4, 'timezone': 0, 'timezone_hour': 0, 'timezone_minute': 0}
1993-08-14 08:22:33	{'epoch': 745316553.0, 'timezone': 0, 'timezone_hour': 0, 'timezone_minute': 0}
1993-08-14 08:22:33.42	{'epoch': 745316553.42, 'timezone': 0, 'timezone_hour': 0, 'timezone_minute': 0}
2001-04-20 14:42:11	{'epoch': 987777731.0, 'timezone': 0, 'timezone_hour': 0, 'timezone_minute': 0}
2001-04-20 14:42:11.123	{'epoch': 987777731.123, 'timezone': 0, 'timezone_hour': 0, 'timezone_minute': 0}
2004-01-31 12:00:00.00005	{'epoch': 1075550400.00005, 'timezone': 0, 'timezone_hour': 0, 'timezone_minute': 0}
2004-01-31 12:00:00.05	{'epoch': 1075550400.05, 'timezone': 0, 'timezone_hour': 0, 'timezone_minute': 0}
2004-02-01 12:00:00.00005	{'epoch': 1075636800.00005, 'timezone': 0, 'timezone_hour': 0, 'timezone_minute': 0}
2004-02-01 12:00:00.05	{'epoch': 1075636800.05, 'timezone': 0, 'timezone_hour': 0, 'timezone_minute': 0}
2004-02-29 13:05:47.123456	{'epoch': 1078059947.123456, 'timezone': 0, 'timezone_hour': 0, 'timezone_minute': 0}
2008-01-01 00:00:01.5	{'epoch': 1199145601.5, 'timezone': 0, 'timezone_hour': 0, 'timezone_minute': 0}
2008-01-01 00:00:01.594	{'epoch': 1199145601.594, 'timezone': 0, 'timezone_hour': 0, 'timezone_minute': 0}
2008-01-01 00:00:01.794	{'epoch': 1199145601.794, 'timezone': 0, 'timezone_hour': 0, 'timezone_minute': 0}
2008-01-01 00:00:01.88926	{'epoch': 1199145601.88926, 'timezone': 0, 'timezone_hour': 0, 'timezone_minute': 0}
2008-01-01 00:00:01.894	{'epoch': 1199145601.894, 'timezone': 0, 'timezone_hour': 0, 'timezone_minute': 0}
2008-01-01 00:00:01.98926	{'epoch': 1199145601.98926, 'timezone': 0, 'timezone_hour': 0, 'timezone_minute': 0}
2008-01-01 00:00:01.99926	{'epoch': 1199145601.99926, 'timezone': 0, 'timezone_hour': 0, 'timezone_minute': 0}
2008-01-01 00:00:11.1	{'epoch': 1199145611.1, 'timezone': 0, 'timezone_hour': 0, 'timezone_minute': 0}
2019-01-06 04:03:02.123456	{'epoch': 1546747382.123456, 'timezone': 0, 'timezone_hour': 0, 'timezone_minute': 0}
2019-01-06 04:03:02.5	{'epoch': 1546747382.5, 'timezone': 0, 'timezone_hour': 0, 'timezone_minute': 0}
2020-01-01 00:00:01.88926	{'epoch': 1577836801.88926, 'timezone': 0, 'timezone_hour': 0, 'timezone_minute': 0}
2020-12-31 21:25:58.745232	{'epoch': 1609449958.745232, 'timezone': 0, 'timezone_hour': 0, 'timezone_minute': 0}
2021-04-15 14:55:17.915	{'epoch': 1618498517.915, 'timezone': 0, 'timezone_hour': 0, 'timezone_minute': 0}
2021-04-15 14:55:17.915	{'epoch': 1618498517.915, 'timezone': 0, 'timezone_hour': 0, 'timezone_minute': 0}
2021-05-02 12:11:49.5	{'epoch': 1619957509.5, 'timezone': 0, 'timezone_hour': 0, 'timezone_minute': 0}
2021-12-01 13:54:48.123456	{'epoch': 1638366888.123456, 'timezone': 0, 'timezone_hour': 0, 'timezone_minute': 0}
2022-01-01 00:00:41	{'epoch': 1640995241.0, 'timezone': 0, 'timezone_hour': 0, 'timezone_minute': 0}
infinity	{'epoch': NULL, 'timezone': NULL, 'timezone_hour': NULL, 'timezone_minute': NULL}

query II
SELECT ts, DATE_PART(['epoch', 'timezone', 'timezone_hour', 'timezone_minute'], ts) AS parts
FROM millennia
ORDER BY 1;
----
1001-03-15 (BC) 20:38:40	{'epoch': -93717746480.0, 'timezone': 0, 'timezone_hour': 0, 'timezone_minute': 0}
0044-03-15 (BC) 20:38:40	{'epoch': -63517749680.0, 'timezone': 0, 'timezone_hour': 0, 'timezone_minute': 0}
0998-02-16 20:38:40	{'epoch': -30669247280.0, 'timezone': 0, 'timezone_hour': 0, 'timezone_minute': 0}
1998-02-16 20:38:40	{'epoch': 887661520.0, 'timezone': 0, 'timezone_hour': 0, 'timezone_minute': 0}
2001-02-16 20:38:40	{'epoch': 982355920.0, 'timezone': 0, 'timezone_hour': 0, 'timezone_minute': 0}

query I
SELECT DATE_PART(['hour', 'minute', 'microsecond'], NULL::TIMESTAMP);
----
NULL

query I
SELECT DATE_PART(['hour', 'minute', 'microsecond'], '1962-07-31 12:20:48.123456'::TIMESTAMP);
----
{'hour': 12, 'minute': 20, 'microsecond': 48123456}

# Aliases
query I
SELECT DATE_PART(['month', 'months', 'mon'], '1962-07-31 12:20:48.123456'::TIMESTAMP);
----
{'month': 7, 'months': 7, 'mon': 7}

query II
SELECT ts::DATE AS d, DATE_PART(['year', 'month', 'months'], ts) AS parts
FROM timestamps
ORDER BY 1;
----
NULL	NULL
-infinity	{'year': NULL, 'month': NULL, 'months': NULL}
1962-07-31	{'year': 1962, 'month': 7, 'months': 7}
1969-01-01	{'year': 1969, 'month': 1, 'months': 1}
1992-01-01	{'year': 1992, 'month': 1, 'months': 1}
1992-01-01	{'year': 1992, 'month': 1, 'months': 1}
1992-01-01	{'year': 1992, 'month': 1, 'months': 1}
1993-08-14	{'year': 1993, 'month': 8, 'months': 8}
1993-08-14	{'year': 1993, 'month': 8, 'months': 8}
2001-04-20	{'year': 2001, 'month': 4, 'months': 4}
2001-04-20	{'year': 2001, 'month': 4, 'months': 4}
2004-01-31	{'year': 2004, 'month': 1, 'months': 1}
2004-01-31	{'year': 2004, 'month': 1, 'months': 1}
2004-02-01	{'year': 2004, 'month': 2, 'months': 2}
2004-02-01	{'year': 2004, 'month': 2, 'months': 2}
2004-02-29	{'year': 2004, 'month': 2, 'months': 2}
2008-01-01	{'year': 2008, 'month': 1, 'months': 1}
2008-01-01	{'year': 2008, 'month': 1, 'months': 1}
2008-01-01	{'year': 2008, 'month': 1, 'months': 1}
2008-01-01	{'year': 2008, 'month': 1, 'months': 1}
2008-01-01	{'year': 2008, 'month': 1, 'months': 1}
2008-01-01	{'year': 2008, 'month': 1, 'months': 1}
2008-01-01	{'year': 2008, 'month': 1, 'months': 1}
2008-01-01	{'year': 2008, 'month': 1, 'months': 1}
2019-01-06	{'year': 2019, 'month': 1, 'months': 1}
2019-01-06	{'year': 2019, 'month': 1, 'months': 1}
2020-01-01	{'year': 2020, 'month': 1, 'months': 1}
2020-12-31	{'year': 2020, 'month': 12, 'months': 12}
2021-04-15	{'year': 2021, 'month': 4, 'months': 4}
2021-04-15	{'year': 2021, 'month': 4, 'months': 4}
2021-05-02	{'year': 2021, 'month': 5, 'months': 5}
2021-12-01	{'year': 2021, 'month': 12, 'months': 12}
2022-01-01	{'year': 2022, 'month': 1, 'months': 1}
infinity	{'year': NULL, 'month': NULL, 'months': NULL}

#
# Function-only parts
#
query II
SELECT ts, epoch_us(ts) FROM timestamps ORDER BY ALL;
----
NULL	NULL
-infinity	NULL
1962-07-31 12:20:48.123456	-234185951876544
1969-01-01 01:03:20.45432	-31532199545680
1992-01-01 01:01:01.4	694227661400000
1992-01-01 01:01:02.2	694227662200000
1992-01-01 01:01:02.4	694227662400000
1993-08-14 08:22:33	745316553000000
1993-08-14 08:22:33.42	745316553420000
2001-04-20 14:42:11	987777731000000
2001-04-20 14:42:11.123	987777731123000
2004-01-31 12:00:00.00005	1075550400000050
2004-01-31 12:00:00.05	1075550400050000
2004-02-01 12:00:00.00005	1075636800000050
2004-02-01 12:00:00.05	1075636800050000
2004-02-29 13:05:47.123456	1078059947123456
2008-01-01 00:00:01.5	1199145601500000
2008-01-01 00:00:01.594	1199145601594000
2008-01-01 00:00:01.794	1199145601794000
2008-01-01 00:00:01.88926	1199145601889260
2008-01-01 00:00:01.894	1199145601894000
2008-01-01 00:00:01.98926	1199145601989260
2008-01-01 00:00:01.99926	1199145601999260
2008-01-01 00:00:11.1	1199145611100000
2019-01-06 04:03:02.123456	1546747382123456
2019-01-06 04:03:02.5	1546747382500000
2020-01-01 00:00:01.88926	1577836801889260
2020-12-31 21:25:58.745232	1609449958745232
2021-04-15 14:55:17.915	1618498517915000
2021-04-15 14:55:17.915	1618498517915000
2021-05-02 12:11:49.5	1619957509500000
2021-12-01 13:54:48.123456	1638366888123456
2022-01-01 00:00:41	1640995241000000
infinity	NULL

# EPOCH_US doesn't  require ICU
query II
SELECT ts::TIMESTAMPTZ, epoch_us(ts::TIMESTAMPTZ) FROM timestamps ORDER BY ALL;
----
NULL	NULL
-infinity	NULL
1962-07-31 12:20:48.123456+00	-234185951876544
1969-01-01 01:03:20.45432+00	-31532199545680
1992-01-01 01:01:01.4+00	694227661400000
1992-01-01 01:01:02.2+00	694227662200000
1992-01-01 01:01:02.4+00	694227662400000
1993-08-14 08:22:33+00	745316553000000
1993-08-14 08:22:33.42+00	745316553420000
2001-04-20 14:42:11+00	987777731000000
2001-04-20 14:42:11.123+00	987777731123000
2004-01-31 12:00:00.00005+00	1075550400000050
2004-01-31 12:00:00.05+00	1075550400050000
2004-02-01 12:00:00.00005+00	1075636800000050
2004-02-01 12:00:00.05+00	1075636800050000
2004-02-29 13:05:47.123456+00	1078059947123456
2008-01-01 00:00:01.5+00	1199145601500000
2008-01-01 00:00:01.594+00	1199145601594000
2008-01-01 00:00:01.794+00	1199145601794000
2008-01-01 00:00:01.88926+00	1199145601889260
2008-01-01 00:00:01.894+00	1199145601894000
2008-01-01 00:00:01.98926+00	1199145601989260
2008-01-01 00:00:01.99926+00	1199145601999260
2008-01-01 00:00:11.1+00	1199145611100000
2019-01-06 04:03:02.123456+00	1546747382123456
2019-01-06 04:03:02.5+00	1546747382500000
2020-01-01 00:00:01.88926+00	1577836801889260
2020-12-31 21:25:58.745232+00	1609449958745232
2021-04-15 14:55:17.915+00	1618498517915000
2021-04-15 14:55:17.915+00	1618498517915000
2021-05-02 12:11:49.5+00	1619957509500000
2021-12-01 13:54:48.123456+00	1638366888123456
2022-01-01 00:00:41+00	1640995241000000
infinity	NULL

query II
SELECT ts, epoch_ms(ts) FROM timestamps ORDER BY ALL;
----
NULL	NULL
-infinity	NULL
1962-07-31 12:20:48.123456	-234185951876
1969-01-01 01:03:20.45432	-31532199545
1992-01-01 01:01:01.4	694227661400
1992-01-01 01:01:02.2	694227662200
1992-01-01 01:01:02.4	694227662400
1993-08-14 08:22:33	745316553000
1993-08-14 08:22:33.42	745316553420
2001-04-20 14:42:11	987777731000
2001-04-20 14:42:11.123	987777731123
2004-01-31 12:00:00.00005	1075550400000
2004-01-31 12:00:00.05	1075550400050
2004-02-01 12:00:00.00005	1075636800000
2004-02-01 12:00:00.05	1075636800050
2004-02-29 13:05:47.123456	1078059947123
2008-01-01 00:00:01.5	1199145601500
2008-01-01 00:00:01.594	1199145601594
2008-01-01 00:00:01.794	1199145601794
2008-01-01 00:00:01.88926	1199145601889
2008-01-01 00:00:01.894	1199145601894
2008-01-01 00:00:01.98926	1199145601989
2008-01-01 00:00:01.99926	1199145601999
2008-01-01 00:00:11.1	1199145611100
2019-01-06 04:03:02.123456	1546747382123
2019-01-06 04:03:02.5	1546747382500
2020-01-01 00:00:01.88926	1577836801889
2020-12-31 21:25:58.745232	1609449958745
2021-04-15 14:55:17.915	1618498517915
2021-04-15 14:55:17.915	1618498517915
2021-05-02 12:11:49.5	1619957509500
2021-12-01 13:54:48.123456	1638366888123
2022-01-01 00:00:41	1640995241000
infinity	NULL

# EPOCH_MS doesn't  require ICU
query II
SELECT ts::TIMESTAMPTZ, epoch_ms(ts::TIMESTAMPTZ) FROM timestamps ORDER BY ALL;
----
NULL	NULL
-infinity	NULL
1962-07-31 12:20:48.123456+00	-234185951876
1969-01-01 01:03:20.45432+00	-31532199545
1992-01-01 01:01:01.4+00	694227661400
1992-01-01 01:01:02.2+00	694227662200
1992-01-01 01:01:02.4+00	694227662400
1993-08-14 08:22:33+00	745316553000
1993-08-14 08:22:33.42+00	745316553420
2001-04-20 14:42:11+00	987777731000
2001-04-20 14:42:11.123+00	987777731123
2004-01-31 12:00:00.00005+00	1075550400000
2004-01-31 12:00:00.05+00	1075550400050
2004-02-01 12:00:00.00005+00	1075636800000
2004-02-01 12:00:00.05+00	1075636800050
2004-02-29 13:05:47.123456+00	1078059947123
2008-01-01 00:00:01.5+00	1199145601500
2008-01-01 00:00:01.594+00	1199145601594
2008-01-01 00:00:01.794+00	1199145601794
2008-01-01 00:00:01.88926+00	1199145601889
2008-01-01 00:00:01.894+00	1199145601894
2008-01-01 00:00:01.98926+00	1199145601989
2008-01-01 00:00:01.99926+00	1199145601999
2008-01-01 00:00:11.1+00	1199145611100
2019-01-06 04:03:02.123456+00	1546747382123
2019-01-06 04:03:02.5+00	1546747382500
2020-01-01 00:00:01.88926+00	1577836801889
2020-12-31 21:25:58.745232+00	1609449958745
2021-04-15 14:55:17.915+00	1618498517915
2021-04-15 14:55:17.915+00	1618498517915
2021-05-02 12:11:49.5+00	1619957509500
2021-12-01 13:54:48.123456+00	1638366888123
2022-01-01 00:00:41+00	1640995241000
infinity	NULL

query II
SELECT ts, epoch_ns(ts) FROM timestamps ORDER BY ALL;
----
NULL	NULL
-infinity	NULL
1962-07-31 12:20:48.123456	-234185951876544000
1969-01-01 01:03:20.45432	-31532199545680000
1992-01-01 01:01:01.4	694227661400000000
1992-01-01 01:01:02.2	694227662200000000
1992-01-01 01:01:02.4	694227662400000000
1993-08-14 08:22:33	745316553000000000
1993-08-14 08:22:33.42	745316553420000000
2001-04-20 14:42:11	987777731000000000
2001-04-20 14:42:11.123	987777731123000000
2004-01-31 12:00:00.00005	1075550400000050000
2004-01-31 12:00:00.05	1075550400050000000
2004-02-01 12:00:00.00005	1075636800000050000
2004-02-01 12:00:00.05	1075636800050000000
2004-02-29 13:05:47.123456	1078059947123456000
2008-01-01 00:00:01.5	1199145601500000000
2008-01-01 00:00:01.594	1199145601594000000
2008-01-01 00:00:01.794	1199145601794000000
2008-01-01 00:00:01.88926	1199145601889260000
2008-01-01 00:00:01.894	1199145601894000000
2008-01-01 00:00:01.98926	1199145601989260000
2008-01-01 00:00:01.99926	1199145601999260000
2008-01-01 00:00:11.1	1199145611100000000
2019-01-06 04:03:02.123456	1546747382123456000
2019-01-06 04:03:02.5	1546747382500000000
2020-01-01 00:00:01.88926	1577836801889260000
2020-12-31 21:25:58.745232	1609449958745232000
2021-04-15 14:55:17.915	1618498517915000000
2021-04-15 14:55:17.915	1618498517915000000
2021-05-02 12:11:49.5	1619957509500000000
2021-12-01 13:54:48.123456	1638366888123456000
2022-01-01 00:00:41	1640995241000000000
infinity	NULL

# EPOCH_NS doesn't  require ICU
query II
SELECT ts::TIMESTAMPTZ, epoch_ns(ts::TIMESTAMPTZ) FROM timestamps ORDER BY ALL;
----
NULL	NULL
-infinity	NULL
1962-07-31 12:20:48.123456+00	-234185951876544000
1969-01-01 01:03:20.45432+00	-31532199545680000
1992-01-01 01:01:01.4+00	694227661400000000
1992-01-01 01:01:02.2+00	694227662200000000
1992-01-01 01:01:02.4+00	694227662400000000
1993-08-14 08:22:33+00	745316553000000000
1993-08-14 08:22:33.42+00	745316553420000000
2001-04-20 14:42:11+00	987777731000000000
2001-04-20 14:42:11.123+00	987777731123000000
2004-01-31 12:00:00.00005+00	1075550400000050000
2004-01-31 12:00:00.05+00	1075550400050000000
2004-02-01 12:00:00.00005+00	1075636800000050000
2004-02-01 12:00:00.05+00	1075636800050000000
2004-02-29 13:05:47.123456+00	1078059947123456000
2008-01-01 00:00:01.5+00	1199145601500000000
2008-01-01 00:00:01.594+00	1199145601594000000
2008-01-01 00:00:01.794+00	1199145601794000000
2008-01-01 00:00:01.88926+00	1199145601889260000
2008-01-01 00:00:01.894+00	1199145601894000000
2008-01-01 00:00:01.98926+00	1199145601989260000
2008-01-01 00:00:01.99926+00	1199145601999260000
2008-01-01 00:00:11.1+00	1199145611100000000
2019-01-06 04:03:02.123456+00	1546747382123456000
2019-01-06 04:03:02.5+00	1546747382500000000
2020-01-01 00:00:01.88926+00	1577836801889260000
2020-12-31 21:25:58.745232+00	1609449958745232000
2021-04-15 14:55:17.915+00	1618498517915000000
2021-04-15 14:55:17.915+00	1618498517915000000
2021-05-02 12:11:49.5+00	1619957509500000000
2021-12-01 13:54:48.123456+00	1638366888123456000
2022-01-01 00:00:41+00	1640995241000000000
infinity	NULL

query II
SELECT ts, nanosecond(ts) FROM timestamps ORDER BY ALL;
----
NULL	NULL
-infinity	NULL
1962-07-31 12:20:48.123456	48123456000
1969-01-01 01:03:20.45432	20454320000
1992-01-01 01:01:01.4	1400000000
1992-01-01 01:01:02.2	2200000000
1992-01-01 01:01:02.4	2400000000
1993-08-14 08:22:33	33000000000
1993-08-14 08:22:33.42	33420000000
2001-04-20 14:42:11	11000000000
2001-04-20 14:42:11.123	11123000000
2004-01-31 12:00:00.00005	50000
2004-01-31 12:00:00.05	50000000
2004-02-01 12:00:00.00005	50000
2004-02-01 12:00:00.05	50000000
2004-02-29 13:05:47.123456	47123456000
2008-01-01 00:00:01.5	1500000000
2008-01-01 00:00:01.594	1594000000
2008-01-01 00:00:01.794	1794000000
2008-01-01 00:00:01.88926	1889260000
2008-01-01 00:00:01.894	1894000000
2008-01-01 00:00:01.98926	1989260000
2008-01-01 00:00:01.99926	1999260000
2008-01-01 00:00:11.1	11100000000
2019-01-06 04:03:02.123456	2123456000
2019-01-06 04:03:02.5	2500000000
2020-01-01 00:00:01.88926	1889260000
2020-12-31 21:25:58.745232	58745232000
2021-04-15 14:55:17.915	17915000000
2021-04-15 14:55:17.915	17915000000
2021-05-02 12:11:49.5	49500000000
2021-12-01 13:54:48.123456	48123456000
2022-01-01 00:00:41	41000000000
infinity	NULL

query II
SELECT ts::TIMESTAMPTZ, nanosecond(ts::TIMESTAMPTZ) FROM timestamps ORDER BY ALL;
----
NULL	NULL
-infinity	NULL
1962-07-31 12:20:48.123456+00	48123456000
1969-01-01 01:03:20.45432+00	20454320000
1992-01-01 01:01:01.4+00	1400000000
1992-01-01 01:01:02.2+00	2200000000
1992-01-01 01:01:02.4+00	2400000000
1993-08-14 08:22:33+00	33000000000
1993-08-14 08:22:33.42+00	33420000000
2001-04-20 14:42:11+00	11000000000
2001-04-20 14:42:11.123+00	11123000000
2004-01-31 12:00:00.00005+00	50000
2004-01-31 12:00:00.05+00	50000000
2004-02-01 12:00:00.00005+00	50000
2004-02-01 12:00:00.05+00	50000000
2004-02-29 13:05:47.123456+00	47123456000
2008-01-01 00:00:01.5+00	1500000000
2008-01-01 00:00:01.594+00	1594000000
2008-01-01 00:00:01.794+00	1794000000
2008-01-01 00:00:01.88926+00	1889260000
2008-01-01 00:00:01.894+00	1894000000
2008-01-01 00:00:01.98926+00	1989260000
2008-01-01 00:00:01.99926+00	1999260000
2008-01-01 00:00:11.1+00	11100000000
2019-01-06 04:03:02.123456+00	2123456000
2019-01-06 04:03:02.5+00	2500000000
2020-01-01 00:00:01.88926+00	1889260000
2020-12-31 21:25:58.745232+00	58745232000
2021-04-15 14:55:17.915+00	17915000000
2021-04-15 14:55:17.915+00	17915000000
2021-05-02 12:11:49.5+00	49500000000
2021-12-01 13:54:48.123456+00	48123456000
2022-01-01 00:00:41+00	41000000000
infinity	NULL

query I 
SELECT nanosecond(t) 
FROM VALUES 
    ('1992-01-01 12:03:27.123456789'::TIMESTAMP_NS),
    ('1900-01-01 03:08:47.987654'::TIMESTAMP_NS),
    (NULL::TIMESTAMP_NS),
    ('2020-09-27 13:12:01'::TIMESTAMP_NS)
    AS tbl(t);
----
27123456789
47987654000
NULL
1000000000

# Invalid parts
statement error
SELECT ts, DATE_PART(['duck', 'month', 'day'], ts) AS parts
FROM timestamps
ORDER BY 1;
----

# Duplicate parts
statement error
SELECT ts, DATE_PART(['year', 'month', 'day', 'year'], ts) AS parts
FROM timestamps
ORDER BY 1;
----

# 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;
----

# epoch_ns has nanosecond precision on TIMESTAMP_NS
query I
select epoch_ns(make_timestamp_ns(1732118940123456789))
----
1732118940123456789