Parsing time format with zone in presto sql

How to parse the following time format to timestamp in presto sql, more specifically AWS Athena?

Mon Feb 27 2023 06:21:22 GMT+0530 (India Standard Time),

Mon Feb 27 2023 00:37:44 GMT-0500 (Eastern Standard Time), etc

I tried this,

select date_parse(
    'Mon Feb 27 2023 06:21:22 GMT+0530 (India Standard Time)',
    '%a %b %d %Y %H:%i:%s GMT%z (%Z)')

but got the following error

INVALID_FUNCTION_ARGUMENT: Invalid format: "Mon Feb 27 2023 06:21:22 GMT+0530 (India Standard Time)" is malformed at "+0530 (India Standard Time)"

I'm not sure if the %z or %Z specifier is present in Presto sql.

Note that the timezone may be anything, not just GMT+0530, so i can't ignore the timezone.

Solution

First of all as far as I can see India Standard Time and Eastern Standard Time are not in the list of timezones supported by Athena, I suggest to remove this part from the string and then switch to parse_datetime which supports JodaTime’s DateTimeFormat:

-- sample data
with dataset(dt) as (
    values ('Mon Feb 27 2023 06:21:22 GMT+0530 (India Standard Time)'),
           ('Mon Feb 27 2023 00:37:44 GMT-0500 (Eastern Standard Time)')
)

-- query
SELECT
    PARSE_DATETIME(regexp_replace(dt, '\s*\(.*\)'), 'E MMM dd yyyy HH:mm:ss ''GMT''Z')
FROM dataset;

Output:

             _col0
--------------------------------
 2023-02-27 06:21:22.000 +05:30
 2023-02-27 00:37:44.000 -05:00