CODEWITHSUNDEEP
sqlstringdatetimeprestoamazon-athena
Rupz
Rupz

Reputation: 45

Athena SQL date

I queried the Athena database to get starttime and endtime. And I'm trying find the duration between start time and end time, and. used date diff function to get that information. But I guess the datatype and format of date in not enabling to use date diff.

Here is the query: SELECT, json_extract_scalar(eventdata, 'starttime') AS starttime, json_extract_scalar(eventdata, 'endtime') AS endtime FROM table1

Here is the sample data:

enter image description here

How can I find the duration? Any help is appreciated. Thank you

Upvotes: 0

Views: 361

Answers (1)

GMB
GMB

Reputation: 222672

You can use from_iso8601_timestamp() to turn these formated strings to timestamps. Then you can use date_diff():

select 
    json_extract_scalar(eventdata, 'starttime') as starttime, 
    json_extract_scalar(eventdata, 'endtime') as endtime,
    date_diff(
        'second',   -- or another unit you like
        from_iso8601_timestamp(json_extract_scalar(eventdata, 'endtime')),
        from_iso8601_timestamp(json_extract_scalar(eventdata, 'starttime'))
    ) diff_seconds
from table1

Upvotes: 1

Related Questions