Presto/Athena Examples: Date and Datetime functions

Last updated:
Table of Contents

Does your date look like this "2009-10-30T17:59:55.047"? This is a ISO 8601 Timestamp

Convert string to date, ISO 8601 date format

Format: yyyy-mm-dd

select from_iso8601_date('2018-12-11') from mytable
-- 2018-12-11

Convert string to datetime, ISO 8601 timestamp format

Format: yyyy-mm-dd'T'hh:mm:ss.SSS

select from_iso8601_timestamp('2009-10-30T17:59:55.047') from mytable
-- 2009-10-30 17:59:55.047 UTC

Convert string to date, custom format

See all date specifiers here

select date_parse('27/01/1990','%d/%m/%Y') from mytable
-- 1990-01-27 00:00:00.000

Get year from date

See all extraction functions here

select year(date_col) from mytable

Get month from date

select month(date_col) from mytable

Get day of month from date

select day(date_col) from mytable

Dialogue & Discussion