Help us improve the AWS re:Post Knowledge Center by sharing your feedback in a brief survey. Your input can influence how we create and update our content to better support your AWS journey.
Athena - convert string to timestamp
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
- 新しい順
- 投票が多い順
- コメントが多い順
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""
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://....'
That is correct values have quotes in them- which was causing the confusion
Recreated the table w/o quotes which fixed the issues