Can not use extract <part> on the value of now()

[alamb]

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

[waitingkuo]

i guess this is similar as #3096

[alamb]

I'll plan to try and improve things here myself in the next week or two if no one else has a chance

[waitingkuo]

i'm working on this apache/arrow-rs#1380 which is probably one of the root case for extract and date_part
https://github.com/apache/arrow-rs/blob/master/arrow/src/compute/kernels/temporal.rs#L694-L712

in postgres extract returns numeric/decimal while date_part returns double precision. Should we follow it?
https://www.postgresql.org/docs/15/functions-datetime.html

btw, postgres doc recommend use extract

For historical reasons, the date_part function returns values of type double precision. 
This can result in a loss of precision in certain uses. Using extract is recommended instead.

[alamb]

Should we follow it?

I am not quite sure what you are asking. Are you asking if we should follow postgres so that date_part returns double?

If so I don't have any strong opinion 🤷

[waitingkuo]

@alamb

datafusion currently returns i32 which might lose some information, e.g.

❯ select date_part('second', timestamp '2000-01-01T00:00:00.1');
+--------------------------------------------------------+
| datepart(Utf8("second"),Utf8("2000-01-01T00:00:00.1")) |
+--------------------------------------------------------+
| 0                                                      |
+--------------------------------------------------------+
1 row in set. Query took 0.000 seconds.

and

❯ select extract(second from timestamp '2000-01-01T00:00:00.1');
+--------------------------------------------------------+
| datepart(Utf8("SECOND"),Utf8("2000-01-01T00:00:00.1")) |
+--------------------------------------------------------+
| 0                                                      |
+--------------------------------------------------------+
1 row in set. Query took 0.000 seconds.

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]

is it recommended to follow postgresql's return type?

That probably makes the most sense

if it is preferred, perhaps we could do a separate pr to deal with this

Agreed -- can you file a ticket? (basically copy/paste #3980 (comment) 😆 )

[waitingkuo]

@alamb
i fired 2 instead 😆 #3996 #3997

[alamb]

Thanks @waitingkuo -- We'll get there some day!

[comphead]

This should be fixed by #4548

[alamb]

I confirmed that on master the query works now:

(arrow_dev) alamb@MacBook-Pro-8:~/Software/arrow-datafusion$ datafusion-cli
DataFusion CLI v15.0.0
❯ select extract (day  from now());
+-----------------------------+
| datepart(Utf8("DAY"),now()) |
+-----------------------------+
| 9                           |
+-----------------------------+
1 row in set. Query took 0.035 seconds.

Thank you @comphead ❤️