CODEWITHSUNDEEP
sqlprestostarburst
Peter Lucas
Peter Lucas

Reputation: 1991

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

Upvotes: 0

Views: 235

Answers (1)

nbk
nbk

Reputation: 49410

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

Upvotes: 0

Related Questions