Impala date subtraction timestamp and get the result in equivalent days irrespective of difference in hours or year or days or seconds

user_11834668 0
Viewed 249

I want to subtract two date in impala. I know there is a datediff funciton in impala but if there is two timestamp value how to deal with it, like consider this situation:

select to_date('2022-01-01 15-05-53','yyyy-mm-dd HH24-mi-ss')-to_date('2022-01-01 15-04-53','yyyy-mm-dd HH24-mi-ss') from dual;

There is 1 minute difference and oracle would put the result as 0.000694444 days.

My requirement is if there is any such functionality in impala where I can subtract two timestamp value in the manner 'yyyy-mm-dd HH24-mi-ss', and get the result in equivalent days irrespective of if there is difference in days , year, hours, minute or seconds. Any difference should reflect in equivalent number of days.

Any other way where I can achieve the same thing, I am open to that as well.

Thank you in advance.

impala time date oracle
1 Answers

You can use unix_timestamp(timestamp) to convert both fields to unixtime (int) format. This is actually seconds from 1970-01-01 and very suitable to calculate date time differences in seconds. Once you have seconds from 1970-01-01, you can easily minus them both to know the differences. Your sql should be like this -

select 
unix_timestamp(to_timestamp('2022-01-01 15-06-53','yyyy-MM-dd HH-mm-ss'))  - 
unix_timestamp(to_timestamp('2022-01-01 15-05-53','yyyy-MM-dd HH-mm-ss')
) diff_in_seconds

Once youhave difference in seconds, you can easily convert them to minutes/hours/days - whatever format you want it.

user_64305230
Related