You are viewing a plain text version of this content. The canonical link for it is here.
Posted to github@arrow.apache.org by GitBox <gi...@apache.org> on 2022/10/27 13:05:46 UTC
[GitHub] [arrow-datafusion] alamb opened a new issue, #3980: Can not use `extract ` on the value of `now()`
alamb opened a new issue, #3980:
URL: https://github.com/apache/arrow-datafusion/issues/3980
**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:
```sql
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):
```sql
❯ select extract (day from cast(now() as timestamp));
+-----------------------------+
| datepart(Utf8("DAY"),now()) |
+-----------------------------+
| 27 |
+-----------------------------+
```
**Additional context**
This was reported by early (internal) users of IOx
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] waitingkuo commented on issue #3980: Can not use `extract ` on the value of `now()`
Posted by GitBox <gi...@apache.org>.
waitingkuo commented on issue #3980:
URL: https://github.com/apache/arrow-datafusion/issues/3980#issuecomment-1293743669
i guess this is similar as #3096
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] alamb commented on issue #3980: Can not use `extract ` on the value of `now()`
Posted by GitBox <gi...@apache.org>.
alamb commented on issue #3980:
URL: https://github.com/apache/arrow-datafusion/issues/3980#issuecomment-1344815093
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 ❤️
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] alamb commented on issue #3980: Can not use `extract ` on the value of `now()`
Posted by GitBox <gi...@apache.org>.
alamb commented on issue #3980:
URL: https://github.com/apache/arrow-datafusion/issues/3980#issuecomment-1294992183
> 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 https://github.com/apache/arrow-datafusion/issues/3980#issuecomment-1294513044 😆 )
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] waitingkuo commented on issue #3980: Can not use `extract ` on the value of `now()`
Posted by GitBox <gi...@apache.org>.
waitingkuo commented on issue #3980:
URL: https://github.com/apache/arrow-datafusion/issues/3980#issuecomment-1294513044
@alamb
datafusion currently returns `i32` which might lose some information, e.g.
```bash
❯ 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
```bash
❯ 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
```bash
willy=# select date_part('second', timestamp '2000-01-01T00:00:00.1');
date_part
-----------
0.1
(1 row)
```
which returns double precision
and
```bash
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?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] waitingkuo commented on issue #3980: Can not use `extract ` on the value of `now()`
Posted by GitBox <gi...@apache.org>.
waitingkuo commented on issue #3980:
URL: https://github.com/apache/arrow-datafusion/issues/3980#issuecomment-1293806500
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.
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] alamb commented on issue #3980: Can not use `extract ` on the value of `now()`
Posted by GitBox <gi...@apache.org>.
alamb commented on issue #3980:
URL: https://github.com/apache/arrow-datafusion/issues/3980#issuecomment-1294028055
> 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 🤷
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] alamb commented on issue #3980: Can not use `extract ` on the value of `now()`
Posted by GitBox <gi...@apache.org>.
alamb commented on issue #3980:
URL: https://github.com/apache/arrow-datafusion/issues/3980#issuecomment-1295809605
We'll get there some day!
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] comphead commented on issue #3980: Can not use `extract ` on the value of `now()`
Posted by GitBox <gi...@apache.org>.
comphead commented on issue #3980:
URL: https://github.com/apache/arrow-datafusion/issues/3980#issuecomment-1344797774
This should be fixed by #4548
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] alamb closed issue #3980: Can not use `extract ` on the value of `now()`
Posted by GitBox <gi...@apache.org>.
alamb closed issue #3980: Can not use `extract <part>` on the value of `now()`
URL: https://github.com/apache/arrow-datafusion/issues/3980
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] alamb commented on issue #3980: Can not use `extract ` on the value of `now()`
Posted by GitBox <gi...@apache.org>.
alamb commented on issue #3980:
URL: https://github.com/apache/arrow-datafusion/issues/3980#issuecomment-1293768981
I'll plan to try and improve things here myself in the next week or two if no one else has a chance
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] waitingkuo commented on issue #3980: Can not use `extract ` on the value of `now()`
Posted by GitBox <gi...@apache.org>.
waitingkuo commented on issue #3980:
URL: https://github.com/apache/arrow-datafusion/issues/3980#issuecomment-1295051950
@alamb
i fired 2 instead 😆 #3996 #3997
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org