Athena Timestamp

Question

What is the appropriate format for my datetime? I've tried several combinations and getting various errors. The data is a string and here is an example: "2022-10-28T00:00:00Z"

Neither of these work:

`WHERE MONTH(parse_datetime(start, 'yyyy-MM-dd"T"HH:mm:ss"Z"')) = 12

`WHERE MONTH(parse_datetime(start, 'yyyy-MM-dd HH:mm:ss')) = 12

Answer 1

You need to use single quotes (') to escape symbol when using Java date functions. To add it to the format string you need to escape it with another one:

select parse_datetime('2023-01-30T20:00:02Z', 'yyyy-MM-dd''T''HH:mm:ss''Z''');

Output:

_col0
2023-01-30 20:00:02.000 UTC

Note that in this case you can just use from_iso8601_timestamp function, which should be more correct approach in general:

select from_iso8601_timestamp('2023-01-30T20:00:02Z');