英文:
Parsing time format with zone in presto sql
问题
如何将以下时间格式解析为Presto SQL中的时间戳，更具体地说是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)，等等
我尝试了这个：
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)')
但出现了以下错误：
INVALID_FUNCTION_ARGUMENT: Invalid format: "Mon Feb 27 2023 06:21:22 GMT+0530 (India Standard Time)" is malformed at "+0530 (India Standard Time)"
我不确定Presto SQL中是否存在%z或%Z指示符。
请注意，时区可能是任何时区，而不仅仅是GMT+0530，所以我不能忽略时区。
英文:
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.
答案1
得分: 1
首先，就我所见，印度标准时间和东部标准时间似乎不在Athena支持的时区列表中，建议从字符串中移除此部分，然后使用支持JodaTime的parse_datetime进行转换：
-- 示例数据
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)')
)
-- 查询
SELECT
PARSE_DATETIME(regexp_replace(dt, '\s*\(.*\)'), 'E MMM dd yyyy HH:mm:ss ''GMT''Z')
FROM dataset;
输出：
_col0
--------------------------------
2023-02-27 06:21:22.000 +05:30
2023-02-27 00:37:44.000 -05:00
英文:
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论