Adding hours to dates in Presto

Question

In a Presto db table, I have two string fields, a date of the form, '2022-01-01', and an hour of the form, 22, for 22:00. I'm trying to combine these two elements into a proper timestamp, with date, hours, minutes, seconds. How can I accomplish this?

What I've tried so far

SELECT DATE_ADD('hour', 22, DATE(d) ) AS dt
FROM table

However, I get the error that 'hour' is not the proper DATE field. However, from documentation, my arguments appear to the in the proper order. https://prestodb.io/docs/current/functions/datetime.html

score 6 · Accepted Answer · answered Feb 11 '22 at 19:05

You need to either cast your date to timestamp:

-- sample data
WITH dataset(x, y) AS (
    VALUES (22,  '2022-01-01')
)

-- query
SELECT date_add('hour', x, cast(date(y) as timestamp))
FROM dataset

Or parse the string as timestamp:

SELECT date_add('hour', x, date_parse(y, '%Y-%m-%d'))
FROM dataset

Output:

_col0
2022-01-01 22:00:00.000

Adding hours to dates in Presto

1 Answers1