Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2008
    Posts
    464

    Unanswered: understanding part of a proc....Oracle 9i

    Hi guys

    There's a proc that, when it runs, picks out and updates a table A with values dated from 30 min past the hour to 30 min past the hour, every hour when it runs.

    CREATE OR REPLACE PROCEDURE UPDTAB (STARTDATE IN DATE DEFAULT TRUNC(SYSDATE,'HH')-(1/24)*2, ENDDATE IN DATE DEFAULT TRUNC(SYSDATE,'HH')-(1/1440)*30) AS........

    Could someone help me understand if and if so, how the above code in bold is responsible for this?

    Many thanks and Regards

  2. #2
    Join Date
    May 2009
    Posts
    13
    DATE types in Oracle can be manipulated as fractions of days, as your proc demonstrates.

    For startdate, you're taking the current date/time and truncating it to the current hour, then subtracting 2 hours from it via the (1/24)*2 piece of your code.

    For enddate, you do basically the same thing, but use the fact that there are 1440 minutes in a day to back up the truncated hour by 30 minutes.


    The following runs show your startdate is between 2 and 3 hours before current time and enddate is between 30 and 90 minutes before current time:


    SQL> set serveroutput on
    SQL> CREATE OR REPLACE PROCEDURE UPDTAB (STARTDATE IN DATE DEFAULT TRUNC(SYSDATE,'HH')-(1/24)*2, ENDDATE IN DATE DEFAULT
    TRUNC(SYSDATE,'HH')-(1/1440)*30) AS
    2 begin
    3 dbms_output.put_line('startdate is ' || to_char(startdate, 'yyyymmdd hh24:mi:ss'));
    4 dbms_output.put_line('enddate is ' || to_char(enddate, 'yyyymmdd hh24:mi:ss'));
    5 dbms_output.put_line('current time is ' || to_char(sysdate, 'hh24:mi:ss') );
    6 end;
    7 .
    SQL> /

    Procedure created.

    SQL> exec updtab;
    startdate is 20091212 12:00:00
    enddate is 20091212 13:30:00
    current time is 14:55:58

    PL/SQL procedure successfully completed.

    SQL>
    SQL> exec updtab;
    startdate is 20091212 12:00:00
    enddate is 20091212 13:30:00
    current time is 14:56:09

    PL/SQL procedure successfully completed.

    SQL>
    SQL> exec updtab;
    startdate is 20091212 13:00:00
    enddate is 20091212 14:30:00
    current time is 15:01:09

    PL/SQL procedure successfully completed.

    SQL>

  3. #3
    Join Date
    Aug 2008
    Posts
    464
    Explanation truly appreciated.

    The proc runs at 10 min past the hour, every hour.

    And fetches rows from a view for 30 min past the hour till 30 min past the following hour. So to fetch only the next half hour, I can remove '*30' from the end date section and then to get things back on track meaning to start on the hour and the end date being 60 min later, I can change the equation to:

    (STARTDATE IN DATE DEFAULT TRUNC(SYSDATE,'HH')-(150/1440), ENDDATE IN DATE DEFAULT
    TRUNC(SYSDATE,'HH')-(1/1440)*60)

    Kindly Approve?

  4. #4
    Join Date
    May 2009
    Posts
    13
    You're still looking at a 1.5 hour window, which I don't think is what you want. Try experimenting with variations of the following:

    SELECT TO_CHAR(TRUNC(SYSDATE,'HH')-(90/1440), 'YYYYMMDD:HH24:MIS'),
    TO_CHAR(TRUNC(SYSDATE,'HH')-(30/1440), 'YYYYMMDD:HH24:MIS') FROM DUAL

    Also, to stay consistent in your formula you may want to change the endtime math to 60/1440 instead of (1/1440)*60.

    I don't know exactly what you're trying to do, but I think you're getting the concept.

    Good luck!

  5. #5
    Join Date
    Aug 2008
    Posts
    464
    Actually, currently the 4 quarters of datetime being picked by the above code, every hour, when it runs at 10 min past the hour, are e.g.,

    8:45, 9:00, 9:15, 9:30

    I really just want these times to shift 15 min forward, so that 9:00, 9:15, 9:30 and 9:45 get picked.

    Please advise how I can amend the code? Thanks

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I really just want these times to shift 15 min forward, so that 9:00, 9:15, 9:30 and 9:45 get picked.
    >Please advise how I can amend the code?

    Add 15 minutes to existing results


    However I don't understand how/why you report 15 minute periods.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Aug 2008
    Posts
    464

    Thanks

    Many Thanks guys for helping me out.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •