Repository navigation
Closed
Labels
enhancementNew feature or requestNew feature or request
Description
Note: migrated from original JIRA: https://issues.apache.org/jira/browse/ARROW-12234
Usecase
I have two columns, time_of_last_write, and time_of_first_write, and that have type Timestamp(Nanosecond, None) and I want to compare them to see how much time has elapsed
Actual Behavior
When I try to subtract them I get an error that there isn't a common type to coerce the types to:
> 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")
>
Expected behavior
The query works (the resulting column should be a duration)
The data looks like this:
> 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.
Workaround
You can cast the columns to Int64/bigint and then subtract them. Something like:
select id,
partition_key,
storage,
estimated_bytes,
cast (time_of_last_write as bigint) - cast(time_of_first_write as time_open as bigint) as delta_nanos
from chunks
where database_name = '844910ece80be8bc_7be09b71c487d5d3'
order by id;Metadata
Metadata
Assignees
Labels
enhancementNew feature or requestNew feature or request
Activity
alamb commentedon May 11, 2021
I am going to give this a shot (as we currently have to do some heinous workarounds to calculate intervals from timestamps in IOx)
jorgecarleitao commentedon May 11, 2021
for inspiration
alamb commentedon May 11, 2021
Here is a simpler reproducer:
And then
alamb commentedon May 12, 2021
I have been thinking about this and did some digging in the code. There are probably two major strategies:
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)
alamb commentedon Jul 29, 2021
BTW there is a (fairly nasty) workaround to cast to an integer and do the math that way
So for example, rather than
You can write something like
Which is definitely not very pretty 😢 but it does work
[-] Can't subtract timestamps[/-][+] Can't subtract timestamps / add support for timestamp arithmetic[/+]alamb commentedon Aug 6, 2021
BTW @bjchambers is working on apache/arrow-rs#527 to add kernels to arrow-rs that would then be usable by DataFusion
[-] Can't subtract timestamps / add support for timestamp arithmetic[/-][+] Add support for subtracting timestamps --> intervals[/+]feat: Support HashJoin operator (apache#194)