1

I have a time (without date component) like 9am, let's say it's the current time. I want to query all entrys from a table where this time is between a Start and a End time stored as Timestamp.

How do I do this? Should I store it differently?

I tried something like SELECT * FROM tab1 WHERE (current_timestamp - current_date) BETWEEN starttime AND endtime; but that doesn't work because the difference calculated is always (almost) zero. I could do something with the EXTRACT-command, but that seems to be a little complicated... Isn't there an easy way to do this?

Basically, they are stored appointments.

Thanks in advance.

user1054247
  • 145
  • 1
  • 2
  • 6

3 Answers3

4

Try:

SELECT * FROM tab1 
WHERE (current_timestamp - TRUNC(current_date)) BETWEEN starttime AND endtime
2

these are two options, and to me, extract does not seem to be complicated

where 16 between extract(hour from start_ts) and extract(hour from end_ts)
where '16' between to_char(start_ts, 'hh24') and to_char(end_ts, 'hh24')

Note, that if you put an ordinary index on (start_ts, and_ts), then this query will not use it, instead it will scan the full table. You'll need function based index to avoid full table scan http://www.dba-oracle.com/t_garmany_easysql_function_based_indexes_fbi.htm

There is a third option which could use ordinary index, but this is not exactly what you've asked for, because here you'll have to provide the day in your where, not just the hour

where to_timestamp('2011.12.06. 17', 'yyyy.mm.dd. hh24') between start_ts and end_ts

Of course a fourth option would be storing directly start and end hours instead of timestamp values.

heres the code I tested them with

select * from 
(select
1 as id,
to_timestamp('2011.12.06. 16:10:32', 'yyyy.mm.dd. hh24:mi:ss') as start_ts, 
to_timestamp('2011.12.06. 16:18:32', 'yyyy.mm.dd. hh24:mi:ss') as end_ts
from dual
union
select 
2 as id,
to_timestamp('2011.12.06. 16:20:32', 'yyyy.mm.dd. hh24:mi:ss') as start_ts, 
to_timestamp('2011.12.06. 17:18:32', 'yyyy.mm.dd. hh24:mi:ss') as end_ts
from dual
union
select 
3 as id,
to_timestamp('2011.12.06. 17:30:32', 'yyyy.mm.dd. hh24:mi:ss') as start_ts, 
to_timestamp('2011.12.06. 18:18:32', 'yyyy.mm.dd. hh24:mi:ss') as end_ts
from dual
)
where 16 between extract(hour from start_ts) and extract(hour from end_ts)
--where '16' between to_char(start_ts, 'hh24') and to_char(end_ts, 'hh24')
--where to_timestamp('2011.12.06. 17', 'yyyy.mm.dd. hh24') between start_ts and end_ts
;
bpgergo
  • 15,669
  • 5
  • 44
  • 68
  • All right. Just to clarify: Mark's answer is great for the current time scenario. Within my answer, the third option is equivalent to Mark's answer. – bpgergo Dec 06 '11 at 15:50
0

The to_char function is always helpful when it comes to comparing dates. If you only want to compare the time component of a timestamp use

to_char(your_time, 'hh24mm')
TPete
  • 2,049
  • 4
  • 24
  • 26