Snowflake DATEDIFF vs Hive

user_16447256 0
Viewed 56

Im trying to rewrite hive query into Snowflake Not sure that got this part correctly Hive query:

AND (    
    ((unix_timestamp()-unix_timestamp(fs.Created_Date,'MM/dd/yyyy HH:mm:ss'))/86400>=8 and st.mtcn is null)

OR  ((unix_timestamp(st.Created_Date,'MM/dd/yyyy HH:mm:ss')-unix_timestamp(fs.Created_Date,'MM/dd/yyyy HH:mm:ss'))/86400>=8)   

OR ((unix_timestamp(st.Created_Date,'MM/dd/yyyy HH:mm:ss')-unix_timestamp(mt.opacket_transactiontime,'yyyy-MM-dd HH:mm:ss'))/86400>=8)

)

AND (spc.spacket_disposition='A')     
AND unix_timestamp(mt.OPacket_TransactionTime) >= unix_timestamp('2021-07-11 00:00:00')

In snowflake I have tried this:

AND (    
    (DATEDIFF(day,current_Date,TO_DATE(fs.Created_Date))>=8 and st.mtcn is null)

OR  (DATEDIFF(day,TO_DATE(st.Created_Date),TO_DATE(fs.Created_Date))>=8)


OR (DATEDIFF(day,TO_DATE(st.Created_Date),TO_DATE(mt.opacket_transactiontime))>=8)

)

AND  spc.spacket_disposition='A'    
AND TO_DATE(mt.OPacket_TransactionTime) >= TO_DATE('2021-07-11')

However I got 0 results. So my guess is something wrong. Will appreciate your help.

snowflake-cloud-data-platform hive
1 Answers

unix_timestamp() - Returns seconds in INT format.
Whereas DATEDIFF by default returns difference of dates in INT format.
So, i think, hive considers date + time difference but snowflake consider only date part and time part is ignored. If you want to mimic hive logic in snowflake, you should use below code -

select datediff(second, CURRENT_TIMESTAMP, fs.Created_Date)/86400 >=8
user_64305230
Related