CODEWITHSUNDEEP
prestoamazon-athena
user2496011
user2496011

Reputation: 67

Athena (Presto) string to date conversion

I have the following string: '2019-05-17 04:44:00:000'. I want to convert it to a date so that I can query my table based on the timestamp.

I've tried the following which results in an error:

select street, city,
from_iso8601_timestamp(starttime)
from mydata where subtype='X';
INVALID_FUNCTION_ARGUMENT: Invalid format: "2019-05-17 04:44:00:000" is 
malformed at " 04:44:00:000"

SELECT street, city, date_parse(starttime,'%Y-%m-%d %H:%i:%s')
from mydata where subtype='X';
INVALID_FUNCTION_ARGUMENT: Invalid format: "2019-05-17 04:44:00:000" is malformed at " 04:44:00:000"

What is the correct way to convert this in Athena? Is there a millisecond parameter?

Upvotes: 1

Views: 7802

Answers (1)

jens walter
jens walter

Reputation: 14029

You can parse the given string with the following pattern.

'%Y-%m-%d %H:%i:%s:%f'

The %f stand for fraction of a second and resolves up to microseconds.

Overall this would lead to the following query.

SELECT date_parse('2019-05-17 04:44:00:000','%Y-%m-%d %H:%i:%s:%f')

For more information on that, you can have a look at the official docs.

Upvotes: 8

Related Questions