Find current time between two fields in Oracle

Question

I have a situation where i need to raise an event everyday between 23:00 hrs to next day 01:00 hrs. My table has a data of date which will look like:

Start date          | EndDate
31-05-2016 23:00:00 | 01-06-2016 01:00:00

Already the database is designed like this and applications are live. Now, i tried getting the data using BETWEEN statement, but, nothing works how i need since the ENDDate is next day morning.

Could someone tell how to extract this data correctly.

My ORACLE Query

 SELECT COUNT(*) INTO P_OUTPUT FROM MAINTENANCE_LOG WHERE      
 NVL(RECCURING_TYPE,'O')='D' AND ACTIVE_STATUS='Y' AND
 TO_CHAR(SYSDATE,'HH24MISS') BETWEEN TO_CHAR(START_DATE,'HH24MISS') AND TO_CHAR(END_DATE,'HH24MISS');

I tried converting this to date again, but it also does not work with the EndDate.

Update: In my requirement I need only to check whether the current time is between the startdate or enddate time. I need to run this query everyday and it should do an activity between 23:00 and 1:00 of each day.

Note: If I run the query of Sysdate between startdate and enddate on 31-05-2015 i will get true/false. But, if I run this next day at night I will only get false since the EndDate is already over. So, I cant use the normal between date query.

Why are you to_charing a date field and using between? Don't to_char them. Simply compare sysdate between start_date and end_date... You're asking the engine to compare TEXT values. which should not be done ondates... — xQbert, May 31 '16 at 19:17
Why i am doing a To_CHAR is even though my data is having a Date i only need to check the Current time is between 23:00 and 01:00. It should give me a count(*) based on it. — smilu, May 31 '16 at 19:44
Please show more example rows of sample data, and expected output with details. Edit the question, not in comments. — OldProgrammer, May 31 '16 at 20:08

score 1 · Accepted Answer · answered May 31 '16 at 22:18

SELECT COUNT(*)
INTO   P_OUTPUT
FROM   MAINTENANCE_LOG
WHERE  RECCURING_TYPE = 'D'
AND    ACTIVE_STATUS  = 'Y'
AND    ( SYSDATE - TRUNC(SYSDATE) BETWEEN START_DATE - TRUNC( START_DATE )
                                      AND END_DATE   - TRUNC( START_DATE )
       OR
         SYSDATE - TRUNC(SYSDATE) BETWEEN START_DATE - TRUNC( END_DATE )
                                      AND END_DATE   - TRUNC( END_DATE )
       )

Patrick Marchand · Answer 2 · 2016-05-31T22:45:15.183

If I get the question, you want to treat the times separately from dates. That is, 2016-MAY-31 23:00:00 should be treated as just 23:00:00 and 2016-JUN-01 01:00:00 should be treated as just 01:00:00 and treat these two times as a range? So if I was looking at a time of 23:47:43 or 00:23:11, both would be considered within the range but times like 22:56:34 and 01:34:52 would be outside of the range?

To go with a DATE datatype, your range spans two dates so you would need to put dates on the time input value such that a time like 00:23:11 would be looked at as YYYY-MM-DD 00:23:11 and make this date/time fall between your two date/times (START_DATE and END_DATE.)

In your example, 2016-JUN-01 00:23:11 would work but 2016-MAY-31 00:23:11 would not. Similarly for 23:47:23, 2016-MAY-31 23:47:23 would work but 2016-JUN-01 23:47:23 would not.

Basically, the rule could be as follows: If the time you want to test has an hour < 12 (noon), append the date from the END_DATE, otherwise (hour >= 12) append the date from the START_DATE and compare the result with the date/times in START_DATE and END_DATE.

Perhaps something like this (I'm simulating your start/end dates table with a query here):

WITH test_data AS 
(SELECT '00:23:11' as time_char
   FROM dual
 UNION ALL
 SELECT '23:47:23' as time_char
   FROM dual
 UNION ALL 
 SELECT '22:56:34' as time_char
 FROM dual
 UNION ALL
 SELECT '01:34:52' as time_char
   FROM dual
 UNION ALL
 SELECT '12:34:52' as time_char
   FROM dual
 UNION ALL
 SELECT '23:00:00' as time_char
   FROM dual
 UNION ALL
 SELECT '01:00:00' as time_char
   FROM dual
 UNION ALL
 SELECT '22:59:59' as time_char
   FROM dual
 UNION ALL
 SELECT '01:0:01' as time_char
   FROM dual
)
SELECT test_data.time_char, start_end_table.*
FROM (SELECT TO_DATE('2016-MAY-31 23:00:00', 'YYYY-MON-DD HH24:MI:SS') as start_date
           , TO_DATE('2016-JUN-01 01:00:00', 'YYYY-MON-DD HH24:MI:SS') as end_date
        FROM dual
     ) start_end_table
     FULL OUTER JOIN
     test_data
     ON  
     CASE WHEN TO_NUMBER(SUBSTR(test_data.time_char, 1, 2)) < 12 
           THEN TO_DATE(TO_CHAR(start_end_table.end_date, 'YYYYMMDD')||test_data.time_char, 'YYYYMMDDHH24:MI:SS')
           ELSE TO_DATE(TO_CHAR(start_end_table.start_date, 'YYYYMMDD')||test_data.time_char, 'YYYYMMDDHH24:MI:SS')
      END 
      BETWEEN start_end_table.start_date AND start_end_table.end_date

TIME_CHAR   START_DATE              END_DATE
00:23:11    2016-MAY-31 23:00:00    2016-JUN-01 01:00:00
23:47:23    2016-MAY-31 23:00:00    2016-JUN-01 01:00:00
23:00:00    2016-MAY-31 23:00:00    2016-JUN-01 01:00:00
01:00:00    2016-MAY-31 23:00:00    2016-JUN-01 01:00:00
01:34:52          (null)                   (null)
01:0:01           (null)                   (null)
22:59:59          (null)                   (null)
22:56:34          (null)                   (null)
12:34:52          (null)                   (null)

score 0 · Answer 3 · answered May 31 '16 at 19:35

0

You don't need convert date fields to char, only use dates and BETWEEN clausule to accomplish your select:

SELECT COUNT(*) INTO P_OUTPUT 
FROM MAINTENANCE_LOG 
WHERE      
   NVL(RECCURING_TYPE,'O')='D' AND 
   ACTIVE_STATUS='Y' AND
   SYSDATE BETWEEN START_DATE AND END_DATE;

Anyway, in your question... you could add YYYYMMDD... but please, don't do it if you are using date fields.

TO_CHAR(SYSDATE,   'YYYYMMDDHH24MISS') BETWEEN 
TO_CHAR(START_DATE,'YYYYMMDDHH24MISS') AND 
TO_CHAR(END_DATE,  'YYYYMMDDHH24MISS')

answered May 31 '16 at 19:35

David Isla

619
7
19

I have done those. But, my requirement is to only extract the time and compare the current time against the Start and End time. Let us say today when i am running the query it will return true since it is 31st of May. But tomorrow when i run the End date is already over. – smilu May 31 '16 at 19:45

score 0 · Answer 4 · edited Oct 24 '18 at 08:02

0

This is work for me :

SELECT COUNT(*)
INTO   P_OUTPUT
FROM   MAINTENANCE_LOG
WHERE  RECCURING_TYPE = 'D'
AND    ACTIVE_STATUS  = 'Y'
AND  SYSDATE BETWEEN YEAR_FROM and YEAR_TO

edited Oct 24 '18 at 08:02

Suraj Rao

29,388
11
94
103

answered Oct 24 '18 at 07:59

Vikas Jain

31
1

Find current time between two fields in Oracle

4 Answers4