Oracle Set operator query

Question

Have a SQL query on Oracle 11g which returns the count of whether a record having certain ID and status exists within +/- 15 minutes range in a table.

Now I wish to ignore the current date by adding a condition like AND TIMESTAMP < trunc(sysdate).
However, for cases where the record exists in todays date I wish to ignore the date comparison check in the query '2010-07-20 19:15:11' >= TO_CHAR(TIMESTAMP - (1/1440*15), 'YYYY-MM-DD HH24:MI:SS') AND '2010-07-20 19:15:11' <= (TO_CHAR(TIMESTAMP + (1/1440*15), 'YYYY-MM-DD HH24:MI:SS'))

SELECT count(1) AS COUNT 
FROM MASTER_ONE 
WHERE ID='123' AND STATUS= 'ACTIVE' 
AND '2010-07-20 19:15:11' >= TO_CHAR(TIMESTAMP - (1/1440*15), 'YYYY-MM-DD HH24:MI:SS') 
AND '2010-07-20 19:15:11' <= (TO_CHAR(TIMESTAMP + (1/1440*15), 'YYYY-MM-DD HH24:MI:SS')) 
UNION ALL 
SELECT count(1) AS COUNT 
FROM MASTER_TWO 
WHERE ID='321' AND STATUS= 'ACTIVE' 
AND '2010-07-20 19:15:11' >= TO_CHAR(TIMESTAMP - (1/1440*15), 'YYYY-MM-DD HH24:MI:SS') 
AND '2010-07-20 19:15:11' <= (TO_CHAR(TIMESTAMP + (1/1440*15), 'YYYY-MM-DD HH24:MI:SS'))

How do I do this?

I don't understand your question completly. your timestamp column is a DATE column? in wich cases excatly you need to ignore the comparison? where is the sysdate condition? — Aitor, Jul 21 '11 at 07:13
Yeah - Timestamp column is a Date column. In case it is today i want to ignore the comparison that input date should fall within +-15 minutes of column Timestamp date. — John C, Jul 21 '11 at 16:21

score 1 · Accepted Answer · edited May 23 '17 at 12:11

The first problem with your query is that you're doing a string comparison on the date. Use to_date instead of to_char and let Oracle help you out.

SELECT   
    to_date('2010-07-20 19:15:11', 'YYYY-MM-DD HH24:MI:SS') AS orig_date
  , to_date('2010-07-20 19:15:11', 'YYYY-MM-DD HH24:MI:SS') - 1 / 24 / 4 AS fifteen_min_prior
  , to_date('2010-07-20 19:15:11', 'YYYY-MM-DD HH24:MI:SS') + 1 / 24 / 4 AS fifteen_min_after
FROM dual;

Output:

ORIG_DATE                 FIFTEEN_MIN_PRIOR         FIFTEEN_MIN_AFTER         
------------------------- ------------------------- ------------------------- 
20-JUL-10 07:15:11 PM     20-JUL-10 07:00:11 PM     20-JUL-10 07:30:11 PM

Then use can use those dates in a BETWEEN condition in the predicate. See Oracle date "Between" Query.

I'm not quite clear what you mean by "However, for cases where the record exists in todays date I wish to ignore the date comparison check in the query." You'd just written that you want to exclude values from the current day. Either you're excluding today's records or you're not.

score 1 · Answer 2 · edited Jul 21 '11 at 19:35

Ok, you can try something like this, if I understood you correctly:

SELECT count(1) AS COUNT 
FROM MASTER_ONE 
WHERE ID='123' AND STATUS= 'ACTIVE' 
AND (timestamp > trunc(sysdate) 
  OR (timestamp < trunc(sysdate) 
  AND timestamp BETWEEN to_date(:yourInputDate,'DD/MM/YYYY HH24:MI:SS') - (1/1440*15) 
                    AND to_date(:yourInputDate,'DD/MM/YYYY HH24:MI:SS') + (1/1440*15)))
UNION ALL
SELECT count(1) AS COUNT 
FROM MASTER_TWO 
WHERE ID='321' AND STATUS= 'ACTIVE' 
AND (timestamp > trunc(sysdate) 
  OR (timestamp < trunc(sysdate) 
  AND timestamp BETWEEN to_date(:yourInputDate,'DD/MM/YYYY HH24:MI:SS') - (1/1440*15) 
                    AND to_date(:yourInputDate,'DD/MM/YYYY HH24:MI:SS') + (1/1440*15)))

In this Select, you only apply the 15 minutes condition if your timestamp column has a date prior to sysdate.

I cleaned up the code formatting for you so the full query is syntax highlighted. But while this query properly shows the combination of `AND` and `OR`s needed to do what the OP requested (and properly uses `to_date` and `BETWEEN` instead of `to_char`), I think the OP is actually confusing the issue with the mention of "set operator" in the title and usage of `UNION ALL`. I'm not sure but I think his union may have been an attempt at fulfilling the AND/OR needs... though I do see the table and ID change in the query, so who knows. Either way, the question, title, and query are ambiguous. — BQ., Jul 21 '11 at 19:45

Oracle Set operator query

2 Answers2