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

Question

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.

Answer 1

Try to use from_iso8601_timestamp. Please visit below address to learn more about timestamp related functions: https://docs.starburstdata.com/latest/functions/datetime.html

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(); 

Answer 2

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

Answer 3

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:

https://prestodb.io/docs/current/functions/datetime.html#java-date-functions

https://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html

Answer 4

You can try something like below.

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

Demo