14 Replies Latest reply: May 7, 2008 6:30 AM by Rob van Wijk RSS

    Query On timestamp

    542647
      Hi,

      I have one table like

      <pre>

      Create table date_test
      (id number(10),
      Stage varchar2(10),
      Start_date Date,
      End_date Date)
      /
      </Pre>

      and i have date like

      <Pre>

      ID     STAGE     START_DATE     END_DATE
      1     Stage1     01/01/2008 06:00:00     01/01/2008 08:00:00
      2     Stage1     01/01/2008 05:30:00     01/01/2008 06:25:00
      3     Stage2     01/01/2008 11:10:00     02/01/2008 15:30:00
      4     Stage2     01/01/2008 05:30:00     03/02/2008 08:10:00

      </pre>

      Now i want to take out average time taken to pergorm each stage so i have to group this on stage.and i have to consider that on 1 day there are only 8 working hours. so if the task is started on 1st Jan at 3PM and ended at 3rd Jan at 3PM...It should be 16 hours( 8 * 2). It should not 48 hours. Also i have to exclude saturday and sunday from calculation.
      I have never worked on timestamp as much and when i am trying to simply take out hours between 2 dates it gives me in points even if the hours difference is 2.
      Kindly help me on same
        • 1. Re: Query On timestamp
          290833
          Some of your start_date / end_date pairs fall on different days.

          In order to work out which of these hours are legitimate work hours, you need to provide the time that work stops and starts each day.
          • 2. Re: Query On timestamp
            542647
            Thanx anthony for replying. Please find below the corrective date

            Ther working hours are between 09:30 to 06:30 including 1 hour lunch break which we will exclude from business hours.So the business hours will be 8.
            <Pre>

            ID STAGE START_DATE END_DATE
            1 Stage1 01/01/2008 10:00:00 02/01/2008 13:10:00
            2 Stage1 01/01/2008 11:30:00 03/01/2008 16:25:00
            3 Stage2 01/01/2008 11:10:00 02/01/2008 15:30:00
            4 Stage2 01/01/2008 09:45:00 08/01/2008 16:10:00

            </pre>

            Also i have tried below mentioned query.

            Select Stage,
            avg((extract (day from end_date - Start_date) * 8) + extract (hour from end_date - start_date))
            from date_test1 group by stage
            /

            Now i have to include minutes also and need to exclude saturday and sunday.

            Thanx once again
            • 3. Re: Query On timestamp
              Rob van Wijk
              SQL> select * from date_test
                2  /

                 ID STAGE      START_DATE          END_DATE
              ----- ---------- ------------------- -------------------
                  1 Stage1     01-01-2008 06:00:00 01-01-2008 08:00:00
                  2 Stage1     01-01-2008 05:30:00 01-01-2008 06:25:00
                  3 Stage2     01-01-2008 11:10:00 02-01-2008 15:30:00
                  4 Stage2     01-01-2008 05:30:00 04-02-2008 08:10:00

              4 rows selected.

              SQL> select stage
                2       , avg
                3         ( 24 * (end_date-start_date)
                4           - 16 * trunc(end_date-start_date)
                5           - 16 * trunc((end_date-start_date)/7)
                6           - 8 * case when to_number(to_char(start_date,'d')) > to_number(to_char(end_date,'d')) then 1 else 0 end
                7         ) avg_hours
                8    from date_test
                9   group by stage
              10   order by stage
              11  /

              STAGE      AVG_HOURS
              ---------- ---------
              Stage1         1.458
              Stage2         107.5

              2 rows selected.
              Regards,
              Rob.
              • 4. Re: Query On timestamp
                542647
                Thanx Rob,

                But how to exclude saturday and sunday..It should not count hours when the day is saturday and sunday
                • 5. Re: Query On timestamp
                  Rob van Wijk
                  Already accounted for. Please have a closer look at the expression.

                  Regards,
                  Rob.
                  • 6. Re: Query On timestamp
                    542647
                    Thanx Rob,

                    I am getting the desired output but just for my understanding i am not able to clear how you have excluded Saturday and sunday . I can Judge that through

                    - 16 * trunc((end_date-start_date)/7)

                    You have excluded it.But what is the logic behind it. Really thankful for your help.
                    • 7. Re: Query On timestamp
                      Rob van Wijk
                      Note that there was an error in the fourth part of the expression, which is corrected below.
                      I've added some comments to the query.
                      SQL> select stage
                        2       , avg
                        3         ( 24 * (end_date-start_date)            -- count all hours
                        4           - 16 * trunc(end_date-start_date)     -- for every 24 hours exclude 16 hours, so now you have 8 hours per day
                        5           - 16 * trunc((end_date-start_date)/7) -- for every 7 days exclude 16 hours (8 hours for saturday and 8 for sunday
                        6           - case
                        7             when to_number(to_char(start_date,'d')) > to_number(to_char(end_date,'d'))
                        8             then 16
                        9             else 0
                      10             end                                 -- a period below 7 days can still include a weekend if the daynumber of the
                      11         ) avg_hours                             -- startdate is greater than the daynumber of the end_date.
                      12    from date_test
                      13   group by stage
                      14   order by stage
                      15  /

                      STAGE      AVG_HOURS
                      ---------- ---------
                      Stage1         1.458
                      Stage2         103.5

                      2 rows selected.
                      Regards,
                      Rob.
                      • 8. Re: Query On timestamp
                        542647
                        I have one more problem in this query. Lets say Start_date is 1st and end_date is 4th of same month.
                        And on 2nd and 3rd, Itz a weekend(Saturday and sunday).
                        It still counts 8 hours for both days. So i feel we have to check for day in this query that there should not be any hours for saturday and sunday.
                        • 9. Re: Query On timestamp
                          Rob van Wijk
                          That's exactly what the fourth part of the expression is doing.

                          > So i feel

                          Don't feel, just test ;-)

                          Regards,
                          Rob.
                          • 10. Re: Query On timestamp
                            542647
                            Thanx Rob,

                            It is working exactly like i wanted to. But can you make me understand how we have excluded Saturday and sunday.What is the logic behind it as it looks awesome to me.

                            Thanx alot
                            • 11. Re: Query On timestamp
                              Rob van Wijk
                              > But can you make me understand how we have excluded Saturday and
                              sunday.What is the logic behind it as it looks awesome to me.

                              I did comment already, so what part is it exactly that you are not comfortable with yet?

                              Regards,
                              Rob.
                              • 12. Re: Query On timestamp
                                542647
                                That how it is categorizing as when is saturday and sunday??
                                • 13. Re: Query On timestamp
                                  633515
                                  i tried the same query,but i have created the table with datatype timestamp
                                  and these r my table values

                                  1Stage1 01-JAN-08 10.00.00.000000000 AM 02-JAN-0801.10.00.000000000 PM
                                  2Stage1 01-JAN-08 11.30.00.000000000 AM 03-JAN-08 04.25.00.000000000 PM
                                  4Stage2 01-JAN-08 09.45.00.000000000 AM 08-JAN-08 04.10.00.000000000 PM
                                  3Stage2 01-JAN-08 11.10.00.000000000 AM 02-JAN-08 03.30.00.000000000 PM

                                  so now how will ur query change
                                  • 14. Re: Query On timestamp
                                    Rob van Wijk
                                    It's hard for me to explain it any better in words, so here it is broken down in little parts. The datatype is changed to timestamp here. And I added some cases to show periods from monday to friday and friday to monday.
                                    SQL> select id
                                      2       , stage
                                      3       , start_date
                                      4       , to_char(start_date,'day','nls_date_language=english')
                                      5       , end_date
                                      6       , to_char(end_date,'day','nls_date_language=english')
                                      7    from date_test
                                      8  /

                                       ID STAGE      START_DATE                     TO_CHAR(S END_DATE                       TO_CHAR(E
                                    ----- ---------- ------------------------------ --------- ------------------------------ ---------
                                        1 Stage1     01-JAN-08 06.00.00.000000 AM   tuesday   01-JAN-08 08.00.00.000000 AM   tuesday
                                        2 Stage1     01-JAN-08 05.30.00.000000 AM   tuesday   01-JAN-08 06.25.00.000000 AM   tuesday
                                        3 Stage2     01-JAN-08 11.10.00.000000 AM   tuesday   02-JAN-08 03.30.00.000000 PM   wednesday
                                        4 Stage2     01-JAN-08 05.30.00.000000 AM   tuesday   04-FEB-08 08.10.00.000000 AM   monday
                                        5 Stage3     31-DEC-07 12.00.00.000000 AM   monday    04-JAN-08 12.00.00.000000 AM   friday
                                        6 Stage4     04-JAN-08 12.00.00.000000 AM   friday    07-JAN-08 12.00.00.000000 AM   monday
                                        7 Stage5     31-DEC-07 12.00.00.000000 AM   monday    11-JAN-08 12.00.00.000000 AM   friday
                                        8 Stage6     04-JAN-08 12.00.00.000000 AM   friday    14-JAN-08 12.00.00.000000 AM   monday

                                    8 rows selected.

                                    SQL> select stage
                                      2       , extract(day from end_date - start_date)
                                      3         + extract(hour from end_date - start_date) / 24
                                      4         + extract(minute from end_date - start_date) / 24 / 60
                                      5         + extract(second from end_date - start_date) / 24 / 60 / 60 days
                                      6       , to_number(to_char(start_date,'d')) start_date_day_number
                                      7       , to_number(to_char(end_date,'d')) end_date_day_number
                                      8    from date_test
                                      9  /

                                    STAGE       DAYS START_DATE_DAY_NUMBER END_DATE_DAY_NUMBER
                                    ---------- ----- --------------------- -------------------
                                    Stage1     .0833                     3                   3
                                    Stage1     .0382                     3                   3
                                    Stage2     1.181                     3                   4
                                    Stage2     34.11                     3                   2
                                    Stage3         4                     2                   6
                                    Stage4         3                     6                   2
                                    Stage5        11                     2                   6
                                    Stage6        10                     6                   2

                                    8 rows selected.

                                    SQL> with t as
                                      2  ( select stage
                                      3         , extract(day from end_date - start_date)
                                      4           + extract(hour from end_date - start_date) / 24
                                      5           + extract(minute from end_date - start_date) / 24 / 60
                                      6           + extract(second from end_date - start_date) / 24 / 60 / 60 days
                                      7         , to_number(to_char(start_date,'d')) start_date_day_number
                                      8         , to_number(to_char(end_date,'d')) end_date_day_number
                                      9      from date_test
                                    10  )
                                    11  select stage
                                    12       , 24 * days          part1 -- all hours
                                    13       , 16 * trunc(days)   part2 -- for every 24 hours exclude 16 hours, so now you have 8 hours per day
                                    14       , 16 * trunc(days/7) part3 -- for every 7 days exclude 16 hours (8 hours for saturday and 8 for sunday
                                    15       , case
                                    16         when start_date_day_number > end_date_day_number
                                    17         then 16
                                    18         else 0
                                    19         end part5 -- a period below 7 days can still include a weekend if the daynumber of the
                                    20                   -- startdate is greater than the daynumber of the end_date.
                                    21       , 24 * days
                                    22         - 16 * trunc(days)
                                    23         - 16 * trunc(days/7)
                                    24         - case
                                    25           when start_date_day_number > end_date_day_number
                                    26           then 16
                                    27           else 0
                                    28           end whole_expression
                                    29    from t
                                    30  /

                                    STAGE      PART1 PART2 PART3 PART5 WHOLE_EXPRESSION
                                    ---------- ----- ----- ----- ----- ----------------
                                    Stage1         2     0     0     0                2
                                    Stage1     .9167     0     0     0            .9167
                                    Stage2     28.33    16     0     0            12.33
                                    Stage2     818.7   544    64    16            194.7
                                    Stage3        96    64     0     0               32
                                    Stage4        72    48     0    16                8
                                    Stage5       264   176    16     0               72
                                    Stage6       240   160    16    16               48

                                    8 rows selected.

                                    SQL> with t as
                                      2  ( select stage
                                      3         , extract(day from end_date - start_date)
                                      4           + extract(hour from end_date - start_date) / 24
                                      5           + extract(minute from end_date - start_date) / 24 / 60
                                      6           + extract(second from end_date - start_date) / 24 / 60 / 60 days
                                      7         , to_number(to_char(start_date,'d')) start_date_day_number
                                      8         , to_number(to_char(end_date,'d')) end_date_day_number
                                      9      from date_test
                                    10  )
                                    11  select stage
                                    12       , avg
                                    13         ( 24 * days
                                    14           - 16 * trunc(days)
                                    15           - 16 * trunc(days/7)
                                    16           - case
                                    17             when start_date_day_number > end_date_day_number
                                    18             then 16
                                    19             else 0
                                    20             end
                                    21         ) avg_hours
                                    22    from t
                                    23   group by stage
                                    24   order by stage
                                    25  /

                                    STAGE      AVG_HOURS
                                    ---------- ---------
                                    Stage1         1.458
                                    Stage2         103.5
                                    Stage3            32
                                    Stage4             8
                                    Stage5            72
                                    Stage6            48

                                    6 rows selected.
                                    Hope it helps.

                                    Regards,
                                    Rob.