Add tests for date_part on columns + timestamps with / without timezones (#13732)
* Add tests for date_part on columns + timestamps with / without timezones
* Add tests from https://github.com/apache/datafusion/pull/13372
* remove trailing whitespace
diff --git a/datafusion/sqllogictest/test_files/expr/date_part.slt b/datafusion/sqllogictest/test_files/expr/date_part.slt
index 1d79ac3..dec796a 100644
--- a/datafusion/sqllogictest/test_files/expr/date_part.slt
+++ b/datafusion/sqllogictest/test_files/expr/date_part.slt
@@ -18,6 +18,190 @@
# Tests for `date_part` and `EXTRACT` (which is a different syntax
# for the same function).
+
+## Begin tests fo rdate_part with columns and timestamp's with timezones
+
+# Source data table has
+# timestamps with millisecond (very common timestamp precision) and nanosecond (maximum precision) timestamps
+statement count 0
+CREATE TABLE source_ts AS
+with t as (values
+ ('2020-01-01T00:00:00+00:00'),
+ ('2021-01-01T00:00:00+00:00'), -- year
+ ('2020-09-01T00:00:00+00:00'), -- month
+ ('2020-01-25T00:00:00+00:00'), -- day
+ ('2020-01-24T00:00:00+00:00'), -- day
+ ('2020-01-01T12:00:00+00:00'), -- hour
+ ('2020-01-01T00:30:00+00:00'), -- minute
+ ('2020-01-01T00:00:30+00:00'), -- second
+ ('2020-01-01T00:00:00.123+00:00'), -- ms
+ ('2020-01-01T00:00:00.123456+00:00'), -- us
+ ('2020-01-01T00:00:00.123456789+00:00') -- ns
+)
+SELECT
+ -- nanoseconds, with no, utc, and local timezone
+ arrow_cast(column1, 'Timestamp(Nanosecond, None)') as ts_nano_no_tz,
+ arrow_cast(column1, 'Timestamp(Nanosecond, Some("UTC"))') as ts_nano_utc,
+ arrow_cast(column1, 'Timestamp(Nanosecond, Some("America/New_York"))') as ts_nano_eastern,
+ -- milliseconds, with no, utc, and local timezone
+ arrow_cast(column1, 'Timestamp(Millisecond, None)') as ts_milli_no_tz,
+ arrow_cast(column1, 'Timestamp(Millisecond, Some("UTC"))') as ts_milli_utc,
+ arrow_cast(column1, 'Timestamp(Millisecond, Some("America/New_York"))') as ts_milli_eastern
+FROM t;
+
+
+query PPPPPP
+SELECT * FROM source_ts;
+----
+2020-01-01T00:00:00 2020-01-01T00:00:00Z 2019-12-31T19:00:00-05:00 2020-01-01T00:00:00 2020-01-01T00:00:00Z 2019-12-31T19:00:00-05:00
+2021-01-01T00:00:00 2021-01-01T00:00:00Z 2020-12-31T19:00:00-05:00 2021-01-01T00:00:00 2021-01-01T00:00:00Z 2020-12-31T19:00:00-05:00
+2020-09-01T00:00:00 2020-09-01T00:00:00Z 2020-08-31T20:00:00-04:00 2020-09-01T00:00:00 2020-09-01T00:00:00Z 2020-08-31T20:00:00-04:00
+2020-01-25T00:00:00 2020-01-25T00:00:00Z 2020-01-24T19:00:00-05:00 2020-01-25T00:00:00 2020-01-25T00:00:00Z 2020-01-24T19:00:00-05:00
+2020-01-24T00:00:00 2020-01-24T00:00:00Z 2020-01-23T19:00:00-05:00 2020-01-24T00:00:00 2020-01-24T00:00:00Z 2020-01-23T19:00:00-05:00
+2020-01-01T12:00:00 2020-01-01T12:00:00Z 2020-01-01T07:00:00-05:00 2020-01-01T12:00:00 2020-01-01T12:00:00Z 2020-01-01T07:00:00-05:00
+2020-01-01T00:30:00 2020-01-01T00:30:00Z 2019-12-31T19:30:00-05:00 2020-01-01T00:30:00 2020-01-01T00:30:00Z 2019-12-31T19:30:00-05:00
+2020-01-01T00:00:30 2020-01-01T00:00:30Z 2019-12-31T19:00:30-05:00 2020-01-01T00:00:30 2020-01-01T00:00:30Z 2019-12-31T19:00:30-05:00
+2020-01-01T00:00:00.123 2020-01-01T00:00:00.123Z 2019-12-31T19:00:00.123-05:00 2020-01-01T00:00:00.123 2020-01-01T00:00:00.123Z 2019-12-31T19:00:00.123-05:00
+2020-01-01T00:00:00.123456 2020-01-01T00:00:00.123456Z 2019-12-31T19:00:00.123456-05:00 2020-01-01T00:00:00.123 2020-01-01T00:00:00.123Z 2019-12-31T19:00:00.123-05:00
+2020-01-01T00:00:00.123456789 2020-01-01T00:00:00.123456789Z 2019-12-31T19:00:00.123456789-05:00 2020-01-01T00:00:00.123 2020-01-01T00:00:00.123Z 2019-12-31T19:00:00.123-05:00
+
+# date_part (year) with columns and explicit timestamp
+query IIIIII
+SELECT date_part('year', ts_nano_no_tz), date_part('year', ts_nano_utc), date_part('year', ts_nano_eastern), date_part('year', ts_milli_no_tz), date_part('year', ts_milli_utc), date_part('year', ts_milli_eastern) FROM source_ts;
+----
+2020 2020 2019 2020 2020 2019
+2021 2021 2020 2021 2021 2020
+2020 2020 2020 2020 2020 2020
+2020 2020 2020 2020 2020 2020
+2020 2020 2020 2020 2020 2020
+2020 2020 2020 2020 2020 2020
+2020 2020 2019 2020 2020 2019
+2020 2020 2019 2020 2020 2019
+2020 2020 2019 2020 2020 2019
+2020 2020 2019 2020 2020 2019
+2020 2020 2019 2020 2020 2019
+
+# date_part (month)
+query IIIIII
+SELECT date_part('month', ts_nano_no_tz), date_part('month', ts_nano_utc), date_part('month', ts_nano_eastern), date_part('month', ts_milli_no_tz), date_part('month', ts_milli_utc), date_part('month', ts_milli_eastern) FROM source_ts;
+----
+1 1 12 1 1 12
+1 1 12 1 1 12
+9 9 8 9 9 8
+1 1 1 1 1 1
+1 1 1 1 1 1
+1 1 1 1 1 1
+1 1 12 1 1 12
+1 1 12 1 1 12
+1 1 12 1 1 12
+1 1 12 1 1 12
+1 1 12 1 1 12
+
+# date_part (day)
+query IIIIII
+SELECT date_part('day', ts_nano_no_tz), date_part('day', ts_nano_utc), date_part('day', ts_nano_eastern), date_part('day', ts_milli_no_tz), date_part('day', ts_milli_utc), date_part('day', ts_milli_eastern) FROM source_ts;
+----
+1 1 31 1 1 31
+1 1 31 1 1 31
+1 1 31 1 1 31
+25 25 24 25 25 24
+24 24 23 24 24 23
+1 1 1 1 1 1
+1 1 31 1 1 31
+1 1 31 1 1 31
+1 1 31 1 1 31
+1 1 31 1 1 31
+1 1 31 1 1 31
+
+# date_part (hour)
+query IIIIII
+SELECT date_part('hour', ts_nano_no_tz), date_part('hour', ts_nano_utc), date_part('hour', ts_nano_eastern), date_part('hour', ts_milli_no_tz), date_part('hour', ts_milli_utc), date_part('hour', ts_milli_eastern) FROM source_ts;
+----
+0 0 19 0 0 19
+0 0 19 0 0 19
+0 0 20 0 0 20
+0 0 19 0 0 19
+0 0 19 0 0 19
+12 12 7 12 12 7
+0 0 19 0 0 19
+0 0 19 0 0 19
+0 0 19 0 0 19
+0 0 19 0 0 19
+0 0 19 0 0 19
+
+# date_part (minute)
+query IIIIII
+SELECT date_part('minute', ts_nano_no_tz), date_part('minute', ts_nano_utc), date_part('minute', ts_nano_eastern), date_part('minute', ts_milli_no_tz), date_part('minute', ts_milli_utc), date_part('minute', ts_milli_eastern) FROM source_ts;
+----
+0 0 0 0 0 0
+0 0 0 0 0 0
+0 0 0 0 0 0
+0 0 0 0 0 0
+0 0 0 0 0 0
+0 0 0 0 0 0
+30 30 30 30 30 30
+0 0 0 0 0 0
+0 0 0 0 0 0
+0 0 0 0 0 0
+0 0 0 0 0 0
+
+# date_part (second)
+query IIIIII
+SELECT date_part('second', ts_nano_no_tz), date_part('second', ts_nano_utc), date_part('second', ts_nano_eastern), date_part('second', ts_milli_no_tz), date_part('second', ts_milli_utc), date_part('second', ts_milli_eastern) FROM source_ts;
+----
+0 0 0 0 0 0
+0 0 0 0 0 0
+0 0 0 0 0 0
+0 0 0 0 0 0
+0 0 0 0 0 0
+0 0 0 0 0 0
+0 0 0 0 0 0
+30 30 30 30 30 30
+0 0 0 0 0 0
+0 0 0 0 0 0
+0 0 0 0 0 0
+
+# date_part (millisecond)
+query IIIIII
+SELECT date_part('millisecond', ts_nano_no_tz), date_part('millisecond', ts_nano_utc), date_part('millisecond', ts_nano_eastern), date_part('millisecond', ts_milli_no_tz), date_part('millisecond', ts_milli_utc), date_part('millisecond', ts_milli_eastern) FROM source_ts;
+----
+0 0 0 0 0 0
+0 0 0 0 0 0
+0 0 0 0 0 0
+0 0 0 0 0 0
+0 0 0 0 0 0
+0 0 0 0 0 0
+0 0 0 0 0 0
+30000 30000 30000 30000 30000 30000
+123 123 123 123 123 123
+123 123 123 123 123 123
+123 123 123 123 123 123
+
+# date_part (microsecond)
+query IIIIII
+SELECT date_part('microsecond', ts_nano_no_tz), date_part('microsecond', ts_nano_utc), date_part('microsecond', ts_nano_eastern), date_part('microsecond', ts_milli_no_tz), date_part('microsecond', ts_milli_utc), date_part('microsecond', ts_milli_eastern) FROM source_ts;
+----
+0 0 0 0 0 0
+0 0 0 0 0 0
+0 0 0 0 0 0
+0 0 0 0 0 0
+0 0 0 0 0 0
+0 0 0 0 0 0
+0 0 0 0 0 0
+30000000 30000000 30000000 30000000 30000000 30000000
+123000 123000 123000 123000 123000 123000
+123456 123456 123456 123000 123000 123000
+123456 123456 123456 123000 123000 123000
+
+### Cleanup
+statement ok
+drop table source_ts;
+
+
+
+## "Unit style" tests for types and units on scalar values
+
+
query error
SELECT EXTRACT("'''year'''" FROM timestamp '2020-09-08T12:00:00+00:00')
@@ -529,11 +713,21 @@
14
query I
+select extract(second from timestamp '2024-08-09T12:13:14')
+----
+14
+
+query I
select extract(seconds from '2024-08-09T12:13:14')
----
14
query I
+select extract(seconds from timestamp '2024-08-09T12:13:14')
+----
+14
+
+query I
SELECT extract(second from arrow_cast('23:32:50.123456789'::time, 'Time64(Nanosecond)'))
----
50