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.

score 33 · Answer 1 · edited Mar 28 '18 at 08:39

33

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

edited Mar 28 '18 at 08:39

Nick

138,499
22
57
95

answered Mar 28 '18 at 05:32

kokosing

5,251
5
37
50

1

What to do when the dataetime is string type and contains 29/09/2020 20:21 format? – A l w a y s S u n n y Sep 29 '20 at 18:16

score 9 · Answer 2 · answered May 06 '19 at 08:09

9

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

answered May 06 '19 at 08:09

Dheeraj Inampudi

1,227
15
11

Which is great except that, for some reason, Athena does not support Presto's "%i" reporting "Illegal pattern component: i". Same for %I. Less significantly the question uses a "T" instead of a space as the date/time separator. – Typhlosaurus Nov 20 '20 at 14:54
i used mention function but not working for leap year date_parse('02/29/1977','%m/%d/%Y') showing error – Rajesh Qa May 19 '22 at 05:34
@RajeshQa please refer to prestoDB documentation for corner cases. If you get the solution, please comment here. Thanks – Dheeraj Inampudi May 19 '22 at 12:18

Paulo Moreira · Answer 3 · 2021-08-24T20:28:44.520

6

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

edited Aug 24 '21 at 20:28

answered Aug 24 '21 at 20:22

Paulo Moreira

411
5
13

score -2 · Answer 4 · answered Mar 31 '18 at 15:34

-2

You can try something like below.

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

Demo

answered Mar 31 '18 at 15:34

Alpesh Jikadra

1,692
3
18
38

3

This works only when the input is a TIMESTAMP, not a VARCHAR – Shailesh May 15 '20 at 02:05

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

4 Answers4