What is the equivalent of ORACLE's SYSDATE in Impala?

Question

I have a WHERE clause I am trying to convert from ORACLE SQL into Impala SQL. The col2 output looks something like 201406. I need to have this readable for Impala then format it then turn it into a string. Any help would be much appreciated!

WHERE Table1.col2 <= to_char( SYSDATE-7, 'YYYYMM')

Solution

current_timestamp() is an alias of now() so either of these can be used instead of sysdate

SYSDATE-7 is sysdate minus 7 days, so the equivalent of WHERE Table1.col2 <= to_char( SYSDATE-7, 'YYYYMM') is

WHERE Table1.col2 <= concat( cast(year(to_date( days_add(current_timestamp(),-7 ))) as string), cast(month(to_date( days_add(current_timestamp(),-7 ) )) )

or if the deduction is 7 months then:

concat( cast(year(to_date( months_add(current_timestamp(),-7 ))) as string), cast(month(to_date( months_add(current_timestamp(),-7 ) )) )

note, all untested, and I have used days_add() or months_add() as many folks prefer to use a minus 7 instead of a days_sub() with positive 7. However I'm making the assumption this will work (this works in MySQL which has similar date arithmetic functions).

Also note it is implied that your field Table1.col2 is a string type, if it is an integer then a slightly different solution is needed.

year( months_add(current_timestamp(),-7 ) ) * 100 + month(days_add(current_timestamp(),-7 ))

year( months_add(current_timestamp(),-7 ) ) * 100 + month(months_add(current_timestamp(),-7 ))

Comments

[Nazilla]

This looks like it should work because as you said the functionality is more or less the same as Mysql. It's not working for me just yet but I have come up with a really long hack/gross sql code for this with impala 1.1.3

[Paul Maxwell]

yep, it's going to be long and gross it appears, sorry but I cannot test it so I'm not sure how to help further