How to select record which are not between two time-Seconds range in ORACLE

Question

I have one Oracle table in which ORACLE time column stored in following format.

for 12 AM - 0

for 1 AM - 3600

for 2 AM - 7200 . . .

for 23 PM 82800

i.e. if time is 3 AM then it will be stored as 10800 if time is 11 PM then it will be stored as 82800.

Now I want to select records which are not between 11 PM to 3 AM.

SELECT TIMECol
FROM MyTable
WHERE IBS_To_Time(TimeCol) NOT BETWEEN IBS_To_Time(10800) AND IBS_To_Time(82800)

IBS_To_Time will Converts seconds to 'HH:MM:SS' format But this query does not display expected result. Please help for same. What should be the optimized and best approach.

Answer 1

So it means you want to select records which are between 3am to 11 pm (excluding)

select timecol from mytable
where timecol between 10800+1 and 82800-1

Answer 2

You could convert TIMECol to seconds and deal with it in that fashion in your WHERE clause:

SELECT to_number(to_char(TIMECol,'sssss')) timecol_as_seconds
  FROM MyTable

Answer 3

SELECT ibs_to_time(TIMECol)
FROM MyTable
WHERE ( to_number(TIMECol)  NOT BETWEEN 0 AND  10400 ) AND  (to_number(TIMECol)  NOT BETWEEN 82800 AND  86400)