Search code examples
sqlprestostarburst

Presto SQL to select all records between the current date/time and 36 hours ago

I'd prefer not to update two date string each morning and simply select all records between now and the previous 36 hours. Sample where condition which I would like to paramaterise:

-- DATE_FORMAT(FROM_UNIXTIME(desk.reported_date, 'UTC'), '%Y-%m-%d %T') BETWEEN '2024-01-02 06:00:00.000' AND '2024-12-31 23:59:59.000'

My attempts:

 --DATE_FORMAT(FROM_UNIXTIME(desk.submit_date, 'UTC'),'%Y-%m-%d %T') > (CURRENT_DATE - INTERVAL '36' HOUR)
 --CAST (DATE_FORMAT(FROM_UNIXTIME(desk.submit_date, 'UTC'),'%Y-%m-%d %T') AS DATE)        > CURRENT_DATE - INTERVAL '1' HOUR 
   DATE_FORMAT(FROM_UNIXTIME(desk.submit_date, 'UTC'), '%Y-%m-%d %T') BETWEEN  '2024-01-02 06:00:00.000' AND date_add('hour', desk.submit_date, date_parse(-36, '%Y-%m-%d'))

Common Error:

Cannot subtract hour, minutes or seconds from a date
Solution

  • You can use INTERVAL to get your wanted data.

    If you want to manipute the timestamp current_timestamp (with timezone), you need as result again a timestampo

    WHERE desk.submit_date BETWEEN current_timestamp - interval '36' hour AND current_timestamp