Menu
I am trying to find a date difference In Impala. I have tried a few options. my most recent is below
ABS(dayofyear(CAST(firstdate AS TIMESTAMP)-dayofyear(CAST(seconddate AS TIMESTAMP)
an example of data looks like:
firstDate: 2017-11-25 secondDate: 2017-11-30
The solution that I am looking for in the above scenario would be five. The provided code would give me this answer, but the trouble comes in when the first date is in december and the second one ends in January.
Firstdate: 2016-12-30 seconddate:2017-12-30
with the above code this gives me an output of 358 days,the outcome that I would want to see is 8 days. This code is only looking at what day of the year this falls on, and is not able to consider moving from year to year. I know that there is no datediff function on Impala and I've also tried using just subtraction on the two timestamps but that didn't work either. I have scoured google and have come back empty any suggestions would be greatly appreciated
812
unix_timestamp(finish_time)-unix_timestamp(start_time) will give you the seconds between them.
The DATE type is available in Impala 3.3 and higher.
You should use trunc(timestamp, 'J') . See the documentation for Day, you're using 'Starting day of the week': trunc(timestamp, string unit) Purpose: Strips off fields from a TIMESTAMP value. Unit argument: The unit argument value is case-sensitive.
SQL
SELECT DATEDIFF(TO_DATE(firstdate), TO_DATE(seconddate));
Is this what you are looking for?
183
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
