Add support for subtracting timestamps --> intervals #194

alamb · 2021-04-26T13:32:50Z

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;

The text was updated successfully, but these errors were encountered:

alamb · 2021-05-11T18:18:21Z

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 · 2021-05-11T18:27:11Z

for inspiration

alamb · 2021-05-11T18:36:34Z

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")

alamb · 2021-05-12T10:10:09Z

I have been thinking about this and did some digging in the code. There are probably two major strategies:

Implement arrow compute kernels (as @jorgecarleitao points to in https://github.com/jorgecarleitao/arrow2/blob/main/src/compute/arithmetics/time.rs#L208)
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)

alamb · 2021-07-29T20:09:32Z

BTW there is a (fairly nasty) workaround to cast to an integer and do the math that way

So for example, rather than

select now() - interval '10 minutes'

You can write something like

 select CAST(now() as bigint) - 10*60*(1000000000)

Which is definitely not very pretty 😢 but it does work

alamb · 2021-08-06T18:14:34Z

BTW @bjchambers is working on apache/arrow-rs#527 to add kernels to arrow-rs that would then be usable by DataFusion

alamb added the datafusion Changes in the datafusion crate label Apr 26, 2021

andygrove added the enhancement New feature or request label May 1, 2021

alamb self-assigned this May 11, 2021

alamb mentioned this issue May 13, 2021

[Datafusion] NOW() function support #288

Merged

3 tasks

alamb removed their assignment Jul 29, 2021

alamb changed the title ~~Can't subtract timestamps~~ Can't subtract timestamps / add support for timestamp arithmetic Aug 6, 2021

alamb mentioned this issue Oct 25, 2021

Move code to fold Stable functions like now() from Simplifier to ConstEvaluator #1176

Merged

alamb mentioned this issue Apr 15, 2022

'Date32 + Interval(DayTime)' can't be evaluated because there isn't a common type to coerce the types to #2229

Closed

andygrove self-assigned this Apr 15, 2022

andygrove removed their assignment May 6, 2022

waitingkuo mentioned this issue Aug 10, 2022

Incorrect type coercion rule for date + interval #3093

Closed

alamb mentioned this issue Aug 12, 2022

[EPIC] Proposal for Date/Time enhancement #3100

Closed

waitingkuo mentioned this issue Aug 15, 2022

[EPIC] A collection of Date/Time related open issues #3148

Closed

85 tasks

alamb changed the title ~~Can't subtract timestamps / add support for timestamp arithmetic~~ Add support for subtracting timestamps --> intervals Jan 8, 2023

doki23 mentioned this issue Mar 23, 2023

Timestamp and Interval arithmetics #5704

Closed

alamb mentioned this issue Mar 27, 2023

[EPIC] More complete support for INTERVAL #5753

Closed

14 tasks

berkaysynnada mentioned this issue Mar 28, 2023

Support timestamp and interval arithmetic #5764

Merged

alamb closed this as completed in #5764 Mar 30, 2023

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Add support for subtracting timestamps --> intervals #194

Add support for subtracting timestamps --> intervals #194

alamb commented Apr 26, 2021 •

edited

Loading

alamb commented May 11, 2021

jorgecarleitao commented May 11, 2021

alamb commented May 11, 2021

alamb commented May 12, 2021

alamb commented Jul 29, 2021

alamb commented Aug 6, 2021

Add support for subtracting timestamps --> intervals #194

Add support for subtracting timestamps --> intervals #194

Comments

alamb commented Apr 26, 2021 • edited Loading

Usecase

Actual Behavior

Expected behavior

Workaround

alamb commented May 11, 2021

jorgecarleitao commented May 11, 2021

alamb commented May 11, 2021

alamb commented May 12, 2021

alamb commented Jul 29, 2021

alamb commented Aug 6, 2021

alamb commented Apr 26, 2021 •

edited

Loading