CODEWITHSUNDEEP
sqloracle-databasetimestamp
asteri
asteri

Reputation: 11572

Oracle SQL - Determine if a timestamp falls within a range, excluding date

I'm looking for a way to determine if a timestamp falls between two times, regardless of the date in that timestamp. So for example, if the time in the timestamp falls between '00:00:00.000' (midnight) and '01:00:00.000' (1 A.M.), I'd want to select that row regardless of the particular date.

I've tried lots of different variations on the to_char and to_date functions, but I keep getting errors. Coming from Informix, Oracle seems much more complicated.

The thing closest to "correct" (I think) that I've tried is:

SELECT *
FROM my_table
WHERE SUBSTR(TO_CHAR(my_timestamp), 10) > '00:00:00.000'
AND SUBSTR(TO_CHAR(my_timestamp), 10) < '01:00:00.000'

... But nothing works. Any tips or tricks?

I found a way to do it, but I'd still prefer something a little less hacky, if it exists.

SUBSTR(SUBSTR(TO_CHAR(my_timestamp), 11), 0, 12) > '01.00.00.000'

Upvotes: 2

Views: 4403

Answers (3)

Alex Poole
Alex Poole

Reputation: 191275

As a variation on @kubanczyk's answer, since these are timestamps you get an interval when you subtract a value from its truncated form:

select systimestamp - trunc(systimestamp) from dual;

SYSTIMESTAMP-TRUNC(SYSTIMESTAMP)
---------------------------------------------------------------------------
+000000000 09:46:46.589795

Which isn't very helpful. But if you're always looking for exact hours, as in your example, you can extract the hour number from that:

select extract (hour from systimestamp - trunc(systimestamp)) from dual;

EXTRACT(HOURFROMSYSTIMESTAMP-TRUNC(SYSTIMESTAMP))
-------------------------------------------------
                                                9

So in your example you could use:

SELECT *
FROM my_table
WHERE EXTRACT(HOUR FROM my_timestamp - TRUNC(my_timestamp)) = 0

SQL Fiddle demo.

But, this will only be straightforward if the timeslots are exactly aligned with hours; otherwise you'd need to extract other elements too and the logic could get confusing, and @Ankit's approach will be simpler overall.

Upvotes: 0

Ankit Bansal
Ankit Bansal

Reputation: 5072

Your solution looks correct to me except I haven't tried substr function. This is what I used in one of my previous project:

select * from orders 
   where to_char(my_timestamp,'hh24:mi:ss.FF3') 
   between '00:00:00.000' and '01:00:00.123';

Upvotes: 4

kubanczyk
kubanczyk

Reputation: 5941

Use TRUNC(my_timestamp, 'J') to remove the hours and get only the '2013-08-15 00:00:00.00'. So:

WHERE my_timestamp - TRUNC(my_timestamp, 'J') > 0 
AND my_timestamp - TRUNC(my_timestamp, 'J') < 1/24 ;

Upvotes: 1

Related Questions