How To Convert Date String to Presto Date Format

PrestoDB supports ANSI SQL and includes support for several SQL dialects, so it’s straightforward to convert a date string to date format and vice-versa in various formats. 

If you have ISO 8601 format dates or timestamps like “2020-09-16 14:27:00” it’s very straightforward – use the date() function. Let’s test that:

presto:default> create table datetest1 (s1 varchar);
CREATE TABLE
presto:default> insert into datetest1 values ('2020-09-16');
INSERT: 1 row
 
presto:default> select date(s1) as myDate from datetest1;
   myDate   
------------
 2020-09-16 

An alternative is to use cast(x as date) – it’s exactly the same:

presto:default> select CAST(s1 AS date) as myDate from datetest1;
   myDate   
------------
 2020-09-16 

Another alternative is to use the from_iso8601_date() function:

presto:default> select from_iso8601_date(s1) as myDate from datetest1;
   myDate   
------------
 2020-09-16 

Ok but what if my dates are not ISO 8601? Like “16/09/2020” which is common in Europe and APAC? Let’s set-up some string-based test data again:

presto:default> create table datetest2 (s1 varchar);
CREATE TABLE
presto:default> insert into datetest2 values ('16/09/2020');
INSERT: 1 row

Did you know PrestoDB supports MySQL dialect?  Let’s convert our string to a timestamp using the MySQL date_parse() function, which returns a timestamp type:

presto:default> select date_parse(s1, '%d/%c/%Y') as myTimestamp from datetest2;
       myTimestamp       
-------------------------
 2020-09-16 00:00:00.000 

That works. And if we want that as a date instead of a timestamp, no problem just cast it:

presto:default> select cast(date_parse(s1, '%d/%c/%Y') as date) as myDate from datetest2;
   myDate   
------------
 2020-09-16 

Date_parse() is powerful:  For example you can use %c for non zero-padded month numbers, %e for non zero-padded days of the month, and it can handle two or four digit years. If your separators are “.” instead of “/” it can handle that. And if you have timestamps with “AM” or “PM” present then its ‘%p’ specifier can handle that too. 

So that’s how you convert date string to date format!  You can find more information on datetime functions in the PrestoDB docs at https://prestodb.io/docs/current/functions/datetime.html