Repository navigation
Can not use extract <part> on the value of now() #3980
Description
Describe the bug
I would like to be able to find the current date, or the current day of week, etc
DataFusion supports now() and extract (hour from <timestamp>) syntax ✅
However, you can't use extract with the value of now() 😢
To Reproduce
❯ select extract (day from now());
Plan("Coercion from [Utf8, Timestamp(Nanosecond, Some(\"UTC\"))] to the signature OneOf([Exact([Utf8, Date32]), Exact([Utf8, Date64]), Exact([Utf8, Timestamp(Second, None)]), Exact([Utf8, Timestamp(Microsecond, None)]), Exact([Utf8, Timestamp(Millisecond, None)]), Exact([Utf8, Timestamp(Nanosecond, None)])]) failed.")
Expected behavior
I expect the day part of now() to be extracted as in postgres:
postgres=# select extract(day from now());
extract
---------
27
(1 row)Note that the error seems to be that DataFusion can't coerce a Timestamp(Nanosecond, "UTC") to Timestamp(Nanosecond, None) -- aka it is related to timezones
You can work around the problem by explicitly casting the output of now() to timestamp (not timestamptz):
❯ select extract (day from cast(now() as timestamp));
+-----------------------------+
| datepart(Utf8("DAY"),now()) |
+-----------------------------+
| 27 |
+-----------------------------+Additional context
This was reported by early (internal) users of IOx
Activity
waitingkuo commentedon Oct 27, 2022
i guess this is similar as #3096
alamb commentedon Oct 27, 2022
I'll plan to try and improve things here myself in the next week or two if no one else has a chance
waitingkuo commentedon Oct 27, 2022
i'm working on this apache/arrow-rs#1380 which is probably one of the root case for
extractanddate_parthttps://github.com/apache/arrow-rs/blob/master/arrow/src/compute/kernels/temporal.rs#L694-L712
in postgres
extractreturnsnumeric/decimalwhiledate_partreturns double precision. Should we follow it?https://www.postgresql.org/docs/15/functions-datetime.html
btw, postgres doc recommend use
extractalamb commentedon Oct 27, 2022
I am not quite sure what you are asking. Are you asking if we should follow postgres so that
date_partreturnsdouble?If so I don't have any strong opinion 🤷
waitingkuo commentedon Oct 28, 2022
@alamb
datafusion currently returns
i32which might lose some information, e.g.and
while postgresql has
willy=# select date_part('second', timestamp '2000-01-01T00:00:00.1'); date_part ----------- 0.1 (1 row)which returns double precision
and
willy=# select extract(second from timestamp '2000-01-01T00:00:00.1'); extract ---------- 0.100000 (1 row)which returns decimal
is it recommended to follow postgresql's return type?
if it is preferred, perhaps we could do a separate pr to deal with this
alamb commentedon Oct 28, 2022
That probably makes the most sense
Agreed -- can you file a ticket? (basically copy/paste #3980 (comment) 😆 )
waitingkuo commentedon Oct 28, 2022
@alamb
i fired 2 instead 😆 #3996 #3997
alamb commentedon Oct 29, 2022
Thanks @waitingkuo -- We'll get there some day!
comphead commentedon Dec 9, 2022
This should be fixed by #4548
alamb commentedon Dec 9, 2022
I confirmed that on master the query works now:
Thank you @comphead ❤️