presto - getting days interval (not date)

Question

How do I get the days interval for prestodb? I can convert to milliseconds and convert these to number of days but I am looking if there is any shorter way to do this.

Example: I want to see how many days has it been since the first row inserted in a table.

SELECT 
    to_milliseconds(date(current_date) - min(created)) / (1000*60*60*24) as days_since_first_row
FROM
    some_table

What I am hoping to see: (Either 1 of below)

 SELECT 
        to_days(date(current_date) - min(created)) / (1000*60*60*24) as days_since_first_row
        ,cast(date(current_date) - min(created)) as days) as days_since_first_row2
    FROM
        some_table

score 12 · Answer 1 · answered Sep 14 '20 at 22:27

Unfortunately, daylight savings breaks the solution from the accepted answer. DAY(DATE '2020-09-6' - DATE '2020-03-09') and DAY(DATE '2020-09-6' - DATE '2020-03-08') are both equal to 181 due to daylight savings time and DAY acting as a floor function on timestamps.

Instead, use DATE_DIFF:

DATE_DIFF('day', DATE '2020-09-6', DATE '2020-03-09')

Piotr Findeisen · Accepted Answer · 2021-03-06T20:54:16.757

9

Use subtraction to obtain an interval and then use day on the interval to get number of days elapsed.

presto:default> select day(current_date - date '2018-07-01');
 _col0
-------
    86

The documentation for this is at https://trino.io/docs/current/functions/datetime.html

edited Mar 06 '21 at 20:54

answered Sep 25 '18 at 12:18

Piotr Findeisen

19,480
2
52
82

THanks! Where is this in the documentation? Is this part of the *Convenience Extraction Functions*? Also using `second()`, `hour()`, and `minute()` don't seem to return me what I expected. – addicted Sep 25 '18 at 13:07
@addicted you should read this as "extract day portion from [day to seconds] interval". `hour()` will extract "hour portion from interval", which for intervals between two dates will always be 0. If you want to express interval in numbers of hours you might need to extract day portion, hour portion and combine these two. – Piotr Findeisen Sep 26 '18 at 07:01
Thanks for the explanation on the datetime component. – addicted Sep 26 '18 at 08:23

presto - getting days interval (not date)

2 Answers2