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

Piotr Findeisen · Answer 1 · 2020-08-18T15:11:55.443

8

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:

cast to varchar
parse_datetime with appropriate format
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.

edited Aug 18 '20 at 15:11

answered Dec 22 '18 at 16:27

Piotr Findeisen

19,480
2
52
82

This approach creates an issue where the month, for some reason, is always converted to January. The same is happening even in the answer. – Neelotpal Shukla Aug 18 '20 at 14:39
@NeelotpalShukla it was a wrong pattern. Updated. Thanks! – Piotr Findeisen Aug 18 '20 at 15:12

score 2 · Answer 2 · answered Dec 22 '18 at 12:26

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
                          );

score 1 · Answer 3 · answered Dec 22 '18 at 09:57

1

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)

answered Dec 22 '18 at 09:57

Derviş Kayımbaşıoğlu

28,492
4
50
72

score 0 · Answer 4 · answered Oct 13 '21 at 16:40

In addition, suppose we have date in format 20211011_1234 and we want one month older date and want back the original format, we can use the following formatting to convert date to int and vice versa.

select cast(date_format(
CAST(parse_datetime(cast(
split_part('20211011_1234', '_', 1) as varchar), 'yyyyMMdd') 
AS date) - interval '30' day ,'%Y%m%d') as int) as column_name

Format int as date in presto SQL

4 Answers4