Time diff in Amazon Athena / Presto (seconds and minutes )

Question

I have a list of creation time stamps and ending time stamps , i would like to get the amount of seconds last from creation to ending . could not find any way to do that without using UNIX time stamp (which i dont have at the moment) .

something like that :

datediff('second',min(creation_time),max(ending_time))

creation_time = '2017-03-20 10:55:00' ..

Answer 1

date_diff

date_diff('second', min(creation_time),max(ending_time))

Answer 2

unix_timestam() function converts date to seconds passed from 1970-01-01

SELECT 
  (unix_timestamp('2017-03-20 10:55:00') - unix_timestamp('2017-03-20 10:56:00'))

OK
-60

Divide by 60 to get minutes

Edit: The solution above works in Hive. Presto does not have unix_timestamp as @nclark mentioned in the comment. There is to_unixtime function in Presto, it returns DOUBLE, so you need to cast it to bigint. The same logic in Presto:

CAST(to_unixtime(max(ending_time)) AS BIGINT) - CAST(to_unixtime(min(creation_time)) AS BIGINT)