Hive Query - How to compare a date from one table to see if its in between start and stop timestamp-CodePudding

Below is the sample of the query I am trying to run. What I need is - I only want to show the records from trip_3g table where oil_chng_date is between start and stop timestamp from mma table for a given VIN id.

SELECT trip_3g.vin, 
       trip_3g.start, 
       trip_3g.oil_flag, 
       trip_3g.oil_chng_date,
       mma.vin,
       mma.start,
       mma.end,
       mma.lifecycle_mode,
       mma.auth_mode   
FROM trip_3g
INNER JOIN mma ON trip_3g.vin = mma.vin
WHERE trip_3g.region = 'NA' AND trip_3g.oil_flag = 1
LIMIT 100;

CodePudding user response：

Try using JOIN with the conditions you specify:

SELECT trip_3g.vin,  trip_3g.start,  trip_3g.oil_flag, trip_3g.oil_chng_date,
       mma.vin, mma.start, mma.end, mma.lifecycle_mode, mma.auth_mode   
FROM trip_3g JOIN
     mma
     ON trip_3g.vin = mma.vin AND
        trip_3g.oil_chng_date BETWEEN mma.start and mma.end
WHERE trip_3g.region = 'NA' AND trip_3g.oil_flag = 1
LIMIT 100;

CodePudding user response：

WHERE ... AND DATEDIFF(day, trip_3g.oil_chng_date, mma.start) <= 0 AND DATEDIFF(day, trip_3g.oil_chng_date, mma.end) >= 0;