Merging two columns in hive and use between operat

问题:

I have a login details table in hive with columns

(DATE, TIME, USER)

I am trying to write a query which can select users who have logged in between two dates with time also taking in consideration. For example: I want to know the users who have logged in between 10-12-2012 02:30:00 and 28-12-2012 16:20:00. Dates in DD-MM-YYYY and Time in HH:MM:SS format.

I am able to execute

select * from test_table where time between "02:30:00" and "16:20:00" ;

select * from test_table where date between "10-12-2012" and "28-12-2012" ;

But i am not getting how to take both date and time columns into consideration while fetching the required result. Please guide me. Thanks in advance

回答1:

SELECT * FROM test_table
WHERE date BETWEEN "11-12-2012" AND "27-12-2012"
OR (date = '10-12-2012' and  time >= '02:30:00')
OR (date = '28-12-2012' and  time <= '16:20:00');