I need to build a query in presto that could look back the trailing 70 days, the table I am working with is storing the dates in the format of ‘YYYYMMDD’.
in postgresql, I can just simply write the where clause as
where date >= to_char(current_date - 70, 'YYYYMMDD')
and it’ll pull in the date 70 days ago in a YYYYMMDD format.
However, in PrestoSQL, that function doesn’t seem to exist, is there an alternative to this?
Advertisement
Answer
You can do this with date_format()
:
where date >= date_format(current_date - interval '70' day, '%Y%m%d')
Note that storing dates as strings is not a good practice at all – you should be using the proper date
-like datatype – and then you won’t need to do conversions at all.