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 2021/04/26 13:33:05 UTC
[GitHub] [arrow-datafusion] alamb opened a new issue #194: Can't subtract timestamps
alamb opened a new issue #194:
URL: https://github.com/apache/arrow-datafusion/issues/194
*Note*: migrated from original JIRA: https://issues.apache.org/jira/browse/ARROW-12234
I have two columns, time_of_last_write, and time_of_first_write, and that have type `Timestamp(Nanosecond, None)`
When I try to subtract them I get an error that there isn't a common type to coerce the types to:
{code}
> select id, partition_key, storage, estimated_bytes, time_of_last_write - time_of_first_write as time_open from chunks where database_name = '844910ece80be8bc_7be09b71c487d5d3' order by id;
Plan("\'Timestamp(Nanosecond, None) - Timestamp(Nanosecond, None)\' can\'t be evaluated because there isn\'t a common type to coerce the types to")
>
{code}
Expected behavior: The query works (the resulting column should be a duration)
The data looks like this:
{code}
> select * from chunks where database_name = '844910ece80be8bc_7be09b71c487d5d3' order by id;
+-----------------------------------+-----+---------------------+---------------------+-----------------+-------------------------------+-------------------------------+-------------------------------+
| database_name | id | partition_key | storage | estimated_bytes | time_of_first_write | time_of_last_write | time_closing |
+-----------------------------------+-----+---------------------+---------------------+-----------------+-------------------------------+-------------------------------+-------------------------------+
| 844910ece80be8bc_7be09b71c487d5d3 | 452 | 2021-04-06 18:00:00 | ClosedMutableBuffer | 10746690 | 2021-04-06 18:46:52.356380931 | 2021-04-06 18:47:09.065541747 | 2021-04-06 18:47:09.098939917 |
| 844910ece80be8bc_7be09b71c487d5d3 | 453 | 2021-04-06 18:00:00 | ClosedMutableBuffer | 11248853 | 2021-04-06 18:47:09.495662420 | 2021-04-06 18:47:13.032639050 | 2021-04-06 18:47:13.058829814 |
| 844910ece80be8bc_7be09b71c487d5d3 | 454 | 2021-04-06 18:00:00 | ClosedMutableBuffer | 11249404 | 2021-04-06 18:47:13.594526676 | 2021-04-06 18:47:16.697048218 | 2021-04-06 18:47:16.723124402 |
| 844910ece80be8bc_7be09b71c487d5d3 | 455 | 2021-04-06 18:00:00 | ClosedMutableBuffer | 11248972 | 2021-04-06 18:47:17.128724226 | 2021-04-06 18:47:20.055123319 | 2021-04-06 18:47:20.081196973 |
| 844910ece80be8bc_7be09b71c487d5d3 | 456 | 2021-04-06 18:00:00 | ClosedMutableBuffer | 11248778 | 2021-04-06 18:47:20.609498175 | 2021-04-06 18:47:24.196610989 | 2021-04-06 18:47:24.233891509 |
| 844910ece80be8bc_7be09b71c487d5d3 | 457 | 2021-04-06 18:00:00 | ClosedMutableBuffer | 11249297 | 2021-04-06 18:47:24.660687691 | 2021-04-06 18:47:27.734848138 | 2021-04-06 18:47:27.762860931 |
| 844910ece80be8bc_7be09b71c487d5d3 | 458 | 2021-04-06 18:00:00 | ClosedMutableBuffer | 11249046 | 2021-04-06 18:47:28.128078919 | 2021-04-06 18:47:31.652250155 | 2021-04-06 18:47:31.690460702 |
| 844910ece80be8bc_7be09b71c487d5d3 | 459 | 2021-04-06 18:00:00 | ClosedMutableBuffer | 11249824 | 2021-04-06 18:47:32.286068833 | 2021-04-06 18:47:36.461676369 | 2021-04-06 18:47:36.486294829 |
| 844910ece80be8bc_7be09b71c487d5d3 | 460 | 2021-04-06 18:00:00 | ClosedMutableBuffer | 11249913 | 2021-04-06 18:47:36.944984769 | 2021-04-06 18:47:40.162251810 | 2021-04-06 18:47:40.188262747 |
| 844910ece80be8bc_7be09b71c487d5d3 | 461 | 2021-04-06 18:00:00 | ClosedMutableBuffer | 11248237 | 2021-04-06 18:47:40.719734516 | 2021-04-06 18:47:44.370867837 | 2021-04-06 18:47:44.397872698 |
| 844910ece80be8bc_7be09b71c487d5d3 | 462 | 2021-04-06 18:00:00 | ClosedMutableBuffer | 11602754 | 2021-04-06 18:47:44.844728218 | 2021-04-06 18:48:24.309093588 | 2021-04-06 18:48:24.339811197 |
| 844910ece80be8bc_7be09b71c487d5d3 | 463 | 2021-04-06 18:00:00 | ClosedMutableBuffer | 11249162 | 2021-04-06 18:48:24.847852183 | 2021-04-06 18:48:30.529014754 | 2021-04-06 18:48:30.556962859 |
| 844910ece80be8bc_7be09b71c487d5d3 | 464 | 2021-04-06 18:00:00 | ClosedMutableBuffer | 11248908 | 2021-04-06 18:48:31.148468537 | 2021-04-06 18:48:36.805296070 | 2021-04-06 18:48:36.830190418 |
| 844910ece80be8bc_7be09b71c487d5d3 | 465 | 2021-04-06 18:00:00 | ClosedMutableBuffer | 11250833 | 2021-04-06 18:48:37.258673133 | 2021-04-06 18:48:39.849493178 | 2021-04-06 18:48:39.875272790 |
| 844910ece80be8bc_7be09b71c487d5d3 | 466 | 2021-04-06 18:00:00 | ClosedMutableBuffer | 11248570 | 2021-04-06 18:48:40.304598973 | 2021-04-06 18:48:43.572838266 | 2021-04-06 18:48:43.597973739 |
| 844910ece80be8bc_7be09b71c487d5d3 | 467 | 2021-04-06 18:00:00 | ClosedMutableBuffer | 11248882 | 2021-04-06 18:48:44.086791040 | 2021-04-06 18:48:46.746045462 | 2021-04-06 18:48:46.770647201 |
| 844910ece80be8bc_7be09b71c487d5d3 | 468 | 2021-04-06 18:00:00 | ClosedMutableBuffer | 11248821 | 2021-04-06 18:48:47.160663577 | 2021-04-06 18:48:52.829573140 | 2021-04-06 18:48:52.856238893 |
| 844910ece80be8bc_7be09b71c487d5d3 | 469 | 2021-04-06 18:00:00 | ClosedMutableBuffer | 11248912 | 2021-04-06 18:48:53.294245366 | 2021-04-06 18:48:56.049161562 | 2021-04-06 18:48:56.075061211 |
| 844910ece80be8bc_7be09b71c487d5d3 | 470 | 2021-04-06 18:00:00 | ClosedMutableBuffer | 11249496 | 2021-04-06 18:48:56.458660830 | 2021-04-06 18:49:03.772657983 | 2021-04-06 18:49:03.798770081 |
| 844910ece80be8bc_7be09b71c487d5d3 | 471 | 2021-04-06 18:00:00 | ClosedMutableBuffer | 11248444 | 2021-04-06 18:49:04.348904590 | 2021-04-06 18:49:07.493503973 | 2021-04-06 18:49:07.531384607 |
| 844910ece80be8bc_7be09b71c487d5d3 | 472 | 2021-04-06 18:00:00 | ClosedMutableBuffer | 11249942 | 2021-04-06 18:49:08.057454104 | 2021-04-06 18:49:09.739696342 | 2021-04-06 18:49:09.774282392 |
| 844910ece80be8bc_7be09b71c487d5d3 | 473 | 2021-04-06 18:00:00 | ClosedMutableBuffer | 11249694 | 2021-04-06 18:49:10.149856237 | 2021-04-06 18:49:11.917092181 | 2021-04-06 18:49:11.944858405 |
| 844910ece80be8bc_7be09b71c487d5d3 | 474 | 2021-04-06 18:00:00 | ClosedMutableBuffer | 11249121 | 2021-04-06 18:49:12.468381788 | 2021-04-06 18:49:15.591750131 | 2021-04-06 18:49:15.616630965 |
| 844910ece80be8bc_7be09b71c487d5d3 | 475 | 2021-04-06 18:00:00 | ClosedMutableBuffer | 11248991 | 2021-04-06 18:49:16.153951838 | 2021-04-06 18:49:18.976354659 | 2021-04-06 18:49:19.016245178 |
| 844910ece80be8bc_7be09b71c487d5d3 | 476 | 2021-04-06 18:00:00 | ClosedMutableBuffer | 11250208 | 2021-04-06 18:49:19.395871363 | 2021-04-06 18:49:22.759913395 | 2021-04-06 18:49:22.784817611 |
| 844910ece80be8bc_7be09b71c487d5d3 | 477 | 2021-04-06 18:00:00 | ClosedMutableBuffer | 11498521 | 2021-04-06 18:49:23.615569513 | 2021-04-06 18:49:26.623931291 | 2021-04-06 18:49:26.660244449 |
| 844910ece80be8bc_7be09b71c487d5d3 | 478 | 2021-04-06 18:00:00 | ClosedMutableBuffer | 11249159 | 2021-04-06 18:49:27.064616718 | 2021-04-06 18:49:30.264468989 | 2021-04-06 18:49:30.294619674 |
| 844910ece80be8bc_7be09b71c487d5d3 | 479 | 2021-04-06 18:00:00 | ClosedMutableBuffer | 11248638 | 2021-04-06 18:49:30.785027120 | 2021-04-06 18:49:34.726633411 | 2021-04-06 18:49:34.752227308 |
| 844910ece80be8bc_7be09b71c487d5d3 | 480 | 2021-04-06 18:00:00 | ClosedMutableBuffer | 11250018 | 2021-04-06 18:49:35.237067884 | 2021-04-06 18:49:38.151745575 | 2021-04-06 18:49:38.176959756 |
| 844910ece80be8bc_7be09b71c487d5d3 | 481 | 2021-04-06 18:00:00 | ClosedMutableBuffer | 11249106 | 2021-04-06 18:49:38.989765994 | 2021-04-06 18:49:41.981941826 | 2021-04-06 18:49:42.021489386 |
| 844910ece80be8bc_7be09b71c487d5d3 | 482 | 2021-04-06 18:00:00 | ClosedMutableBuffer | 11249719 | 2021-04-06 18:49:42.644818082 | 2021-04-06 18:49:46.472421441 | 2021-04-06 18:49:46.509991361 |
| 844910ece80be8bc_7be09b71c487d5d3 | 483 | 2021-04-06 18:00:00 | ClosedMutableBuffer | 11116086 | 2021-04-06 18:49:46.875400328 | 2021-04-06 18:50:09.665534681 | 2021-04-06 18:50:09.691033812 |
| 844910ece80be8bc_7be09b71c487d5d3 | 484 | 2021-04-06 18:00:00 | ClosedMutableBuffer | 11248956 | 2021-04-06 18:50:10.190978072 | 2021-04-06 18:50:13.525333003 | 2021-04-06 18:50:13.555135870 |
| 844910ece80be8bc_7be09b71c487d5d3 | 485 | 2021-04-06 18:00:00 | ClosedMutableBuffer | 11249975 | 2021-04-06 18:50:14.048012899 | 2021-04-06 18:50:18.148096051 | 2021-04-06 18:50:18.179913988 |
| 844910ece80be8bc_7be09b71c487d5d3 | 486 | 2021-04-06 18:00:00 | ClosedMutableBuffer | 11249553 | 2021-04-06 18:50:19.237745733 | 2021-04-06 18:50:22.398324666 | 2021-04-06 18:50:22.423405042 |
| 844910ece80be8bc_7be09b71c487d5d3 | 487 | 2021-04-06 18:00:00 | ClosedMutableBuffer | 11249978 | 2021-04-06 18:50:22.874774972 | 2021-04-06 18:50:25.579356855 | 2021-04-06 18:50:25.619053584 |
| 844910ece80be8bc_7be09b71c487d5d3 | 488 | 2021-04-06 18:00:00 | ClosedMutableBuffer | 11249615 | 2021-04-06 18:50:26.031973601 | 2021-04-06 18:50:28.603738970 | 2021-04-06 18:50:28.638264822 |
| 844910ece80be8bc_7be09b71c487d5d3 | 489 | 2021-04-06 18:00:00 | ClosedMutableBuffer | 11248703 | 2021-04-06 18:50:29.055082574 | 2021-04-06 18:50:32.052280871 | 2021-04-06 18:50:32.079017869 |
| 844910ece80be8bc_7be09b71c487d5d3 | 490 | 2021-04-06 18:00:00 | ClosedMutableBuffer | 11249523 | 2021-04-06 18:50:32.620883345 | 2021-04-06 18:50:35.622862033 | 2021-04-06 18:50:35.660574780 |
| 844910ece80be8bc_7be09b71c487d5d3 | 491 | 2021-04-06 18:00:00 | ClosedMutableBuffer | 11248929 | 2021-04-06 18:50:36.056558431 | 2021-04-06 18:50:41.432433559 | 2021-04-06 18:50:41.459101618 |
| 844910ece80be8bc_7be09b71c487d5d3 | 492 | 2021-04-06 18:00:00 | ClosedMutableBuffer | 11249221 | 2021-04-06 18:50:41.984444304 | 2021-04-06 18:50:45.072866077 | 2021-04-06 18:50:45.103413731 |
| 844910ece80be8bc_7be09b71c487d5d3 | 493 | 2021-04-06 18:00:00 | ClosedMutableBuffer | 10740456 | 2021-04-06 18:50:45.565485387 | 2021-04-06 18:51:08.565446851 | 2021-04-06 18:51:08.591433645 |
| 844910ece80be8bc_7be09b71c487d5d3 | 494 | 2021-04-06 18:00:00 | ClosedMutableBuffer | 11250166 | 2021-04-06 18:51:09.026273698 | 2021-04-06 18:51:11.946318759 | 2021-04-06 18:51:11.972177119 |
| 844910ece80be8bc_7be09b71c487d5d3 | 495 | 2021-04-06 18:00:00 | ClosedMutableBuffer | 11249472 | 2021-04-06 18:51:12.510656937 | 2021-04-06 18:51:15.268351520 | 2021-04-06 18:51:15.296416090 |
| 844910ece80be8bc_7be09b71c487d5d3 | 496 | 2021-04-06 18:00:00 | ClosedMutableBuffer | 11249199 | 2021-04-06 18:51:15.698716218 | 2021-04-06 18:51:18.238037128 | 2021-04-06 18:51:18.263700006 |
| 844910ece80be8bc_7be09b71c487d5d3 | 497 | 2021-04-06 18:00:00 | ClosedMutableBuffer | 11248498 | 2021-04-06 18:51:18.645019482 | 2021-04-06 18:51:23.603122152 | 2021-04-06 18:51:23.628969685 |
| 844910ece80be8bc_7be09b71c487d5d3 | 498 | 2021-04-06 18:00:00 | ClosedMutableBuffer | 11249891 | 2021-04-06 18:51:24.086045609 | 2021-04-06 18:51:26.726909386 | 2021-04-06 18:51:26.751204288 |
| 844910ece80be8bc_7be09b71c487d5d3 | 499 | 2021-04-06 18:00:00 | ClosedMutableBuffer | 11249982 | 2021-04-06 18:51:27.171562630 | 2021-04-06 18:51:29.775156947 | 2021-04-06 18:51:29.804096909 |
| 844910ece80be8bc_7be09b71c487d5d3 | 500 | 2021-04-06 18:00:00 | ClosedMutableBuffer | 11249407 | 2021-04-06 18:51:30.286989309 | 2021-04-06 18:51:33.473672824 | 2021-04-06 18:51:33.499924112 |
| 844910ece80be8bc_7be09b71c487d5d3 | 501 | 2021-04-06 18:00:00 | ClosedMutableBuffer | 11249745 | 2021-04-06 18:51:34.012279898 | 2021-04-06 18:51:36.792340410 | 2021-04-06 18:51:36.816946023 |
| 844910ece80be8bc_7be09b71c487d5d3 | 502 | 2021-04-06 18:00:00 | ClosedMutableBuffer | 11249040 | 2021-04-06 18:51:37.227702303 | 2021-04-06 18:51:42.792531763 | 2021-04-06 18:51:42.817708406 |
| 844910ece80be8bc_7be09b71c487d5d3 | 503 | 2021-04-06 18:00:00 | ClosedMutableBuffer | 11249636 | 2021-04-06 18:51:43.367933934 | 2021-04-06 18:51:46.765820047 | 2021-04-06 18:51:46.794681550 |
| 844910ece80be8bc_7be09b71c487d5d3 | 504 | 2021-04-06 18:00:00 | OpenMutableBuffer | 1630094 | 2021-04-06 18:51:47.203180319 | 2021-04-06 18:51:47.203180319 | |
+-----------------------------------+-----+---------------------+---------------------+-----------------+-------------------------------+-------------------------------+-------------------------------+
53 rows in set. Query took 0 seconds.
{code}
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] alamb commented on issue #194: Can't subtract timestamps
Posted by GitBox <gi...@apache.org>.
alamb commented on issue #194:
URL: https://github.com/apache/arrow-datafusion/issues/194#issuecomment-839650054
I have been thinking about this and did some digging in the code. There are probably two major strategies:
1. Implement arrow compute kernels (as @jorgecarleitao points to in https://github.com/jorgecarleitao/arrow2/blob/main/src/compute/arithmetics/time.rs#L208)
2. Implement the operations in datafusion itself (by casting the various time arrays to their primitive i64 versions, performing whatever math is needed, and then casting them back to the resulting type)
Long term I think approach 1 is likely both the "cleanest" and fastest performance approach, but it will take non trivial time.
I may try to bound the time required by just implementing subtraction (end to end in arrow + datafusion), and filing tickets to fill out the rest of the operations (the most useful ones are described in the [postgres docs](https://www.postgresql.org/docs/current/functions-datetime.html))
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] alamb commented on issue #194: Can't subtract timestamps / add support for timestamp arithmetic
Posted by GitBox <gi...@apache.org>.
alamb commented on issue #194:
URL: https://github.com/apache/arrow-datafusion/issues/194#issuecomment-894433918
BTW @bjchambers is working on https://github.com/apache/arrow-rs/issues/527 to add kernels to arrow-rs that would then be usable by DataFusion
--
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 #194: Can't subtract timestamps
Posted by GitBox <gi...@apache.org>.
alamb commented on issue #194:
URL: https://github.com/apache/arrow-datafusion/issues/194#issuecomment-838971642
Here is a simpler reproducer:
```
echo "2020-09-08T12:00:00+00:00,2020-09-09T12:00:00+00:00" > /tmp/foo.csv
echo "2020-09-08T12:00:00+00:00,2020-09-08T11:00:00+00:00" >> /tmp/foo.csv
```
And then
```
>
CREATE EXTERNAL TABLE foo(ts1 timestamp, ts2 timestamp)
STORED AS CSV
LOCATION '/tmp/foo.csv';
0 rows in set. Query took 0 seconds.
>
SELECT ts2 - ts1 from foo;
Plan("\'Timestamp(Nanosecond, None) - Timestamp(Nanosecond, None)\' can\'t be evaluated because there isn\'t a common type to coerce the types to")
```
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] jorgecarleitao commented on issue #194: Can't subtract timestamps
Posted by GitBox <gi...@apache.org>.
jorgecarleitao commented on issue #194:
URL: https://github.com/apache/arrow-datafusion/issues/194#issuecomment-838957553
[for inspiration](https://github.com/jorgecarleitao/arrow2/blob/main/src/compute/arithmetics/time.rs#L208)
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] alamb commented on issue #194: Can't subtract timestamps
Posted by GitBox <gi...@apache.org>.
alamb commented on issue #194:
URL: https://github.com/apache/arrow-datafusion/issues/194#issuecomment-838944330
I am going to give this a shot (as we currently have to do some heinous workarounds to calculate intervals from timestamps in 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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] alamb commented on issue #194: Can't subtract timestamps
Posted by GitBox <gi...@apache.org>.
alamb commented on issue #194:
URL: https://github.com/apache/arrow-datafusion/issues/194#issuecomment-889425187
BTW there is a (fairly nasty) workaround to cast to an integer and do the math that way
So for example, rather than
```sql
select now() - interval '10 minutes'
```
You can write something like
```sql
select CAST(now() as bigint) - 10*60*(1000000000)
```
Which is definitely not very pretty 😢 but it does work
--
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