Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

presto - getting days interval (not date)

Tags:

sql

presto

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
like image 775
addicted Avatar asked Sep 25 '18 09:09

addicted


2 Answers

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')
like image 68
0x24a537r9 Avatar answered Oct 08 '22 05:10

0x24a537r9


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

like image 23
Piotr Findeisen Avatar answered Oct 08 '22 03:10

Piotr Findeisen