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