How to convert string into timestamp in Presto (Athena)?

I want to convert datatype of string (eg : '2018-03-27T00:20:00.855556Z' ) into timestamp (eg : '2018-03-27 00:20:00').

Actually I execute the query in Athena :

select * from tb_name where elb_status_code like '5%%' AND date between DATE_ADD('hour',-2,NOW()) AND NOW(); 

But I got error :

SYNTAX_ERROR: line 1:100: Cannot check if varchar is BETWEEN timestamp with time zone and timestamp with time zone

This query ran against the "vf_aws_metrices" database, unless qualified by the query. Please post the error message on our forum or contact customer support with Query Id: 6b4ae2e1-f890-4b73-85ea-12a650d69278.

Reason : Because date in string format and have to convert into timestamp. But I don't know how to convert it.

4 Answers

Try to use from_iso8601_timestamp. Please visit below address to learn more about timestamp related functions:

presto:tiny> select from_iso8601_timestamp('2018-03-27T00:20:00.855556Z'); _col0 ----------------------------- 2018-03-27 00:20:00.855 UTC (1 row) 

I believe you query shoul look like:

select * from tb_name where elb_status_code like '5%%' AND from_iso8601_timestamp(date) between DATE_ADD('hour',-2,NOW()) AND NOW(); 

I used the following way and it worked for me.

date_parse(eta,'%Y-%m-%d %h:%i:%s') 

Please go through the documentation below for detailed outputs

datetime in presto

I did:

select parse_datetime('2020-12-20 16:05:33','yyyy-MM-dd H:m:s') as dta;

parse_datetime(string, format) → timestamp with time zone

seealso:

You can try something like below.

SELECT DATE_FORMAT('2018-03-27T00:20:00.855556Z','%Y-%m-%d %H:%i:%s'); 

Demo