CODEWITHSUNDEEP
sqlhive
Pyd
Pyd

Reputation: 6159

SQL Date Range using two columns

I have two columns in Hive table namely dt and hr, I want to get dt and hr between two given dt's and hr values

For Example : dt='2019-01-10' and '2019-01-15' so I want to get the dt ranges So I will query like select * from table_name where dt >='2019-01-10' and dt<='2019-01-15';how to achieve the same with one more column hr something like below:

select * from table_name where (dt >='2019-01-10' and hr >='05') and (dt<='2019- 01-15' and hr <='15');

But the above query doesn't work as expected which returns hr>='05' for all the dates But I want all the hr(00 to 23) for the dates in between 2019-01-10 and 2019-01-15

Upvotes: 0

Views: 1663

Answers (3)

jarlh
jarlh

Reputation: 44795

Simply add a condition for those days without hr restriction:

select * from table_name
where ((dt >= '2019-01-10' and hr >= '05') and (dt <= '2019-01-15' and hr <='15'))
   or (dt > '2019-01-10' and dt < '2019-01-15')

Or, does Hive perhaps support "Row and table constructors":

select * from table_name
where (dt, hr) >= ('2019-01-10', '05') and (dt, hr) <= ('2019-01-15', '15')

Upvotes: 0

forpas
forpas

Reputation: 164194

You must check 3 conditions combined by or.
If dt is '2019-01-10' then hr must be >= '05'.
If dt is '2019-01-15' then hr must be <= '15'.
For any other dt between '2019-01-10' (exclusive) and '2019-01-15' (exclusive)
the value of hr should not be checked.

select * 
from table_name 
where 
  (dt ='2019-01-10' and hr >= '05') 
  or
  (dt ='2019-01-15' and hr <= '15')
  or
  (dt > '2019-01-10' and dt < '2019-01-15')

Alternative solution:

select * 
from table_name 
where 
  concat(dt, ' ', hr) >= concat('2019-01-10', ' ', '05') 
  and
  concat(dt, ' ', hr) <= concat('2019-01-15', ' ', '15')

If you can use between it's even better:

select * 
from table_name 
where 
  concat(dt, ' ', hr) between 
  concat('2019-01-10', ' ', '05') and concat('2019-01-15', ' ', '15')

Upvotes: 1

Krishna508
Krishna508

Reputation: 9

why dont you add a new column and join the dt and hr

update #tab set datetime1= CAST(CONCAT(date1, ' ', time1) AS DATETIME2(7))

Later you can select the datetime range from this newly added column.

If you dont want to add any new column to your table, insert the values in your table to a temp table and join the date time in that temp table.

Upvotes: 0

Related Questions