Date string

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. Below we’ll discuss how to handle Presto date functions and date strings.

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 a Presto insert:

Date string
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 the Presto 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 for a date string is to use the from_iso8601_date() function:

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

>> Looking for an easy way to get up and running with Presto? Take a look at Ahana Cloud. <<

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! 

If you’re looking for a more comprehensive dive into operating Presto, download our free ebook: Learning and Operating Presto

Want more Presto tips & tricks in your inbox? Sign up for our Presto community newsletter.

Common Questions & Errors with Date String

Why am I getting the following error: ‘>’ cannot be applied to date, varchar (10)

Answer: You are trying to compare a string literal in your query to a date type in your schema. The operator in the error can be any operator >,=,<,>=,<=. To fix that, you need to cast the string to a date type or use a function to parse the string to date.

Example error: select now() as time_now where current_date = '2021-06-01'

Example solution: 

Option 1: Cast 
select now() as time_now where current_date > date '2021-06-01'

Option 2: Cast Explicit
select now() as time_now where current_date > Cast ( '2021-06-01' as Date)

Option 3: Parse iso8601 date
select now() as time_now where current_date > from_iso8601_date('2021-06-01')

Option 4: MySQL parser, Non Standard Date format using date_parse 
select now() as time_now where current_date > date_parse('01-06-2021','%d-%m-%Y')

Option 5: Java DataTime for parser, Non Standard Date format using date_parse 
select now() as time_now where current_date > parse_datetime('01-06-2021','dd-MM-YYYY')

How do I convert string with timestamp to a date?

Answer: You are trying to accurately convert a string of the format such as ‘2021-05-13T19:02:57.415006+01:00’ to date and use it in your query for comparison. You need to parse the string using either date_parse or parse_datetime

Example problem: Need to convert the following timestamp to a date string: ‘2021-05-13T19:02:57.415006+01:00’

Example solution: 

Option 1: MySQL parser, Non Standard Date format using 
select date_parse('2021-05-13T19:02:57.415006','%Y-%m-%dT%h:%i:%s.%f') AT TIME ZONE '+01:00'

Option 2: Java DateTime format parser, Non Standard Date format using 
select parse_datetime('2021-05-13T19:02:57.415006+01:00','YYYY-MM-dd''T''HH:mm:ss.SSSSSSZ')

Want more Presto tips & tricks? Sign up for our Presto community newsletter.

Presto community newsletter