CODEWITHSUNDEEP
sqlamazon-web-servicesamazon-athena
Stupid420
Stupid420

Reputation: 1419

How to make an SQL query AWS Athena Presto compatible

I am having an SQL query as follows.

EXTRACT(EPOCH FROM (i.intime-p.dob)::INTERVAL)/86400 as age

It first subtracts two time stamps i.intime-p.dob and then apply extract, epoch, interval and divide by 86400 to compute it as age.

It should be noted that p.dob column is in 2138-07-17 format and i.intime is in 2138-07-17 21:20:07 format.

I used the following modified query in AWS Athena to but I am not sure If it is doing the same function as the original EXTRACT(EPOCH FROM (i.intime-p.dob)::INTERVAL)/86400 as age.

My modified AWS Athena query is under.

date_diff('second',  p.dob, i.intime) as age

Can anyone confirm If both the queries, (the original one and the modified AWS athena one) are generating same values of age?

I don't think so they are generating the same values.

My goal is to use AWS athena to replicate the result of simple EXTRACT(EPOCH FROM (i.intime-p.dob)::INTERVAL)/86400 as age

Upvotes: 0

Views: 329

Answers (1)

John Rotenstein
John Rotenstein

Reputation: 269360

It appears that you wish to determine the number of days between two dates.

You could simply use:

SELECT DATE_DIFF('day', p.dob, i.intime)

Here's a test:

SELECT DATE_DIFF('day', CAST('2019-01-01' AS DATE), CAST('2020-01-01' AS DATE))

Result : 365

SELECT DATE_DIFF('day', CAST('2020-01-01' AS DATE), CAST('2021-01-01' AS DATE))

Result: 366 (Leap year!)

Upvotes: 1

Related Questions