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
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')
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With