SQL query not to SELECT something for a period of time

I've an sql query which SELECTs certain fields from a table if the (sysdate - upd_dt) * 1440 > 10
basically, this query is a perl script which gets executed every 5 mins
Now, I need to modify the query so that it doesn't selects anything if the system time is between 0000 and 0115

I dont want to decrease the frequency of the perl script as it does some other stuffs.

Is there any way to perform this?

rgds
prasen
prasen120998Asked:
Who is Participating?
 
BillAn1Connect With a Mentor Commented:
You could try something like this :
SELECT * FROM MYTABLE
WHERE (sysdate - upd_dt) * 1440 > 10
AND TO_CHAR('HHMI', sysdate) < '0115'
0
 
prasen120998Author Commented:
I'm using Oracle database
0
 
annamalai77Connect With a Mentor Commented:
hi

SELECT *
FROM MYTABLE
WHERE (sysdate - upd_dt) * 1440 > 10
AND to_number(TO_CHAR(sysdate, 'HHMI')) between 0000 and  0115

regards
annamalai
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
BillAb1 and annamalai77 are on the good way, so give points to them.
But they should read more carefully, because the question is in the negative way, thus add a NOT to the condition:

SELECT *
FROM MYTABLE
WHERE (sysdate - upd_dt) * 1440 > 10
AND NOT TO_CHAR(sysdate, 'HHMI') between '0000' and '0115'

respectively:
ELECT * FROM MYTABLE
WHERE (sysdate - upd_dt) * 1440 > 10
AND TO_CHAR('HHMI', sysdate) > '0115'

Cheers
0
 
annamalai77Commented:
hi the correct query

a minor correction as said by angel

SELECT *
FROM MYTABLE
WHERE (sysdate - upd_dt) * 1440 > 10
AND to_number(TO_CHAR(sysdate, 'HHMI')) not between 0000 and  0115

regards
annamalai

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Hi annamalai77,
may you tell us why you use to_number?
0
 
annamalai77Commented:
hi angel

just to convert the character data to numeric data thats all. even without to_number the query will work but to avoid any problems which might arise i have used to_number.

regards
annamalai
0
 
prasen120998Author Commented:
Thanks for the suggestions ---
do I need to change the below
to_number(TO_CHAR(sysdate, 'HHMI'))

to
to_number(TO_CHAR(sysdate, 'HH24MI'))
in order to get the 24 hrs clock?
0
 
annamalai77Commented:
hi

yes u can use if u want to convert it into a 24hr clock.

regards
annamalai
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
As I pointed out previously:
>>BillAb1 and annamalai77 are on the good way, so give points to them.
CHeers
0
All Courses

From novice to tech pro — start learning today.