Athena - convert string to timestamp

Question

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

Madhav Vishnubhatta · Accepted Answer

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""

Balu Mathew · Answer

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://....'
```

Athena - convert string to timestamp

新增您的答案

相關內容