Presto: Extracting date from a timestamp like "2017-11-15T11:18:42.573Z"?

[tapaswenipathak]

I had a column with timestamp 2017-11-15T11:18:42.573Z format, searching in the documentation figured out that parse_datetime parses the timestamp. Parsed the timestamp as parse_datetime(field_name, 'YYYY-MM-DD''T''HH:mm:ss.SSS''Z') as date which gave the timestamp in UTC format. To extract date out of the timestamp I cast it to date. The part of query looked like

CAST(parse_datetime(field_name, 'YYYY-MM-DD''T''HH:mm:ss.SSS''Z') as date) as date,

Is there any predefined/inbuilt function which can do this for me? I tried few different functions available here.

I also tried from_iso8601_timestamp() on the timestamp value, query was like
CAST(from_iso8601_timestamp(field_name) as date) as date, was doing this in AWS Athena in Mode, this returned an error as

[Simba][AthenaJDBC](100071) An error has been thrown from the AWS Athena client.

I read the contributing guidelines before opening this issue, please feel free to close if such question issues are not supposed to be open here, I'll then try on SO, great if can get some information about this.

[nezihyigitbasi]

from_iso8601_timestamp should work, just tried this with the latest release:

select cast(from_iso8601_timestamp('2017-11-15T11:18:42.573Z') as date);
   _col0
------------
 2017-11-15
(1 row)

[findepi]

Looks this has been answered.

[msparker23]

I have tried to do this but it throws another error: Invalid format: "2019-01-29 08:55:29.867" is malformed at " 08:55:29.867"

I think this is the correct function as the data is a string with format like this 2017-08-21T01:42:39.263Z

Any ideas?

[bhrd]

@msparker23 - Only with a T, the timestamp is of ISO-8601 format. For your input 2019-01-29 08:55:29.867, SELECT CAST('2019-01-29 08:55:29.867' as TIMESTAMP) should work.

[naveen09]

How about for "2019-07-17 14:31:01.000000000 +00:00 ?

[avanibhatnagar]

I'm working with a table where the date column has values such as "3/10/2019 11:31 PM EDT"

I'm able to work with values without the timezone using date_parse("fieldname", '%m/%d/%Y %H:%i %p')

But here since there is a timezone I'm kind of stuck.. I've tried:

date_parse("fieldname",  '%m/%d/%Y %H:%i %p Z')
date_parse("fieldname",  '%m/%d/%Y %H:%i %p %z') 

And some modifications of parse_datetime as well. But I can't get around this.

[davidmason]

@avanibhatnagar I am having the same problem where I have a time zone specified but can't parse it. Did you learn anything about getting that working?