11

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' ..

Piotr Findeisen
  • 19,480
  • 2
  • 52
  • 82
Latent
  • 556
  • 1
  • 9
  • 23

2 Answers2

34

date_diff

date_diff('second', min(creation_time),max(ending_time))
David דודו Markovitz
  • 42,900
  • 6
  • 64
  • 88
  • 1
    Thanks , seems like Athena have some different function that hiveql do no support .according Hiveql documentation that wouldnt work but..in Athena it did. – Latent Mar 20 '17 at 09:19
  • "Athena is based on the Presto distributed SQL engine" https://aws.amazon.com/blogs/aws/amazon-athena-interactive-sql-queries-for-data-in-amazon-s3/ – David דודו Markovitz Mar 20 '17 at 09:22
1

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)
leftjoin
  • 36,950
  • 8
  • 57
  • 116
  • 2
    `unix_timestamp` is apparently not supported in Athena or Presto, e.g. https://forums.aws.amazon.com/thread.jspa?threadID=265717 – nclark May 21 '20 at 18:47