Format int as date in presto SQL

Question

I have an integer date column "date_created" storing values like...

20180527, 20191205, 20200208

And am wondering what the best way to parse as a date is so I could do something like this in a query...

select * from table where formatted(date_created) > formatted(date_created) - 90

(to return everything within the last 90 days)

I've found some similar examples that convert from date ints representing seconds or milliseconds, but none where the columns are essentially date strings stored as integers.

Appreciate any thoughts on the best way to achieve this

Answer 1

And am wondering what the best way to parse as a date is so I could do something like this in a query...

You can convert "date as a number" (eg. 20180527 for May 27, 2018) using the following:

1. cast to varchar
2. parse_datetime with appropriate format
3. cast to date (since parse_datetime returns a timestamp)

Example:

presto> SELECT CAST(parse_datetime(CAST(20180527 AS varchar), 'yyyyMMdd') AS date);
   _col0
------------
 2018-05-27

However, this is not necessarily the best way to query your data. By adapting your search conditions to the format of your data (and not vice versa), you can potentially benefit from predicate push down and partition pruning. See @GordonLinoff answer for information how to do this.

Answer 2

You can do the comparison in the world of integers or of dates. You might as well convert the current date minus 90 days to a number:

select t.*
from t
where date_created >= cast(date_format(current_date - interval '90 day',
                                       '%Y%m%d'
                                      ) as int
                          );

Answer 3

the below query is index friendly for any database since it does not use function on indexed column

select * from table where date_created > timestamp (formatted(date) - 90)