FREE TOOL

SQL Date Format Converter

Paste a date, a timestamp or a date error and get the date_parse, from_iso8601_timestamp, TO_TIMESTAMP and to_timestamp expressions for Athena, Redshift and Glue.

EXAMPLES

ERRORS

COLUMN

SQL expressions →Format strings compared →

See result ↓

Athena

from_iso8601_timestamp(event_time)

CAST(from_iso8601_timestamp(event_time) AT TIME ZONE 'UTC' AS timestamp)

CAST(from_iso8601_timestamp(event_time) AS date)

date_format(current_timestamp AT TIME ZONE 'UTC', '%Y-%m-%dT%H:%i:%sZ')

Rows in another format make the whole query fail; wrap the expression in try(...) to get NULL for them instead.

Redshift

CAST(event_time AS TIMESTAMPTZ)

CAST(CAST(event_time AS TIMESTAMPTZ) AS DATE)

TO_CHAR(SYSDATE, 'YYYY-MM-DD"T"HH24:MI:SS"Z"')

TIMEFORMAT 'auto'

With 'auto', check that COPY read every row: SELECT colname, raw_field_value, err_reason FROM stl_load_errors ORDER BY starttime DESC LIMIT 10;

Glue (Spark)

to_timestamp(event_time, "yyyy-MM-dd'T'HH:mm:ssXXX")

to_date(event_time, "yyyy-MM-dd'T'HH:mm:ssXXX")

F.to_timestamp(F.col('event_time'), "yyyy-MM-dd'T'HH:mm:ssXXX")

date_format(current_timestamp(), "yyyy-MM-dd'T'HH:mm:ssXXX")

A value that does not match the pattern gives NULL - or the error "Fail to parse ... in the new parser" when the Spark 2 parser would have read it.

Athena, Redshift and Glue are core AWS Data Engineer Associate exam topicsTry free DEA-C01 practice questions with answers and explanations.DEA-C01 questions →

Date functions in Athena, Redshift and Glue

Athena (engine version 3) runs Trino SQL, Redshift follows PostgreSQL, and Glue jobs run Spark SQL. The same task has a different function in each:

Task	Athena	Redshift	Glue (Spark)
String to timestamp	`date_parse(s, '%Y-%m-%d %H:%i:%s')`	`TO_TIMESTAMP(s, 'YYYY-MM-DD HH24:MI:SS')`	`to_timestamp(s, 'yyyy-MM-dd HH:mm:ss')`
ISO 8601 string to timestamp	`from_iso8601_timestamp(s)`	`CAST(s AS TIMESTAMPTZ)`	`to_timestamp(s)`
Epoch seconds to timestamp	`from_unixtime(n)`	`TIMESTAMP 'epoch' + n * INTERVAL '1 second'`	`timestamp_seconds(n)`
Timestamp to string	`date_format(ts, '%Y-%m-%d')`	`TO_CHAR(ts, 'YYYY-MM-DD')`	`date_format(ts, 'yyyy-MM-dd')`
Timestamp to epoch seconds	`to_unixtime(ts)`	`EXTRACT(EPOCH FROM ts)`	`unix_seconds(ts)`
Start of the day	`date_trunc('day', ts)`	`DATE_TRUNC('day', ts)`	`date_trunc('day', ts)`
Add 7 days	`date_add('day', 7, ts)`	`DATEADD(day, 7, ts)`	`ts + INTERVAL 7 DAYS`
Days between two dates	`date_diff('day', start, end)`	`DATEDIFF(day, start, end)`	`datediff(end, start)` - the end comes first
Last 7 days	`ts > current_timestamp - INTERVAL '7' DAY`	`ts > DATEADD(day, -7, GETDATE())`	`ts > current_timestamp() - INTERVAL 7 DAYS`

Format strings compared

Athena alone has two format languages: MySQL-style for date_parse and date_format, Joda-style for parse_datetime and format_datetime. Spark's patterns use the same letters as Joda for everything below except offsets.

Part	Athena date_parse	Athena parse_datetime, Spark	Redshift
Year (2024)	`%Y`	`yyyy`	`YYYY`
Month (01)	`%m`	`MM`	`MM`
Month name (Jan)	`%b`	`MMM`	`Mon`
Day (15)	`%d`	`dd`	`DD`
Hour, 00-23	`%H`	`HH`	`HH24`
Hour, 01-12	`%h`	`hh`	`HH12` or `HH`
Minutes	`%i`	`mm`	`MI`
Seconds	`%s`	`ss`	`SS`
Fraction (.123)	`%f`	`SSS`	`MS`, `US` for six digits
AM/PM	`%p`	`a`	`AM`
Offset (+01:00, Z)	none	`ZZ` in Joda, `XXX` in Spark	none in TO_TIMESTAMP

The minutes are the classic trap: %M in date_parse is the month name, so date_format(ts, '%H:%M') prints "10:January"; MM in Spark is the month, so HH:MM gives NULL. In Redshift, HH is the 12-hour clock in functions such as TO_CHAR, but the 24-hour clock in COPY's TIMEFORMAT.

Common date mistakes

Milliseconds read as seconds. from_unixtime(1705314600123) is the year 56009; divide by 1000 first, or use timestamp_millis in Spark.
YYYY and DD in Spark. YYYY is rejected by Spark 3 ("Fail to recognize pattern"); DD is the day of the year, which matches the day of the month in January only - the query works until February.
A TIMESTAMP column over CSV or JSON files. Athena reads a TIMESTAMP in text files only as yyyy-MM-dd HH:mm:ss; other formats fail with HIVE_BAD_DATA. Declare the column as string and convert it in the query or in a view.
Comparing a timestamp with a string. Athena does not convert '2024-01-15' into a date; write DATE '2024-01-15' or TIMESTAMP '2024-01-15 00:00:00'.
The strict Spark 3 parser. Since Glue 3.0, the pattern has to match the whole value: dd fails on a one-digit day, and yyyy-MM-dd fails on a value with a time.

Frequently asked questions

How do I convert a string to a timestamp in Athena?

For ISO 8601 (2024-01-15T10:30:00Z), use from_iso8601_timestamp(s). For 2024-01-15 10:30:00, CAST(s AS timestamp) is enough. For anything else, use date_parse(s, format) with a MySQL-style format - paste one of your values above to get it.

How do I convert epoch milliseconds to a date in Athena?

from_unixtime(ms / 1e3). Dividing by 1e3, a double, keeps the milliseconds; wrap it in CAST(... AS date) for the date alone.

Why does Athena return a timestamp with time zone?

from_iso8601_timestamp, parse_datetime and from_unixtime return timestamp with time zone. To get a plain timestamp in UTC, use CAST(x AT TIME ZONE 'UTC' AS timestamp).

Is it safe to paste my data here?

Yes. Everything runs in your browser: nothing you enter is uploaded, processed on a server or stored. The page only counts that it was used, which kind of value it read and which error it recognized, never the text.

References

Functions in Athena engine version 3
Trino date and time functions and operators
Redshift datetime format strings
Redshift COPY DATEFORMAT and TIMEFORMAT strings
Spark datetime patterns
Migrating AWS Glue jobs to version 3.0