Athena - convert string to timestamp
0
I use the below query in Athena to parse string so I could so some filtering on the timestamp.
The column event_time is a string in the format - 2023/04/28 15:32:39 UTC
select date_parse(event_time, '%Y/%m/%d %H:%i:%S UTC')
from "database"."table" ;
I get the error INVALID_FUNCTION_ARGUMENT: Invalid format: ""2023/04/28 15:32:39 UTC""
I don't see an issue with date_parse function because I am able to parse sample dates from the table
SELECT date_parse(TestData.MultiDateCol, '%Y/%m/%d %H:%i:%s UTC') as newDate
FROM
( SELECT '2023/04/28 15:32:39 UTC' AS MultiDateCol ) AS TestData
What am I doing wrong here
- 语言
- English
已提问 3 年前1.2万 查看次数
2 回答
- 最新
- 投票最多
- 评论最多
1
Can you check if the event_time column has the quotes in it? If I run the query below I get the same message that you get with the same pair of double quotes on either end.
SELECT date_parse(TestData.MultiDateCol, '%Y/%m/%d %H:%i:%s UTC') as newDate
FROM
( SELECT '"2023/04/28 15:32:39 UTC"' AS MultiDateCol ) AS TestData
INVALID_FUNCTION_ARGUMENT: Invalid format: ""2023/04/28 15:32:39 UTC""
0
Used the below DDL to create table w/o quotes
CREATE EXTERNAL TABLE `my_table`(
....
ROW FORMAT SERDE
'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES (
'escapeChar'='\\',
'quoteChar'='\"',
'separatorChar'=',')
STORED AS INPUTFORMAT
'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
's3://....'
已回答 3 年前
That is correct values have quotes in them- which was causing the confusion
Recreated the table w/o quotes which fixed the issues