Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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.

like image 350
Harshit Agrawal Avatar asked Mar 27 '18 13:03

Harshit Agrawal


People also ask

How do you change timestamp in Athena?

Athena requires the Java TIMESTAMP format. Use Presto's date and time function or casting to convert the STRING to TIMESTAMP in the query filter condition. For more information, see Date and time functions and operators in the Presto documentation.

How do I get the timestamp from a Presto date?

You can convert timestamp to date with cast(col as date) or date(col) .

Is Athena Presto?

Amazon Athena lets you deploy Presto using the AWS Serverless platform, with no servers, virtual machines, or clusters to setup, manage, or tune.


3 Answers

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(); 
like image 58
kokosing Avatar answered Oct 28 '22 20:10

kokosing


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

like image 33
Dheeraj Avatar answered Oct 28 '22 21:10

Dheeraj


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

like image 4
Paulo Moreira Avatar answered Oct 28 '22 22:10

Paulo Moreira