CODEWITHSUNDEEP
oracle-databasedateselectfilter
Imran Desai
Imran Desai

Reputation: 13

How to filter particular time period in oracle

I have a date and time column(Login_time) in this format '5/23/2018 3:35:18 PM'. I want to filter rows with August month and time period of 6.30 to 7.30. I am able to filter august month using Login_time >= to_date('08/1/2018','MM/DD/YYYY') and Login_time >= to_date('08/31/2018','MM/DD/YYYY') but now I want rows only between 6.30 am to 7.30 am.

Upvotes: 1

Views: 3909

Answers (4)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59476

You simply include the time:

Login_time >= to_date('08/01/2018 06:30:00','MM/DD/YYYY HH24:MI:SS') 
 and Login_time <= to_date('08/31/2018 07:30:59','MM/DD/YYYY HH24:MI:SS') 
  and to_char(login_time, 'hh24:mi:ss') >= '06:30:00'
  and to_char(login_time, 'hh24:mi:ss') <= '07:30:59'

or

EXTRACT(MONTH FROM CAST(Login_time AS TIMESTAMP)) = 8 
  and to_char(login_time, 'fmSSSSS') between 6*60*60 + 30*60 AND 7*60*60 + 30*60

Upvotes: 1

Sam M
Sam M

Reputation: 4166

I've taken a different approach to the "between 6:30 AM and 7:30 AM" requirement. In this answer, I subtract 30 minutes, then check if the hour value is either 6 or 7. I chose this approach as a variation to string comparison. I don't know if it performs better but it's an alternate approach. The casting to timestamp is necessary to extract the hour value.

 WHERE trunc(login_time) BETWEEN to_date('8/1/2018', 'mm/dd/yyyy') AND to_date('8/31/2018', 'mm/dd/yyyy')
   AND extract(hour FROM cast(login_time-(30/60/24) as timestamp)) IN (6,7)

If you are always going to be querying an entire month, this could be shortened to

 WHERE trunc(login_time, 'MONTH') = to_date('8/1/2018', 'mm/dd/yyyy')
   AND extract(hour FROM cast(login_time-(30/60/24) as timestamp)) IN (6,7)

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 94913

Be careful with date/time ranges. You'd usually want to include the start time and exclude an end time, e.g. all rows from 2018-08-01 until before 2018-09-01, thus including the whole last day, no matter how close to midnight. Here is the whole query:

select *
from mytable
where login_time >= date '2018-08-01' 
  and login_time < date '2018-09-01' 
  and to_char(login_time, 'hh24:mi') >= '06:30'
  and to_char(login_time, 'hh24:mi') < '07:31';

Upvotes: 5

Mureinik
Mureinik

Reputation: 311438

You could format the time portion of column to a string, and then compare it lexichograpically:

TO_CHAR(login_time, 'HH24:MI:SS') BETWEEN '06:30:00' AND '07:30:00'

Upvotes: 2

Related Questions