How do I convert timestamp to date with Presto?
Luckily Presto has a wide range of conversion functions and they are listed in the docs. Many of these allow us to specifically convert a timestamp type to a date type.
To test this out we can use Presto’s built-in
current_timestamp function (an alias for the now() function) that returns the current system time as a timestamp:
presto> select current_timestamp as "Date & Time Here Now"; Date & Time Here Now --------------------------------------- 2020-11-27 13:20:04.093 Europe/London (1 row)
To grab the date part of a timestamp we can simply cast to a date:
presto> select cast(current_timestamp as date) as "Today's date"; Today's date -------------- 2020-11-27 (1 row)
Or we can use date() which is an alias for cast(x as date):
presto> select date(current_timestamp) as "Today's date"; Today's date -------------- 2020-11-27 (1 row)
We can use date_format() which is one of Presto’s MySQL-compatible functions:
presto:demo> select date_format(current_timestamp, '%Y%m%d') as "Today's date"; Today's date ---------- 20201127 (1 row)
Finally we can use format_datetime() which uses a format string compatible with JodaTime’s DateTimeFormat pattern format:
presto:demo> select format_datetime(current_timestamp, 'Y-M-d') as "Date"; Date ---------- 2020-11-27 (1 row)
The above 5 examples should allow you to convert timestamps to dates in any scenario.
Looking to learn more about Presto?
Download the free Ebook, Learning and Operating Presto
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.
select now() as time_now where current_date = '2021-06-01'
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
Example problem: Need to convert the following timestamp to date: ‘2021-05-13T19:02:57.415006+01:00’
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')
Looking for related content? How To Convert Date String to Presto Date Format
Want more Presto tips & tricks? Sign up for our Presto community newsletter.