how to fetch the half hour records if the time comes dynamically

below query will fetch the records for the last 1/2 hour records from 11p.m to 11:30 p.m;

SELECT *
FROM   EMPLOYEE WHERE  To_DATE(TO_CHAR(EMPLOYEE_DATE, 'mmddyyyy') || START_TIME, 'mmddyyyyhh:mi:ss pm')
BETWEEN  TRUNC(SYSDATE) + ( 23 / 24 ) AND TRUNC(SYSDATE) + ( 23.5 / 24 );

how to fetch the half hour records  if the time comes dynamically.

Lets say if user passed time as 1:10 p.m then it should fetch from 1 t0 1:30 p.m;
then the query should come like this right.

SELECT *
FROM   EMPLOYEE WHERE  To_DATE(TO_CHAR(EMPLOYEE_DATE, 'mmddyyyy') || START_TIME, 'mmddyyyyhh:mi:ss pm')
BETWEEN  TRUNC(SYSDATE) + ( 13 / 24 ) AND TRUNC(SYSDATE) + ( 13.5 / 24 );

another example is if the time comes as 8:35 a.m then the records comes from 8:30 to 9:00 a.m.
LVL 20
chaitu chaituAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Wasim Akram ShaikCommented:
Using sysdate as a default date to check then you can use below query to get the half an hour records.. substitute in your above query..


select TO_CHAR(sysdate, 'DD-MM-YY hh24:mi:ss'),TO_CHAR(sysdate-(0.5/24), 'DD-MM-YY hh24:mi:ss') from dual

Open in new window


if you have any variable in which time comes dynamically then replace the variable with sysdate above and you can use the same..

this query is converting the time in 24 hr format and retrieving last 30 minute time..
0
chaitu chaituAuthor Commented:
need  the fetch current day records only but time is going to change based on the input passed by user.
0
johnsoneSenior Oracle DBACommented:
This shows how you can get the 2 dates that you are looking for:
WITH d 
     AS (SELECT SYSDATE dt 
         FROM   dual) 
SELECT CASE 
         WHEN dt - Trunc(dt, 'hh') < ( 30 / 1440 ) THEN Trunc(dt, 'hh') 
         ELSE Trunc(dt, 'hh') + ( 30 / 1440 ) 
       END, 
       CASE 
         WHEN dt - Trunc(dt, 'hh') < ( 30 / 1440 ) THEN 
         Trunc(dt, 'hh') + ( 30 / 1440 ) 
         ELSE Trunc(dt, 'hh') + ( 1 / 24 ) 
       END 
FROM   d; 

Open in new window

There are many ways to incorporate that into your query, but if you want to incorporate it using SYSDATE as in your example, I would just put in the case statements like this;
SELECT * 
FROM   employee 
WHERE  To_date(To_char(employee_date, 'mmddyyyy') 
               || start_time, 'mmddyyyyhh:mi:ss pm') BETWEEN CASE 
                                                               WHEN 
              SYSDATE - Trunc(SYSDATE, 'hh') < ( 30 / 1440 ) THEN 
       Trunc(SYSDATE, 'hh') 
                                                               ELSE 
              Trunc(SYSDATE, 'hh') + ( 30 / 1440 ) 
                                                             END AND CASE 
                                                                       WHEN 
              SYSDATE - Trunc(SYSDATE, 'hh') < ( 30 / 1440 ) THEN 
                        Trunc(SYSDATE, 'hh') + ( 30 / 1440 ) 
                        ELSE Trunc(SYSDATE, 'hh') + ( 1 / 24 ) 
                      END; 

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

chaitu chaituAuthor Commented:
i think you didn't understand the requirement.if i pass 11:10 p.m as input to that query where are taking that input.

if i pass 11:10 p.m to the query then i should get today's records from 11 to 11:30 p.m records.

if i pass 8:45 a.m as input then i should get today's records from 8:30 a.m to 9.00 a.m records.
0
johnsoneSenior Oracle DBACommented:
That is exactly what I gave you.  Did  you run the first query that shows to it gets the dates?  Plug in any date you want into the SELECT SYSDATE FROM DUAL statement and it shows how it works.  Does exactly what you asked.

You don't say how the variable goes in, so just substitute in the variable for SYSDATE.

Is the variable a date, string, number?  What is the format?  Is it a bind variable?  I would hope that you should be able to plug the variable logic into the query.
0
Mark GeerlingsDatabase AdministratorCommented:
I recommend that you have at least one reference in the "where" clause of your query to the unmodified EMPLOYEE_DATE column, something like this:

SELECT *
 FROM   EMPLOYEE WHERE  EMPLOYEE_DATE between [your_date] -30/1440 and [your_date] + 30/1440
  and [the conditions from Johnsone or Wasim...]

Otherwise, the performance of this query will get slower and slower as records are added.
0
awking00Commented:
>>if I pass 11:10 p.m to the query then i should get today's records from 11 to 11:30 p.m records.
 if I pass 8:45 a.m as input then I should get today's records from 8:30 a.m to 9.00 a.m records<<
How are these being passed (i.e. what data type and what mechanism)?
0
johnsoneSenior Oracle DBACommented:
markgeer's comment is correct.  If the column is indexed, then you are going to want to add to the where clause.

Based on this and the other question, time is not being stored in the EMPLOYEE_DATE column so they should all be defaulted to midnight, so the date math of adding and subtracting 30 minutes that markgeer has suggested wouldn't work.  It would have to be something like:

EMPLOYEE_DATE = variable passed in

Since we don't know the mechanism for passing in the variable, we cannot give a more exact where clause to get the index used.
0
awking00Commented:
Some sample data with your table structure would help. We are making assumptions that may or may not be valid and need more clarification. Is employee_date equal to the current_date or is it more like a date hired?
0
chaitu chaituAuthor Commented:
  CREATE TABLE "HR"."EMPLOYEE" 
   (	  "EMPLOYEE_ID" NUMBER(6,0), 
	  "EMPLOYEE_DATE" DATE,
	  "START_TIME" VARCHAR2(20 BYTE)
   );


Insert into EMPLOYEE (EMPLOYEE_ID,EMPLOYEE_DATE,START_TIME) values (1,to_date('23-MAY-15','DD-MON-RR'),'01:11:19 PM');
Insert into EMPLOYEE (EMPLOYEE_ID,EMPLOYEE_DATE,START_TIME) values (2,to_date('23-MAY-15','DD-MON-RR'),'08:31:19 AM');
Insert into EMPLOYEE (EMPLOYEE_ID,EMPLOYEE_DATE,START_TIME) values (3,to_date('23-MAY-15','DD-MON-RR'),'12:11:19 PM');
Insert into EMPLOYEE (EMPLOYEE_ID,EMPLOYEE_DATE,START_TIME) values (4,to_date('23-MAY-15','DD-MON-RR'),'11:11:19 AM');
Insert into EMPLOYEE (EMPLOYEE_ID,EMPLOYEE_DATE,START_TIME) values (5,to_date('23-MAY-15','DD-MON-RR'),'05:35:19 PM');
Insert into EMPLOYEE (EMPLOYEE_ID,EMPLOYEE_DATE,START_TIME) values (6,to_date('23-MAY-15','DD-MON-RR'),'09:15:19 PM');

Open in new window


I will once again explain you the exact requirement.above is the sample data.

from the UI application user clicked Hyper link or entered in the text box as 01:20:11 pm then it should fetch last half hour records on that date(i.e. current date);Then our query should take it as input and fetch from 1 p.m to 1:30 p.m records.so the output should be the first record in this case.



Is it possible to achieve the solution only through single sql query or do i need to write any stored procedure or pl/sql block.
0
johnsoneSenior Oracle DBACommented:
Did you try the any of the queries that have been provided?

You mention a time, but what should the date be?

This query assumes that the date is today.
SELECT * 
FROM   employee 
WHERE  To_date(To_char(employee_date, 'mmddyyyy') 
               || start_time, 'mmddyyyyhh:mi:ss pm') BETWEEN CASE 
                                                               WHEN 
              To_date(To_char(SYSDATE, 'mmddyyyy') 
                      ||'01:20:11 pm', 'mmddyyyyhh:mi:ss pm') - Trunc( 
                     To_date(To_char(SYSDATE, 'mmddyyyy') 
                             ||'01:20:11 pm', 'mmddyyyyhh:mi:ss pm'), 'hh') < ( 
              30 / 
              1440 ) THEN Trunc(To_date(To_char(SYSDATE, 'mmddyyyy') 
                                        ||'01:20:11 pm', 'mmddyyyyhh:mi:ss pm'), 
                          'hh') 
                                                               ELSE 
              Trunc(To_date(To_char(SYSDATE, 'mmddyyyy') 
                            ||'01:20:11 pm', 'mmddyyyyhh:mi:ss pm'), 'hh') + ( 
              30 / 
              1440 ) 
                                                             END AND 
              CASE 
                WHEN 
              To_date( 
                        To_char(SYSDATE, 
                        'mmddyyyy') 
                        ||'01:20:11 pm', 
       'mmddyyyyhh:mi:ss pm') - Trunc( 
                                To_date(To_char(SYSDATE, 'mmddyyyy') 
                                        ||'01:20:11 pm', 'mmddyyyyhh:mi:ss pm'), 
                                'hh') < ( 
                                                                            30 / 
                                                                            1440 
                                        ) THEN 
                                                                       Trunc( 
                To_date( 
                                                                       To_char( 
                SYSDATE, 
                'mmddyyyy') 
                ||'01:20:11 pm', 
       'mmddyyyyhh:mi:ss pm') 
                                                                       , 'hh') 
                                                                       + ( 30 / 
                1440 ) 
                                                                       ELSE 
              Trunc(To_date(To_char(SYSDATE, 'mmddyyyy') 
                            ||'01:20:11 pm', 'mmddyyyyhh:mi:ss pm'), 'hh') + ( 
              1 / 24 ) 
                                                                     END; 

Open in new window

All I did is substitute the current date and the time provided for SYSDATE in my original query as stated.  Query comes back with one record, EMPOYEE_ID=1, which according to the limited data looks correct.

Not sure why you couldn't do the substitution yourself.

This assumes that there is no index on EMPLOYEE_DATE.  If there is an index, I would add EMPOLOYEE_DATE = TRUNC(SYSDATE) to the where clause to cut down on the number of records read.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
chaitu chaituAuthor Commented:
Thanks for reply.

"You mention a time, but what should the date be?"
from the first  post onwards keep on telling fetch the current date records.

"Not sure why you couldn't do the substitution yourself."

initially i didn't get what to substitute in the final query.when i see your first and posts .you have replaced with

SYSDATE with

"To_date(To_char(SYSDATE, 'mmddyyyy') ||'01:20:11 pm', 'mmddyyyyhh:mi:ss pm')"

"This assumes that there is no index on EMPLOYEE_DATE."

index is there on EMPLOYEE_DATE table.
0
chaitu chaituAuthor Commented:
I didnt understand the case statements logic.can you please explain?

CASE
    WHEN SYSDATE - Trunc(SYSDATE, 'hh') < (30 / 1440) THEN Trunc(SYSDATE, 'hh')
    ELSE Trunc(SYSDATE, 'hh') + (30 / 1440)
END
AND CASE
        WHEN SYSDATE - Trunc(SYSDATE, 'hh') < (30 / 1440) THEN Trunc(SYSDATE, 'hh') + (30 / 1440)
        ELSE Trunc(SYSDATE, 'hh') + (1 / 24)
    END;

Open in new window

two case when statements initial conditions looks same but you  are taking only hrs..little bit confusing..
0
johnsoneSenior Oracle DBACommented:
The first part of the case must be the same.  You are checking the difference between the time specified (SYSDATE) and the start of the hour (TRUNC(SYSDATE, 'hh')).  If that is less than 30 minutes, then for a start time you want the start of the hour (TRUNC(SYSDATE, 'hh')).  If it is not less than 30 minutes, then the start time is the start of the hour plus 30 minutes (TRUNC(SYSDATE,'hh')+(30/1440)).

The second case does the same thing for the end time.  If difference between time being checked and start of hour is less than 30 minutes, then you want the half hour.  If not, then you want the start of the next hour.
0
awking00Commented:
I'm still not sure of the mechanism used to pass in the time variable, but I think the following does what you want using a substitution variable (with the ampersand):
select e.* from employee e,
(select begin_time, end_time from
 (select
  trunc(sysdate) + (level - 1)/48 begin_time,
  trunc(sysdate) + level/48 end_time
  from dual
  connect by level <= 48) x
 where to_date(to_char(trunc(sysdate)||' &timepassedin'),'DD-MON-YY HH:MI:SS AM')
 between x.begin_time and x.end_time) y
where to_date(to_char(e.employee_date||' '||start_time),'DD-MON-YY HH:MI:SS AM')
between y.begin_time and y.end_time;

The substitution variable will prompt you to "Enter value for timepassedin:"
Entering 01:20:11 pm would produce the record for employee_id 1, 8:25:14 would produce the record for employee_id 2, etc.

Hope this helps.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.