Menu
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' ..
969
Athena is a great choice for getting started with analytics if you have nothing set up yet. Redshift Spectrum is great for Redshift customers. Presto is for everything else, including large data sets, more regular analytics, and higher user concurrency.
AWS Athena is an interactive query service based on Presto that makes it easy to analyze data in Amazon S3 using standard SQL. Athena is serverless, so there is no infrastructure to manage.
LAST_DAY returns the date of the last day of the month that contains date . The return type is always DATE , regardless of the datatype of date .
SELECT current_date - interval '1' day AS yesterday_in_iso; I love the interval datatype, it reminds me of PostgreSQL.
date_diff
date_diff('second', min(creation_time),max(ending_time))
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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
