7 Replies Latest reply on Aug 8, 2010 11:50 AM by 776599

    Query Based on Date & Time Range

    776599
      Hello Users,

      There is a requirement to display weekly report from saturday 8:00 AM to previous saturday 08:00 AM.

      I have a table "Downtime"
      IPADDRESS       First Occurrence                   Last Occurrence           Downtime
      172.29.10.12    31-JUL-10 08:12:50             01-AUG-10 09:00:00      1
      172.29.11.54    01-AUG-10 11:12:00            01-AUG-10 01:10:00      3
      172.29.58.7      07-AUG-10 04:10:00            07-AUG-10 05:00:00      2
      I will not have problem when I run the report on saturday as I can give condition as sysdate - 7.

      First problem is how will I default to 8 AM.

      Second if the report is run on sunday or in between before next saturdya I have to query the records only from satuday 8 AM to previous saturday 8 AM.

      Can anyone please help me on this?

      Thanks.
      Ravi.
        • 1. Re: Query Based on Date & Time Range
          Ravi,

          The unit of a date column is in days. To add 8 hours, simply add 8/24 (for legibility)
          To get on the previous saturday

          next_day(sysdate, 'SAT') - 7

          ----------------
          Sybrand Bakker
          Senior Oracle DBA
          • 2. Re: Query Based on Date & Time Range
            Solomon Yakobson
            A hint:
            TRUNC(dt,'IW')
            will give you beginning of ISO week, which is always Monday. Therefore:
            TRUNC(dt,'IW') - 5/3
            will always give you 8 a.m. Saturday prior or equal to dt.

            SY.
            • 3. Re: Query Based on Date & Time Range
              Hoek
              That's a great hint, Solomon, thanks.

              I always end up getting frustrated because trunc on a date returns a NULL for 'midnight'....and the ability to perform date arithmetic is gone all of a sudden. Why isn't Oracle just resetting to '00:00:00' when truncating a date, I wonder?

              This should illustrate my point:
              -- generating hours, starting 14 days ago:
              with t as (
              select trunc(sysdate-14)+ level/24-1 dt
              from   dual
              connect by level <= 24*14
              )
              --
              -- querying generated data:
              --
              select dt
              --,      trunc(dt,'iw') - 5/3  -- great workaround, but why can't we just work with 00:00:00 for the time component?
              from   t;
              --
              --
              --
              -- generating hours, starting 14 days ago:
              with t as (
              select trunc(sysdate-14)+ level/24-1 dt
              from   dual
              connect by level <= 24*14
              )
              --
              -- querying data between saturday and previous saturday
              -- from the same resultset and 'it doesn't work'
              --
              select dt
              from   t
              where  dt between trunc(dt-7)+8/24 and trunc(sysdate)+8/24;
              • 4. Re: Query Based on Date & Time Range
                Frank Kulash
                Hi,
                hoek wrote:
                That's a great hint, Solomon, thanks.

                I always end up getting frustrated because trunc on a date returns a NULL for 'midnight'....and the ability to perform date arithmetic is gone all of a sudden. Why isn't Oracle just resetting to '00:00:00' when truncating a date, I wonder?
                I must not understand what you're asking.
                It sounds as if you're saying that TRUNC (dt) sometimes returns something that is NULL (or somehow partially NULL), but I'm sure you don't really think that.

                For other readers who may be wondering:
                TRUNC (dt), where dt is a DATE (and not NULL) , always returns a DATE, and DATEs always have a time. Either the whole DATE is NULL or none of it is; you can't have a year-month-day in a DATE column and not have a time, or have a NULL time.
                TRUNC(dt, x) may return a DATE where the time is not midnight, but only if x is a format string like 'MI' that specifies an interval less than a day. When x indicates a longer interval (e,g, 'IW'), or when x is omitted, the time is always midnight. In any event, you can do date arithmetic on the results.
                I'm sure you understand this, but your question makes it sound like you don't.
                This should illustrate my point:
                -- generating hours, starting 14 days ago:
                with t as (
                select trunc(sysdate-14)+ level/24-1 dt
                from   dual
                connect by level <= 24*14
                )
                --
                -- querying generated data:
                --
                select dt
                --,      trunc(dt,'iw') - 5/3  -- great workaround, but why can't we just work with 00:00:00 for the time component?
                from   t;
                --
                --
                --
                -- generating hours, starting 14 days ago:
                with t as (
                select trunc(sysdate-14)+ level/24-1 dt
                from   dual
                connect by level <= 24*14
                )
                --
                -- querying data between saturday and previous saturday
                -- from the same resultset and 'it doesn't work'
                --
                select dt
                from   t
                where  dt between trunc(dt-7)+8/24 and trunc(sysdate)+8/24;
                Is this a parody of a poorly written question?
                Are you trying to make the point that no one should ever say "it doesn't work" without explaining exactly what doesn't work, and what the expected output is?
                I get the same 336 rows when I run either of the queries above.
                As you've often said, posters should always show exactly what results they want.
                • 5. Re: Query Based on Date & Time Range
                  776599
                  Hi All,

                  Thanks for your help.

                  Sorry, for not being clear. Client has changed their requirment. They want to go with parameterised query to input date range and default it to 8 AM.

                  I tried to modify the query to accept the Start Date and End date parameter as input.
                  select * from downtime where Begindate >= &a and Enddate <= &b
                  But how will I default the user input date range Begindate and Enddate to 8 AM.

                  Hard coding will not work here as it is runtime user input value.

                  Any idea please?

                  Thanks.
                  Ravi
                  • 6. Re: Query Based on Date & Time Range
                    Frank Kulash
                    Hi,

                    So Saturdays no longer have anything to do with this problem.
                    Here's one way to get parameters from the user and use them in the querry:
                    ACCEPT  p_begindate     PROMPT "Down time must begin on or after this date (e.g. 01-Jan-2010): "
                    ACCEPT      p_enddate     PROMPT "Down time must end on or before this date (e.g. 31-Jan-2010): "
                    
                    SET     VERIFY     OFF
                    
                    select      * 
                    from      downtime 
                    where      Begindate      >= TO_DATE (&p_begindate, 'DD-MON-YYYY') + (8 / 24)
                    and      Enddate      <= TO_DATE (&p_enddate,   'DD-MON-YYYY') + (8 / 24)
                    ;
                    
                    
                    SET     VERIFY     ON
                    See if you can find more meaningful variable names than &a and &b.

                    I used DD-Mon-YYY format. Of course, you can use any format that's convenient for your users.

                    When you call TO_DATE without specifying hours, minutes or seconds, they all default to 0, so
                    TO_DATE (&p_begindate, 'DD-MON-YYYY')
                    is midnight on (that is, at the beginning of) the given date, and adding (8 / 24) to that reslults in 8:00 a.m. on the given date.

                    This query includes only rows where the downtime is entirely between the parameters.
                    For example, say Startdate is noon on July 30, 2010, and Enddate is noon on August 2, 2010 on a certain row.
                    If p_enddate is '01-Aug-2010' and p_enddate is '06-Aug-2010', then that row will not be included in the query, even though the enddate is 28 hours after p_startdate.
                    Whenever you have a question, always post some sample data (CREATE TABLE and INSERT statements), a few sets of sample paramaeters, and the results you want from that data for each set of parameters.
                    • 7. Re: Query Based on Date & Time Range
                      776599
                      Hi Frank,

                      Thanks a lot. As you said I will post with valid values hence forth.

                      -Ravi