CODEWITHSUNDEEP
sqloracle-databasedatedatetime
Marcus Leon
Marcus Leon

Reputation: 56659

Oracle query on time (and not date)

How can you query on just the time portion of an Orace date field. Ie:

select * from mytable 
where 
date_column < '05:30:00'

Want the query to return any rows where the time represented by date_column is less than 5:30 regardless of the date.

Upvotes: 1

Views: 238

Answers (3)

Alex Poole
Alex Poole

Reputation: 191235

You can see how far the date is from midnight, and filter on that:

select * from mytable 
where date_column - trunc(date_column) < 5.5/24

The date_column - trunc(date_column) calculation will give you a fraction of a day, as is normal for date arithmetic. The 5.5/24 is the fraction of the day represented by the time at 05:30; 5.5 hours out of 24 hours.

If the column was a timestamp instead of a date you'd see an interval data type as the result of the subtraction. You can use an interval literal anyway if you prefer or find it easier to understand than 5.5/24 (or have more complicated times to compare, which are harder to express as a fraction):

select * from mytable 
where date_column < trunc(date_column) + interval '0 05:30:00' day to second;

This way round you're comparing the date in your column with the truncated date (i.e. midnight on that day) with 5 hours 30 minutes added to it, which is 05:30 the same day.

Quick demo with simple data in a CTE, and a third very slight variant, but they all get the same result:

with mytable (date_column) as (
  select to_date('2016-04-15 05:29:29', 'YYYY-MM-DD HH24:MI:SS') from dual
  union all select to_date('2016-04-14 05:29:29', 'YYYY-MM-DD HH24:MI:SS') from dual
  union all select to_date('2016-04-15 05:30:30', 'YYYY-MM-DD HH24:MI:SS') from dual
)
select * from mytable 
where date_column < trunc(date_column) + 5.5/24;

DATE_COLUMN       
-------------------
2016-04-15 05:29:29
2016-04-14 05:29:29

Note though that any manipulation of the column like this will prevent an index being used. If you have to do this regularly it might be worth adding a virtual column/index which does that calculation.

Upvotes: 2

Olafur Tryggvason
Olafur Tryggvason

Reputation: 4874

You need to cast the time back to date, otherwise you're simply doing string comparison. Oracle doesn't really have a clever way of doing this. Simplest to cast both dates to the same day and do the comparison.

I.e.

select *
  from mytable
 where to_date ('01.01.2000 ' || to_char (date_column, 'hh24:mi:ss'), 'dd.mm.yyyy hh24:mi:ss') <
          to_date ('01.01.2000' || '05:30:00', 'dd.mm.yyyy hh24:mi:ss')

Upvotes: 0

Rahul Tripathi
Rahul Tripathi

Reputation: 172378

You can try like this:

select * from mytable 
where 
to_char( date_column, 'HH24:MI:SS' ) < '05:30:00'

Upvotes: 3

Related Questions