This is an automated email from the ASF dual-hosted git repository.
alamb pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/datafusion.git
The following commit(s) were added to refs/heads/main by this push:
new 28e4c64dc7 minor: Extract tests for `EXTRACT` AND `date_part` to their
own file (#13731)
28e4c64dc7 is described below
commit 28e4c64dc738227cd6a4cdf7db48685338582c04
Author: Andrew Lamb <[email protected]>
AuthorDate: Wed Dec 11 09:49:23 2024 -0500
minor: Extract tests for `EXTRACT` AND `date_part` to their own file
(#13731)
---
datafusion/sqllogictest/test_files/expr.slt | 861 --------------------
.../sqllogictest/test_files/expr/date_part.slt | 878 +++++++++++++++++++++
2 files changed, 878 insertions(+), 861 deletions(-)
diff --git a/datafusion/sqllogictest/test_files/expr.slt
b/datafusion/sqllogictest/test_files/expr.slt
index 9b8dfc2186..2306eda77d 100644
--- a/datafusion/sqllogictest/test_files/expr.slt
+++ b/datafusion/sqllogictest/test_files/expr.slt
@@ -832,867 +832,6 @@ SELECT
----
0 NULL 0 NULL
-# test_extract_date_part
-
-query error
-SELECT EXTRACT("'''year'''" FROM timestamp '2020-09-08T12:00:00+00:00')
-
-query error
-SELECT EXTRACT("'year'" FROM timestamp '2020-09-08T12:00:00+00:00')
-
-query I
-SELECT date_part('YEAR', CAST('2000-01-01' AS DATE))
-----
-2000
-
-query I
-SELECT EXTRACT(year FROM timestamp '2020-09-08T12:00:00+00:00')
-----
-2020
-
-query I
-SELECT EXTRACT("year" FROM timestamp '2020-09-08T12:00:00+00:00')
-----
-2020
-
-query I
-SELECT EXTRACT('year' FROM timestamp '2020-09-08T12:00:00+00:00')
-----
-2020
-
-query I
-SELECT date_part('QUARTER', CAST('2000-01-01' AS DATE))
-----
-1
-
-query I
-SELECT EXTRACT(quarter FROM to_timestamp('2020-09-08T12:00:00+00:00'))
-----
-3
-
-query I
-SELECT EXTRACT("quarter" FROM to_timestamp('2020-09-08T12:00:00+00:00'))
-----
-3
-
-query I
-SELECT EXTRACT('quarter' FROM to_timestamp('2020-09-08T12:00:00+00:00'))
-----
-3
-
-query I
-SELECT date_part('MONTH', CAST('2000-01-01' AS DATE))
-----
-1
-
-query I
-SELECT EXTRACT(month FROM to_timestamp('2020-09-08T12:00:00+00:00'))
-----
-9
-
-query I
-SELECT EXTRACT("month" FROM to_timestamp('2020-09-08T12:00:00+00:00'))
-----
-9
-
-query I
-SELECT EXTRACT('month' FROM to_timestamp('2020-09-08T12:00:00+00:00'))
-----
-9
-
-query I
-SELECT date_part('WEEK', CAST('2003-01-01' AS DATE))
-----
-1
-
-query I
-SELECT EXTRACT(WEEK FROM to_timestamp('2020-09-08T12:00:00+00:00'))
-----
-37
-
-query I
-SELECT EXTRACT("WEEK" FROM to_timestamp('2020-09-08T12:00:00+00:00'))
-----
-37
-
-query I
-SELECT EXTRACT('WEEK' FROM to_timestamp('2020-09-08T12:00:00+00:00'))
-----
-37
-
-query I
-SELECT date_part('DAY', CAST('2000-01-01' AS DATE))
-----
-1
-
-query I
-SELECT EXTRACT(day FROM to_timestamp('2020-09-08T12:00:00+00:00'))
-----
-8
-
-query I
-SELECT EXTRACT("day" FROM to_timestamp('2020-09-08T12:00:00+00:00'))
-----
-8
-
-query I
-SELECT EXTRACT('day' FROM to_timestamp('2020-09-08T12:00:00+00:00'))
-----
-8
-
-query I
-SELECT date_part('DOY', CAST('2000-01-01' AS DATE))
-----
-1
-
-query I
-SELECT EXTRACT(doy FROM to_timestamp('2020-09-08T12:00:00+00:00'))
-----
-252
-
-query I
-SELECT EXTRACT("doy" FROM to_timestamp('2020-09-08T12:00:00+00:00'))
-----
-252
-
-query I
-SELECT EXTRACT('doy' FROM to_timestamp('2020-09-08T12:00:00+00:00'))
-----
-252
-
-query I
-SELECT date_part('DOW', CAST('2000-01-01' AS DATE))
-----
-6
-
-query I
-SELECT EXTRACT(dow FROM to_timestamp('2020-09-08T12:00:00+00:00'))
-----
-2
-
-query I
-SELECT EXTRACT("dow" FROM to_timestamp('2020-09-08T12:00:00+00:00'))
-----
-2
-
-query I
-SELECT EXTRACT('dow' FROM to_timestamp('2020-09-08T12:00:00+00:00'))
-----
-2
-
-query I
-SELECT date_part('HOUR', CAST('2000-01-01' AS DATE))
-----
-0
-
-query I
-SELECT EXTRACT(hour FROM to_timestamp('2020-09-08T12:03:03+00:00'))
-----
-12
-
-query I
-SELECT EXTRACT("hour" FROM to_timestamp('2020-09-08T12:03:03+00:00'))
-----
-12
-
-query I
-SELECT EXTRACT('hour' FROM to_timestamp('2020-09-08T12:03:03+00:00'))
-----
-12
-
-query I
-SELECT EXTRACT(minute FROM to_timestamp('2020-09-08T12:12:00+00:00'))
-----
-12
-
-query I
-SELECT EXTRACT("minute" FROM to_timestamp('2020-09-08T12:12:00+00:00'))
-----
-12
-
-query I
-SELECT EXTRACT('minute' FROM to_timestamp('2020-09-08T12:12:00+00:00'))
-----
-12
-
-query I
-SELECT date_part('minute', to_timestamp('2020-09-08T12:12:00+00:00'))
-----
-12
-
-# make sure the return type is integer
-query T
-SELECT arrow_typeof(date_part('minute',
to_timestamp('2020-09-08T12:12:00+00:00')))
-----
-Int32
-
-query I
-SELECT EXTRACT(second FROM timestamp '2020-09-08T12:00:12.12345678+00:00')
-----
-12
-
-query I
-SELECT EXTRACT(millisecond FROM timestamp '2020-09-08T12:00:12.12345678+00:00')
-----
-12123
-
-query I
-SELECT EXTRACT(microsecond FROM timestamp '2020-09-08T12:00:12.12345678+00:00')
-----
-12123456
-
-query error DataFusion error: Internal error: unit Nanosecond not supported
-SELECT EXTRACT(nanosecond FROM timestamp '2020-09-08T12:00:12.12345678+00:00')
-
-query I
-SELECT EXTRACT("second" FROM timestamp '2020-09-08T12:00:12.12345678+00:00')
-----
-12
-
-query I
-SELECT EXTRACT("millisecond" FROM timestamp
'2020-09-08T12:00:12.12345678+00:00')
-----
-12123
-
-query I
-SELECT EXTRACT("microsecond" FROM timestamp
'2020-09-08T12:00:12.12345678+00:00')
-----
-12123456
-
-query error DataFusion error: Internal error: unit Nanosecond not supported
-SELECT EXTRACT("nanosecond" FROM timestamp
'2020-09-08T12:00:12.12345678+00:00')
-
-query I
-SELECT EXTRACT('second' FROM timestamp '2020-09-08T12:00:12.12345678+00:00')
-----
-12
-
-query I
-SELECT EXTRACT('millisecond' FROM timestamp
'2020-09-08T12:00:12.12345678+00:00')
-----
-12123
-
-query I
-SELECT EXTRACT('microsecond' FROM timestamp
'2020-09-08T12:00:12.12345678+00:00')
-----
-12123456
-
-query error DataFusion error: Internal error: unit Nanosecond not supported
-SELECT EXTRACT('nanosecond' FROM timestamp
'2020-09-08T12:00:12.12345678+00:00')
-
-
-# Keep precision when coercing Utf8 to Timestamp
-query I
-SELECT date_part('second', timestamp '2020-09-08T12:00:12.12345678+00:00')
-----
-12
-
-query I
-SELECT date_part('millisecond', timestamp '2020-09-08T12:00:12.12345678+00:00')
-----
-12123
-
-query I
-SELECT date_part('microsecond', timestamp '2020-09-08T12:00:12.12345678+00:00')
-----
-12123456
-
-query error DataFusion error: Internal error: unit Nanosecond not supported
-SELECT date_part('nanosecond', timestamp '2020-09-08T12:00:12.12345678+00:00')
-
-
-query I
-SELECT date_part('second', '2020-09-08T12:00:12.12345678+00:00')
-----
-12
-
-query I
-SELECT date_part('millisecond', '2020-09-08T12:00:12.12345678+00:00')
-----
-12123
-
-query I
-SELECT date_part('microsecond', '2020-09-08T12:00:12.12345678+00:00')
-----
-12123456
-
-query error DataFusion error: Internal error: unit Nanosecond not supported
-SELECT date_part('nanosecond', '2020-09-08T12:00:12.12345678+00:00')
-
-# test_date_part_time
-
-## time32 seconds
-query I
-SELECT date_part('hour', arrow_cast('23:32:50'::time, 'Time32(Second)'))
-----
-23
-
-query I
-SELECT extract(hour from arrow_cast('23:32:50'::time, 'Time32(Second)'))
-----
-23
-
-query I
-SELECT date_part('minute', arrow_cast('23:32:50'::time, 'Time32(Second)'))
-----
-32
-
-query I
-SELECT extract(minute from arrow_cast('23:32:50'::time, 'Time32(Second)'))
-----
-32
-
-query I
-SELECT date_part('second', arrow_cast('23:32:50'::time, 'Time32(Second)'))
-----
-50
-
-query I
-SELECT extract(second from arrow_cast('23:32:50'::time, 'Time32(Second)'))
-----
-50
-
-query I
-SELECT date_part('millisecond', arrow_cast('23:32:50'::time, 'Time32(Second)'))
-----
-50000
-
-query I
-SELECT extract(millisecond from arrow_cast('23:32:50'::time, 'Time32(Second)'))
-----
-50000
-
-query I
-SELECT date_part('microsecond', arrow_cast('23:32:50'::time, 'Time32(Second)'))
-----
-50000000
-
-query I
-SELECT extract(microsecond from arrow_cast('23:32:50'::time, 'Time32(Second)'))
-----
-50000000
-
-query error DataFusion error: Internal error: unit Nanosecond not supported
-SELECT extract(nanosecond from arrow_cast('23:32:50'::time, 'Time32(Second)'))
-
-query R
-SELECT date_part('epoch', arrow_cast('23:32:50'::time, 'Time32(Second)'))
-----
-84770
-
-query R
-SELECT extract(epoch from arrow_cast('23:32:50'::time, 'Time32(Second)'))
-----
-84770
-
-## time32 milliseconds
-query I
-SELECT date_part('hour', arrow_cast('23:32:50.123'::time,
'Time32(Millisecond)'))
-----
-23
-
-query I
-SELECT extract(hour from arrow_cast('23:32:50.123'::time,
'Time32(Millisecond)'))
-----
-23
-
-query I
-SELECT date_part('minute', arrow_cast('23:32:50.123'::time,
'Time32(Millisecond)'))
-----
-32
-
-query I
-SELECT extract(minute from arrow_cast('23:32:50.123'::time,
'Time32(Millisecond)'))
-----
-32
-
-query I
-SELECT date_part('second', arrow_cast('23:32:50.123'::time,
'Time32(Millisecond)'))
-----
-50
-
-query I
-SELECT extract(second from arrow_cast('23:32:50.123'::time,
'Time32(Millisecond)'))
-----
-50
-
-query I
-SELECT date_part('millisecond', arrow_cast('23:32:50.123'::time,
'Time32(Millisecond)'))
-----
-50123
-
-query I
-SELECT extract(millisecond from arrow_cast('23:32:50.123'::time,
'Time32(Millisecond)'))
-----
-50123
-
-query I
-SELECT date_part('microsecond', arrow_cast('23:32:50.123'::time,
'Time32(Millisecond)'))
-----
-50123000
-
-query I
-SELECT extract(microsecond from arrow_cast('23:32:50.123'::time,
'Time32(Millisecond)'))
-----
-50123000
-
-query error DataFusion error: Internal error: unit Nanosecond not supported
-SELECT extract(nanosecond from arrow_cast('23:32:50.123'::time,
'Time32(Millisecond)'))
-
-query R
-SELECT date_part('epoch', arrow_cast('23:32:50.123'::time,
'Time32(Millisecond)'))
-----
-84770.123
-
-query R
-SELECT extract(epoch from arrow_cast('23:32:50.123'::time,
'Time32(Millisecond)'))
-----
-84770.123
-
-## time64 microseconds
-query I
-SELECT date_part('hour', arrow_cast('23:32:50.123456'::time,
'Time64(Microsecond)'))
-----
-23
-
-query I
-SELECT extract(hour from arrow_cast('23:32:50.123456'::time,
'Time64(Microsecond)'))
-----
-23
-
-query I
-SELECT date_part('minute', arrow_cast('23:32:50.123456'::time,
'Time64(Microsecond)'))
-----
-32
-
-query I
-SELECT extract(minute from arrow_cast('23:32:50.123456'::time,
'Time64(Microsecond)'))
-----
-32
-
-query I
-SELECT date_part('second', arrow_cast('23:32:50.123456'::time,
'Time64(Microsecond)'))
-----
-50
-
-query I
-SELECT extract(second from arrow_cast('23:32:50.123456'::time,
'Time64(Microsecond)'))
-----
-50
-
-query I
-SELECT date_part('millisecond', arrow_cast('23:32:50.123456'::time,
'Time64(Microsecond)'))
-----
-50123
-
-query I
-SELECT extract(millisecond from arrow_cast('23:32:50.123456'::time,
'Time64(Microsecond)'))
-----
-50123
-
-query I
-SELECT date_part('microsecond', arrow_cast('23:32:50.123456'::time,
'Time64(Microsecond)'))
-----
-50123456
-
-query I
-SELECT extract(microsecond from arrow_cast('23:32:50.123456'::time,
'Time64(Microsecond)'))
-----
-50123456
-
-query error DataFusion error: Internal error: unit Nanosecond not supported
-SELECT extract(nanosecond from arrow_cast('23:32:50.123456'::time,
'Time64(Microsecond)'))
-
-query R
-SELECT date_part('epoch', arrow_cast('23:32:50.123456'::time,
'Time64(Microsecond)'))
-----
-84770.123456
-
-query R
-SELECT extract(epoch from arrow_cast('23:32:50.123456'::time,
'Time64(Microsecond)'))
-----
-84770.123456
-
-## time64 nanoseconds
-query I
-SELECT date_part('hour', arrow_cast('23:32:50.123456789'::time,
'Time64(Nanosecond)'))
-----
-23
-
-query I
-SELECT extract(hour from arrow_cast('23:32:50.123456789'::time,
'Time64(Nanosecond)'))
-----
-23
-
-query I
-SELECT date_part('minute', arrow_cast('23:32:50.123456789'::time,
'Time64(Nanosecond)'))
-----
-32
-
-query I
-SELECT extract(minute from arrow_cast('23:32:50.123456789'::time,
'Time64(Nanosecond)'))
-----
-32
-
-query I
-SELECT date_part('second', arrow_cast('23:32:50.123456789'::time,
'Time64(Nanosecond)'))
-----
-50
-
-query I
-select extract(second from '2024-08-09T12:13:14')
-----
-14
-
-query I
-select extract(seconds from '2024-08-09T12:13:14')
-----
-14
-
-query I
-SELECT extract(second from arrow_cast('23:32:50.123456789'::time,
'Time64(Nanosecond)'))
-----
-50
-
-query I
-SELECT date_part('millisecond', arrow_cast('23:32:50.123456789'::time,
'Time64(Nanosecond)'))
-----
-50123
-
-query I
-SELECT extract(millisecond from arrow_cast('23:32:50.123456789'::time,
'Time64(Nanosecond)'))
-----
-50123
-
-# just some floating point stuff happening in the result here
-query I
-SELECT date_part('microsecond', arrow_cast('23:32:50.123456789'::time,
'Time64(Nanosecond)'))
-----
-50123456
-
-query I
-SELECT extract(microsecond from arrow_cast('23:32:50.123456789'::time,
'Time64(Nanosecond)'))
-----
-50123456
-
-query I
-SELECT extract(us from arrow_cast('23:32:50.123456789'::time,
'Time64(Nanosecond)'))
-----
-50123456
-
-query error DataFusion error: Internal error: unit Nanosecond not supported
-SELECT date_part('nanosecond', arrow_cast('23:32:50.123456789'::time,
'Time64(Nanosecond)'))
-
-query R
-SELECT date_part('epoch', arrow_cast('23:32:50.123456789'::time,
'Time64(Nanosecond)'))
-----
-84770.123456789
-
-query R
-SELECT extract(epoch from arrow_cast('23:32:50.123456789'::time,
'Time64(Nanosecond)'))
-----
-84770.123456789
-
-# test_extract_epoch
-
-query R
-SELECT extract(epoch from '1870-01-01T07:29:10.256'::timestamp)
-----
--3155646649.744
-
-query R
-SELECT extract(epoch from '2000-01-01T00:00:00.000'::timestamp)
-----
-946684800
-
-query R
-SELECT extract(epoch from to_timestamp('2000-01-01T00:00:00+00:00'))
-----
-946684800
-
-query R
-SELECT extract(epoch from NULL::timestamp)
-----
-NULL
-
-query R
-SELECT extract(epoch from arrow_cast('1970-01-01', 'Date32'))
-----
-0
-
-query R
-SELECT extract(epoch from arrow_cast('1970-01-02', 'Date32'))
-----
-86400
-
-query R
-SELECT extract(epoch from arrow_cast('1970-01-11', 'Date32'))
-----
-864000
-
-query R
-SELECT extract(epoch from arrow_cast('1969-12-31', 'Date32'))
-----
--86400
-
-query R
-SELECT extract(epoch from arrow_cast('1970-01-01', 'Date64'))
-----
-0
-
-query R
-SELECT extract(epoch from arrow_cast('1970-01-02', 'Date64'))
-----
-86400
-
-query R
-SELECT extract(epoch from arrow_cast('1970-01-11', 'Date64'))
-----
-864000
-
-query R
-SELECT extract(epoch from arrow_cast('1969-12-31', 'Date64'))
-----
--86400
-
-# test_extract_interval
-
-query I
-SELECT extract(year from arrow_cast('10 years', 'Interval(YearMonth)'))
-----
-10
-
-query I
-SELECT extract(month from arrow_cast('10 years', 'Interval(YearMonth)'))
-----
-0
-
-query I
-SELECT extract(year from arrow_cast('10 months', 'Interval(YearMonth)'))
-----
-0
-
-query I
-SELECT extract(month from arrow_cast('10 months', 'Interval(YearMonth)'))
-----
-10
-
-query I
-SELECT extract(year from arrow_cast('20 months', 'Interval(YearMonth)'))
-----
-1
-
-query I
-SELECT extract(month from arrow_cast('20 months', 'Interval(YearMonth)'))
-----
-8
-
-query error DataFusion error: Arrow error: Compute error: Year does not
support: Interval\(DayTime\)
-SELECT extract(year from arrow_cast('10 days', 'Interval(DayTime)'))
-
-query error DataFusion error: Arrow error: Compute error: Month does not
support: Interval\(DayTime\)
-SELECT extract(month from arrow_cast('10 days', 'Interval(DayTime)'))
-
-query I
-SELECT extract(day from arrow_cast('10 days', 'Interval(DayTime)'))
-----
-10
-
-query I
-SELECT extract(day from arrow_cast('14400 minutes', 'Interval(DayTime)'))
-----
-0
-
-query I
-SELECT extract(minute from arrow_cast('14400 minutes', 'Interval(DayTime)'))
-----
-14400
-
-query I
-SELECT extract(second from arrow_cast('5.1 seconds', 'Interval(DayTime)'))
-----
-5
-
-query I
-SELECT extract(second from arrow_cast('14400 minutes', 'Interval(DayTime)'))
-----
-864000
-
-query I
-SELECT extract(second from arrow_cast('2 months', 'Interval(MonthDayNano)'))
-----
-0
-
-query I
-SELECT extract(second from arrow_cast('2 days', 'Interval(MonthDayNano)'))
-----
-0
-
-query I
-SELECT extract(second from arrow_cast('2 seconds', 'Interval(MonthDayNano)'))
-----
-2
-
-query I
-SELECT extract(seconds from arrow_cast('2 seconds', 'Interval(MonthDayNano)'))
-----
-2
-
-query R
-SELECT extract(epoch from arrow_cast('2 seconds', 'Interval(MonthDayNano)'))
-----
-2
-
-query I
-SELECT extract(milliseconds from arrow_cast('2 seconds',
'Interval(MonthDayNano)'))
-----
-2000
-
-query I
-SELECT extract(second from arrow_cast('2030 milliseconds',
'Interval(MonthDayNano)'))
-----
-2
-
-query I
-SELECT extract(second from arrow_cast(NULL, 'Interval(MonthDayNano)'))
-----
-NULL
-
-statement ok
-create table t (id int, i interval) as values
- (0, interval '5 months 1 day 10 nanoseconds'),
- (1, interval '1 year 3 months'),
- (2, interval '3 days 2 milliseconds'),
- (3, interval '2 seconds'),
- (4, interval '8 months'),
- (5, NULL);
-
-query III
-select
- id,
- extract(second from i),
- extract(month from i)
-from t
-order by id;
-----
-0 0 5
-1 0 15
-2 0 0
-3 2 0
-4 0 8
-5 NULL NULL
-
-statement ok
-drop table t;
-
-# test_extract_duration
-
-query I
-SELECT extract(second from arrow_cast(2, 'Duration(Second)'))
-----
-2
-
-query I
-SELECT extract(seconds from arrow_cast(2, 'Duration(Second)'))
-----
-2
-
-query R
-SELECT extract(epoch from arrow_cast(2, 'Duration(Second)'))
-----
-2
-
-query I
-SELECT extract(millisecond from arrow_cast(2, 'Duration(Second)'))
-----
-2000
-
-query I
-SELECT extract(second from arrow_cast(2, 'Duration(Millisecond)'))
-----
-0
-
-query I
-SELECT extract(second from arrow_cast(2002, 'Duration(Millisecond)'))
-----
-2
-
-query I
-SELECT extract(millisecond from arrow_cast(2002, 'Duration(Millisecond)'))
-----
-2002
-
-query I
-SELECT extract(day from arrow_cast(864000, 'Duration(Second)'))
-----
-10
-
-query error DataFusion error: Arrow error: Compute error: Month does not
support: Duration\(Second\)
-SELECT extract(month from arrow_cast(864000, 'Duration(Second)'))
-
-query error DataFusion error: Arrow error: Compute error: Year does not
support: Duration\(Second\)
-SELECT extract(year from arrow_cast(864000, 'Duration(Second)'))
-
-query I
-SELECT extract(day from arrow_cast(NULL, 'Duration(Second)'))
-----
-NULL
-
-# test_extract_date_part_func
-
-query B
-SELECT (date_part('year', now()) = EXTRACT(year FROM now()))
-----
-true
-
-query B
-SELECT (date_part('quarter', now()) = EXTRACT(quarter FROM now()))
-----
-true
-
-query B
-SELECT (date_part('month', now()) = EXTRACT(month FROM now()))
-----
-true
-
-query B
-SELECT (date_part('week', now()) = EXTRACT(week FROM now()))
-----
-true
-
-query B
-SELECT (date_part('day', now()) = EXTRACT(day FROM now()))
-----
-true
-
-query B
-SELECT (date_part('hour', now()) = EXTRACT(hour FROM now()))
-----
-true
-
-query B
-SELECT (date_part('minute', now()) = EXTRACT(minute FROM now()))
-----
-true
-
-query B
-SELECT (date_part('second', now()) = EXTRACT(second FROM now()))
-----
-true
-
-query B
-SELECT (date_part('millisecond', now()) = EXTRACT(millisecond FROM now()))
-----
-true
-
-query B
-SELECT (date_part('microsecond', now()) = EXTRACT(microsecond FROM now()))
-----
-true
-
-query error DataFusion error: Internal error: unit Nanosecond not supported
-SELECT (date_part('nanosecond', now()) = EXTRACT(nanosecond FROM now()))
-
query B
SELECT 'a' IN ('a','b')
----
diff --git a/datafusion/sqllogictest/test_files/expr/date_part.slt
b/datafusion/sqllogictest/test_files/expr/date_part.slt
new file mode 100644
index 0000000000..cec80a165f
--- /dev/null
+++ b/datafusion/sqllogictest/test_files/expr/date_part.slt
@@ -0,0 +1,878 @@
+# Licensed to the Apache Software Foundation (ASF) under one
+# or more contributor license agreements. See the NOTICE file
+# distributed with this work for additional information
+# regarding copyright ownership. The ASF licenses this file
+# to you under the Apache License, Version 2.0 (the
+# "License"); you may not use this file except in compliance
+# with the License. You may obtain a copy of the License at
+
+# http://www.apache.org/licenses/LICENSE-2.0
+
+# Unless required by applicable law or agreed to in writing,
+# software distributed under the License is distributed on an
+# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+# KIND, either express or implied. See the License for the
+# specific language governing permissions and limitations
+# under the License.
+
+# Tests for `date_part` and `EXTRACT` (which is a different syntax
+# for the same function).
+
+query error
+SELECT EXTRACT("'''year'''" FROM timestamp '2020-09-08T12:00:00+00:00')
+
+query error
+SELECT EXTRACT("'year'" FROM timestamp '2020-09-08T12:00:00+00:00')
+
+query I
+SELECT date_part('YEAR', CAST('2000-01-01' AS DATE))
+----
+2000
+
+query I
+SELECT EXTRACT(year FROM timestamp '2020-09-08T12:00:00+00:00')
+----
+2020
+
+query I
+SELECT EXTRACT("year" FROM timestamp '2020-09-08T12:00:00+00:00')
+----
+2020
+
+query I
+SELECT EXTRACT('year' FROM timestamp '2020-09-08T12:00:00+00:00')
+----
+2020
+
+query I
+SELECT date_part('QUARTER', CAST('2000-01-01' AS DATE))
+----
+1
+
+query I
+SELECT EXTRACT(quarter FROM to_timestamp('2020-09-08T12:00:00+00:00'))
+----
+3
+
+query I
+SELECT EXTRACT("quarter" FROM to_timestamp('2020-09-08T12:00:00+00:00'))
+----
+3
+
+query I
+SELECT EXTRACT('quarter' FROM to_timestamp('2020-09-08T12:00:00+00:00'))
+----
+3
+
+query I
+SELECT date_part('MONTH', CAST('2000-01-01' AS DATE))
+----
+1
+
+query I
+SELECT EXTRACT(month FROM to_timestamp('2020-09-08T12:00:00+00:00'))
+----
+9
+
+query I
+SELECT EXTRACT("month" FROM to_timestamp('2020-09-08T12:00:00+00:00'))
+----
+9
+
+query I
+SELECT EXTRACT('month' FROM to_timestamp('2020-09-08T12:00:00+00:00'))
+----
+9
+
+query I
+SELECT date_part('WEEK', CAST('2003-01-01' AS DATE))
+----
+1
+
+query I
+SELECT EXTRACT(WEEK FROM to_timestamp('2020-09-08T12:00:00+00:00'))
+----
+37
+
+query I
+SELECT EXTRACT("WEEK" FROM to_timestamp('2020-09-08T12:00:00+00:00'))
+----
+37
+
+query I
+SELECT EXTRACT('WEEK' FROM to_timestamp('2020-09-08T12:00:00+00:00'))
+----
+37
+
+query I
+SELECT date_part('DAY', CAST('2000-01-01' AS DATE))
+----
+1
+
+query I
+SELECT EXTRACT(day FROM to_timestamp('2020-09-08T12:00:00+00:00'))
+----
+8
+
+query I
+SELECT EXTRACT("day" FROM to_timestamp('2020-09-08T12:00:00+00:00'))
+----
+8
+
+query I
+SELECT EXTRACT('day' FROM to_timestamp('2020-09-08T12:00:00+00:00'))
+----
+8
+
+query I
+SELECT date_part('DOY', CAST('2000-01-01' AS DATE))
+----
+1
+
+query I
+SELECT EXTRACT(doy FROM to_timestamp('2020-09-08T12:00:00+00:00'))
+----
+252
+
+query I
+SELECT EXTRACT("doy" FROM to_timestamp('2020-09-08T12:00:00+00:00'))
+----
+252
+
+query I
+SELECT EXTRACT('doy' FROM to_timestamp('2020-09-08T12:00:00+00:00'))
+----
+252
+
+query I
+SELECT date_part('DOW', CAST('2000-01-01' AS DATE))
+----
+6
+
+query I
+SELECT EXTRACT(dow FROM to_timestamp('2020-09-08T12:00:00+00:00'))
+----
+2
+
+query I
+SELECT EXTRACT("dow" FROM to_timestamp('2020-09-08T12:00:00+00:00'))
+----
+2
+
+query I
+SELECT EXTRACT('dow' FROM to_timestamp('2020-09-08T12:00:00+00:00'))
+----
+2
+
+query I
+SELECT date_part('HOUR', CAST('2000-01-01' AS DATE))
+----
+0
+
+query I
+SELECT EXTRACT(hour FROM to_timestamp('2020-09-08T12:03:03+00:00'))
+----
+12
+
+query I
+SELECT EXTRACT("hour" FROM to_timestamp('2020-09-08T12:03:03+00:00'))
+----
+12
+
+query I
+SELECT EXTRACT('hour' FROM to_timestamp('2020-09-08T12:03:03+00:00'))
+----
+12
+
+query I
+SELECT EXTRACT(minute FROM to_timestamp('2020-09-08T12:12:00+00:00'))
+----
+12
+
+query I
+SELECT EXTRACT("minute" FROM to_timestamp('2020-09-08T12:12:00+00:00'))
+----
+12
+
+query I
+SELECT EXTRACT('minute' FROM to_timestamp('2020-09-08T12:12:00+00:00'))
+----
+12
+
+query I
+SELECT date_part('minute', to_timestamp('2020-09-08T12:12:00+00:00'))
+----
+12
+
+# make sure the return type is integer
+query T
+SELECT arrow_typeof(date_part('minute',
to_timestamp('2020-09-08T12:12:00+00:00')))
+----
+Int32
+
+query I
+SELECT EXTRACT(second FROM timestamp '2020-09-08T12:00:12.12345678+00:00')
+----
+12
+
+query I
+SELECT EXTRACT(millisecond FROM timestamp '2020-09-08T12:00:12.12345678+00:00')
+----
+12123
+
+query I
+SELECT EXTRACT(microsecond FROM timestamp '2020-09-08T12:00:12.12345678+00:00')
+----
+12123456
+
+query error DataFusion error: Internal error: unit Nanosecond not supported
+SELECT EXTRACT(nanosecond FROM timestamp '2020-09-08T12:00:12.12345678+00:00')
+
+query I
+SELECT EXTRACT("second" FROM timestamp '2020-09-08T12:00:12.12345678+00:00')
+----
+12
+
+query I
+SELECT EXTRACT("millisecond" FROM timestamp
'2020-09-08T12:00:12.12345678+00:00')
+----
+12123
+
+query I
+SELECT EXTRACT("microsecond" FROM timestamp
'2020-09-08T12:00:12.12345678+00:00')
+----
+12123456
+
+query error DataFusion error: Internal error: unit Nanosecond not supported
+SELECT EXTRACT("nanosecond" FROM timestamp
'2020-09-08T12:00:12.12345678+00:00')
+
+query I
+SELECT EXTRACT('second' FROM timestamp '2020-09-08T12:00:12.12345678+00:00')
+----
+12
+
+query I
+SELECT EXTRACT('millisecond' FROM timestamp
'2020-09-08T12:00:12.12345678+00:00')
+----
+12123
+
+query I
+SELECT EXTRACT('microsecond' FROM timestamp
'2020-09-08T12:00:12.12345678+00:00')
+----
+12123456
+
+query error DataFusion error: Internal error: unit Nanosecond not supported
+SELECT EXTRACT('nanosecond' FROM timestamp
'2020-09-08T12:00:12.12345678+00:00')
+
+
+# Keep precision when coercing Utf8 to Timestamp
+query I
+SELECT date_part('second', timestamp '2020-09-08T12:00:12.12345678+00:00')
+----
+12
+
+query I
+SELECT date_part('millisecond', timestamp '2020-09-08T12:00:12.12345678+00:00')
+----
+12123
+
+query I
+SELECT date_part('microsecond', timestamp '2020-09-08T12:00:12.12345678+00:00')
+----
+12123456
+
+query error DataFusion error: Internal error: unit Nanosecond not supported
+SELECT date_part('nanosecond', timestamp '2020-09-08T12:00:12.12345678+00:00')
+
+
+query I
+SELECT date_part('second', '2020-09-08T12:00:12.12345678+00:00')
+----
+12
+
+query I
+SELECT date_part('millisecond', '2020-09-08T12:00:12.12345678+00:00')
+----
+12123
+
+query I
+SELECT date_part('microsecond', '2020-09-08T12:00:12.12345678+00:00')
+----
+12123456
+
+query error DataFusion error: Internal error: unit Nanosecond not supported
+SELECT date_part('nanosecond', '2020-09-08T12:00:12.12345678+00:00')
+
+# test_date_part_time
+
+## time32 seconds
+query I
+SELECT date_part('hour', arrow_cast('23:32:50'::time, 'Time32(Second)'))
+----
+23
+
+query I
+SELECT extract(hour from arrow_cast('23:32:50'::time, 'Time32(Second)'))
+----
+23
+
+query I
+SELECT date_part('minute', arrow_cast('23:32:50'::time, 'Time32(Second)'))
+----
+32
+
+query I
+SELECT extract(minute from arrow_cast('23:32:50'::time, 'Time32(Second)'))
+----
+32
+
+query I
+SELECT date_part('second', arrow_cast('23:32:50'::time, 'Time32(Second)'))
+----
+50
+
+query I
+SELECT extract(second from arrow_cast('23:32:50'::time, 'Time32(Second)'))
+----
+50
+
+query I
+SELECT date_part('millisecond', arrow_cast('23:32:50'::time, 'Time32(Second)'))
+----
+50000
+
+query I
+SELECT extract(millisecond from arrow_cast('23:32:50'::time, 'Time32(Second)'))
+----
+50000
+
+query I
+SELECT date_part('microsecond', arrow_cast('23:32:50'::time, 'Time32(Second)'))
+----
+50000000
+
+query I
+SELECT extract(microsecond from arrow_cast('23:32:50'::time, 'Time32(Second)'))
+----
+50000000
+
+query error DataFusion error: Internal error: unit Nanosecond not supported
+SELECT extract(nanosecond from arrow_cast('23:32:50'::time, 'Time32(Second)'))
+
+query R
+SELECT date_part('epoch', arrow_cast('23:32:50'::time, 'Time32(Second)'))
+----
+84770
+
+query R
+SELECT extract(epoch from arrow_cast('23:32:50'::time, 'Time32(Second)'))
+----
+84770
+
+## time32 milliseconds
+query I
+SELECT date_part('hour', arrow_cast('23:32:50.123'::time,
'Time32(Millisecond)'))
+----
+23
+
+query I
+SELECT extract(hour from arrow_cast('23:32:50.123'::time,
'Time32(Millisecond)'))
+----
+23
+
+query I
+SELECT date_part('minute', arrow_cast('23:32:50.123'::time,
'Time32(Millisecond)'))
+----
+32
+
+query I
+SELECT extract(minute from arrow_cast('23:32:50.123'::time,
'Time32(Millisecond)'))
+----
+32
+
+query I
+SELECT date_part('second', arrow_cast('23:32:50.123'::time,
'Time32(Millisecond)'))
+----
+50
+
+query I
+SELECT extract(second from arrow_cast('23:32:50.123'::time,
'Time32(Millisecond)'))
+----
+50
+
+query I
+SELECT date_part('millisecond', arrow_cast('23:32:50.123'::time,
'Time32(Millisecond)'))
+----
+50123
+
+query I
+SELECT extract(millisecond from arrow_cast('23:32:50.123'::time,
'Time32(Millisecond)'))
+----
+50123
+
+query I
+SELECT date_part('microsecond', arrow_cast('23:32:50.123'::time,
'Time32(Millisecond)'))
+----
+50123000
+
+query I
+SELECT extract(microsecond from arrow_cast('23:32:50.123'::time,
'Time32(Millisecond)'))
+----
+50123000
+
+query error DataFusion error: Internal error: unit Nanosecond not supported
+SELECT extract(nanosecond from arrow_cast('23:32:50.123'::time,
'Time32(Millisecond)'))
+
+query R
+SELECT date_part('epoch', arrow_cast('23:32:50.123'::time,
'Time32(Millisecond)'))
+----
+84770.123
+
+query R
+SELECT extract(epoch from arrow_cast('23:32:50.123'::time,
'Time32(Millisecond)'))
+----
+84770.123
+
+## time64 microseconds
+query I
+SELECT date_part('hour', arrow_cast('23:32:50.123456'::time,
'Time64(Microsecond)'))
+----
+23
+
+query I
+SELECT extract(hour from arrow_cast('23:32:50.123456'::time,
'Time64(Microsecond)'))
+----
+23
+
+query I
+SELECT date_part('minute', arrow_cast('23:32:50.123456'::time,
'Time64(Microsecond)'))
+----
+32
+
+query I
+SELECT extract(minute from arrow_cast('23:32:50.123456'::time,
'Time64(Microsecond)'))
+----
+32
+
+query I
+SELECT date_part('second', arrow_cast('23:32:50.123456'::time,
'Time64(Microsecond)'))
+----
+50
+
+query I
+SELECT extract(second from arrow_cast('23:32:50.123456'::time,
'Time64(Microsecond)'))
+----
+50
+
+query I
+SELECT date_part('millisecond', arrow_cast('23:32:50.123456'::time,
'Time64(Microsecond)'))
+----
+50123
+
+query I
+SELECT extract(millisecond from arrow_cast('23:32:50.123456'::time,
'Time64(Microsecond)'))
+----
+50123
+
+query I
+SELECT date_part('microsecond', arrow_cast('23:32:50.123456'::time,
'Time64(Microsecond)'))
+----
+50123456
+
+query I
+SELECT extract(microsecond from arrow_cast('23:32:50.123456'::time,
'Time64(Microsecond)'))
+----
+50123456
+
+query error DataFusion error: Internal error: unit Nanosecond not supported
+SELECT extract(nanosecond from arrow_cast('23:32:50.123456'::time,
'Time64(Microsecond)'))
+
+query R
+SELECT date_part('epoch', arrow_cast('23:32:50.123456'::time,
'Time64(Microsecond)'))
+----
+84770.123456
+
+query R
+SELECT extract(epoch from arrow_cast('23:32:50.123456'::time,
'Time64(Microsecond)'))
+----
+84770.123456
+
+## time64 nanoseconds
+query I
+SELECT date_part('hour', arrow_cast('23:32:50.123456789'::time,
'Time64(Nanosecond)'))
+----
+23
+
+query I
+SELECT extract(hour from arrow_cast('23:32:50.123456789'::time,
'Time64(Nanosecond)'))
+----
+23
+
+query I
+SELECT date_part('minute', arrow_cast('23:32:50.123456789'::time,
'Time64(Nanosecond)'))
+----
+32
+
+query I
+SELECT extract(minute from arrow_cast('23:32:50.123456789'::time,
'Time64(Nanosecond)'))
+----
+32
+
+query I
+SELECT date_part('second', arrow_cast('23:32:50.123456789'::time,
'Time64(Nanosecond)'))
+----
+50
+
+query I
+select extract(second from '2024-08-09T12:13:14')
+----
+14
+
+query I
+select extract(seconds from '2024-08-09T12:13:14')
+----
+14
+
+query I
+SELECT extract(second from arrow_cast('23:32:50.123456789'::time,
'Time64(Nanosecond)'))
+----
+50
+
+query I
+SELECT date_part('millisecond', arrow_cast('23:32:50.123456789'::time,
'Time64(Nanosecond)'))
+----
+50123
+
+query I
+SELECT extract(millisecond from arrow_cast('23:32:50.123456789'::time,
'Time64(Nanosecond)'))
+----
+50123
+
+# just some floating point stuff happening in the result here
+query I
+SELECT date_part('microsecond', arrow_cast('23:32:50.123456789'::time,
'Time64(Nanosecond)'))
+----
+50123456
+
+query I
+SELECT extract(microsecond from arrow_cast('23:32:50.123456789'::time,
'Time64(Nanosecond)'))
+----
+50123456
+
+query I
+SELECT extract(us from arrow_cast('23:32:50.123456789'::time,
'Time64(Nanosecond)'))
+----
+50123456
+
+query error DataFusion error: Internal error: unit Nanosecond not supported
+SELECT date_part('nanosecond', arrow_cast('23:32:50.123456789'::time,
'Time64(Nanosecond)'))
+
+query R
+SELECT date_part('epoch', arrow_cast('23:32:50.123456789'::time,
'Time64(Nanosecond)'))
+----
+84770.123456789
+
+query R
+SELECT extract(epoch from arrow_cast('23:32:50.123456789'::time,
'Time64(Nanosecond)'))
+----
+84770.123456789
+
+# test_extract_epoch
+
+query R
+SELECT extract(epoch from '1870-01-01T07:29:10.256'::timestamp)
+----
+-3155646649.744
+
+query R
+SELECT extract(epoch from '2000-01-01T00:00:00.000'::timestamp)
+----
+946684800
+
+query R
+SELECT extract(epoch from to_timestamp('2000-01-01T00:00:00+00:00'))
+----
+946684800
+
+query R
+SELECT extract(epoch from NULL::timestamp)
+----
+NULL
+
+query R
+SELECT extract(epoch from arrow_cast('1970-01-01', 'Date32'))
+----
+0
+
+query R
+SELECT extract(epoch from arrow_cast('1970-01-02', 'Date32'))
+----
+86400
+
+query R
+SELECT extract(epoch from arrow_cast('1970-01-11', 'Date32'))
+----
+864000
+
+query R
+SELECT extract(epoch from arrow_cast('1969-12-31', 'Date32'))
+----
+-86400
+
+query R
+SELECT extract(epoch from arrow_cast('1970-01-01', 'Date64'))
+----
+0
+
+query R
+SELECT extract(epoch from arrow_cast('1970-01-02', 'Date64'))
+----
+86400
+
+query R
+SELECT extract(epoch from arrow_cast('1970-01-11', 'Date64'))
+----
+864000
+
+query R
+SELECT extract(epoch from arrow_cast('1969-12-31', 'Date64'))
+----
+-86400
+
+# test_extract_interval
+
+query I
+SELECT extract(year from arrow_cast('10 years', 'Interval(YearMonth)'))
+----
+10
+
+query I
+SELECT extract(month from arrow_cast('10 years', 'Interval(YearMonth)'))
+----
+0
+
+query I
+SELECT extract(year from arrow_cast('10 months', 'Interval(YearMonth)'))
+----
+0
+
+query I
+SELECT extract(month from arrow_cast('10 months', 'Interval(YearMonth)'))
+----
+10
+
+query I
+SELECT extract(year from arrow_cast('20 months', 'Interval(YearMonth)'))
+----
+1
+
+query I
+SELECT extract(month from arrow_cast('20 months', 'Interval(YearMonth)'))
+----
+8
+
+query error DataFusion error: Arrow error: Compute error: Year does not
support: Interval\(DayTime\)
+SELECT extract(year from arrow_cast('10 days', 'Interval(DayTime)'))
+
+query error DataFusion error: Arrow error: Compute error: Month does not
support: Interval\(DayTime\)
+SELECT extract(month from arrow_cast('10 days', 'Interval(DayTime)'))
+
+query I
+SELECT extract(day from arrow_cast('10 days', 'Interval(DayTime)'))
+----
+10
+
+query I
+SELECT extract(day from arrow_cast('14400 minutes', 'Interval(DayTime)'))
+----
+0
+
+query I
+SELECT extract(minute from arrow_cast('14400 minutes', 'Interval(DayTime)'))
+----
+14400
+
+query I
+SELECT extract(second from arrow_cast('5.1 seconds', 'Interval(DayTime)'))
+----
+5
+
+query I
+SELECT extract(second from arrow_cast('14400 minutes', 'Interval(DayTime)'))
+----
+864000
+
+query I
+SELECT extract(second from arrow_cast('2 months', 'Interval(MonthDayNano)'))
+----
+0
+
+query I
+SELECT extract(second from arrow_cast('2 days', 'Interval(MonthDayNano)'))
+----
+0
+
+query I
+SELECT extract(second from arrow_cast('2 seconds', 'Interval(MonthDayNano)'))
+----
+2
+
+query I
+SELECT extract(seconds from arrow_cast('2 seconds', 'Interval(MonthDayNano)'))
+----
+2
+
+query R
+SELECT extract(epoch from arrow_cast('2 seconds', 'Interval(MonthDayNano)'))
+----
+2
+
+query I
+SELECT extract(milliseconds from arrow_cast('2 seconds',
'Interval(MonthDayNano)'))
+----
+2000
+
+query I
+SELECT extract(second from arrow_cast('2030 milliseconds',
'Interval(MonthDayNano)'))
+----
+2
+
+query I
+SELECT extract(second from arrow_cast(NULL, 'Interval(MonthDayNano)'))
+----
+NULL
+
+statement ok
+create table t (id int, i interval) as values
+ (0, interval '5 months 1 day 10 nanoseconds'),
+ (1, interval '1 year 3 months'),
+ (2, interval '3 days 2 milliseconds'),
+ (3, interval '2 seconds'),
+ (4, interval '8 months'),
+ (5, NULL);
+
+query III
+select
+ id,
+ extract(second from i),
+ extract(month from i)
+from t
+order by id;
+----
+0 0 5
+1 0 15
+2 0 0
+3 2 0
+4 0 8
+5 NULL NULL
+
+statement ok
+drop table t;
+
+# test_extract_duration
+
+query I
+SELECT extract(second from arrow_cast(2, 'Duration(Second)'))
+----
+2
+
+query I
+SELECT extract(seconds from arrow_cast(2, 'Duration(Second)'))
+----
+2
+
+query R
+SELECT extract(epoch from arrow_cast(2, 'Duration(Second)'))
+----
+2
+
+query I
+SELECT extract(millisecond from arrow_cast(2, 'Duration(Second)'))
+----
+2000
+
+query I
+SELECT extract(second from arrow_cast(2, 'Duration(Millisecond)'))
+----
+0
+
+query I
+SELECT extract(second from arrow_cast(2002, 'Duration(Millisecond)'))
+----
+2
+
+query I
+SELECT extract(millisecond from arrow_cast(2002, 'Duration(Millisecond)'))
+----
+2002
+
+query I
+SELECT extract(day from arrow_cast(864000, 'Duration(Second)'))
+----
+10
+
+query error DataFusion error: Arrow error: Compute error: Month does not
support: Duration\(Second\)
+SELECT extract(month from arrow_cast(864000, 'Duration(Second)'))
+
+query error DataFusion error: Arrow error: Compute error: Year does not
support: Duration\(Second\)
+SELECT extract(year from arrow_cast(864000, 'Duration(Second)'))
+
+query I
+SELECT extract(day from arrow_cast(NULL, 'Duration(Second)'))
+----
+NULL
+
+# test_extract_date_part_func
+
+query B
+SELECT (date_part('year', now()) = EXTRACT(year FROM now()))
+----
+true
+
+query B
+SELECT (date_part('quarter', now()) = EXTRACT(quarter FROM now()))
+----
+true
+
+query B
+SELECT (date_part('month', now()) = EXTRACT(month FROM now()))
+----
+true
+
+query B
+SELECT (date_part('week', now()) = EXTRACT(week FROM now()))
+----
+true
+
+query B
+SELECT (date_part('day', now()) = EXTRACT(day FROM now()))
+----
+true
+
+query B
+SELECT (date_part('hour', now()) = EXTRACT(hour FROM now()))
+----
+true
+
+query B
+SELECT (date_part('minute', now()) = EXTRACT(minute FROM now()))
+----
+true
+
+query B
+SELECT (date_part('second', now()) = EXTRACT(second FROM now()))
+----
+true
+
+query B
+SELECT (date_part('millisecond', now()) = EXTRACT(millisecond FROM now()))
+----
+true
+
+query B
+SELECT (date_part('microsecond', now()) = EXTRACT(microsecond FROM now()))
+----
+true
+
+query error DataFusion error: Internal error: unit Nanosecond not supported
+SELECT (date_part('nanosecond', now()) = EXTRACT(nanosecond FROM now()))
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
