9 Replies Latest reply on Mar 20, 2018 3:25 AM by rp0428

    Best practice to save lasting time of .... ?

    2877737

      Hi,

      I have to keep in my table time of lasting tasks in my system. Time of lasting a task it means time, from start to end the task.

      I wondered which data type to apply ? Varchar2 ? - it's problematic to count avg time, max time etc. Data ? it's not an hour Timestamp ? it's not an hour ?

      Which practice is the best ? Mayby there is any standard ?

       

      Thx.

      Paul.

        • 1. Re: Best practice to save lasting time of .... ?
          thatJeffSmith-Oracle

          if you're storing a point in time, or 2 in order to track start and stop times, then DATE. If you need accuracy to the fraction of a second, then TIMESTAMP - note that TIMESTAMP also allows you to be specific about timezone

           

          if you just need to store the duration of time for a transaction, then check out the INTERVAL data type - then you can say things like '3 hours, 4 minutes, and 29 seconds'

           

          Never use VARCHAR2 or CHAR or INTEGER to store date/time data - it will always bite you in the end.

           

          I never use words like never/always, but this is a valid exception.

          • 2. Re: Best practice to save lasting time of .... ?
            2877737

            Thx Jeff,

            but i've never use Interval data type before.

            So, if I have 12:45 what means 12 minutes and 45 seconds, how to convet it to Interval ? (now I keep it in Varchar2 column)

             

            Paul.

            • 3. Re: Best practice to save lasting time of .... ?
              thatJeffSmith-Oracle

              if you have 12:45, you have no interval, you only have the DATE

               

              If you had 2 times, you could compute an interval, or if your application had the interval, then you could store that.

              • 4. Re: Best practice to save lasting time of .... ?
                2877737

                I was wondering and I have one more question.

                If a task last more than one day, eg. 25 hours 44 min. and 34 sek. 25:44:34 it is posible to store that as DATE ? I think so not.

                • 5. Re: Best practice to save lasting time of .... ?
                  thatJeffSmith-Oracle

                  Correct not possible set a period of time as a date. Always possible to record two dates compute the amount of time between them.

                  • 6. Re: Best practice to save lasting time of .... ?
                    mathguy

                    thatJeffSmith-Oracle wrote:

                     

                    if you have 12:45, you have no interval, you only have the DATE

                     

                    Why? If the data is currently in VARCHAR2 data type,   12:45 certainly looks more like an interval HOUR to MINUTE (we assume - perhaps) than a "date".

                    • 7. Re: Best practice to save lasting time of .... ?
                      mathguy

                      If your data is currently in VARCHAR2 data type (which seems to be what you are describing), and if you need to keep track of task duration in hours and minutes, even if it's 25 hours and 33 minutes (or 188 hours and 40 minutes, for example), then NUMBER is the best data type. In that case, present "duration" as 25.55 (25 hours and 33 minutes, 33/60 = 0.55), and 180 hours and 40 minutes as 180.666666667.

                       

                      This would help, for example, if you need to allocate resources (or pay teams of workers) based on number of hours - converting to days would make no sense in such instances. For example, if you pay someone $20 per hour and they worked 188 hours and 40 minutes, it does you absolutely no good to convert to days, hours and minutes.

                       

                      If you need to show duration in days, hours and minutes (and perhaps seconds), then the interval day to second data type is more appropriate. You will need a bit of care when you convert something like 25 hours and 33 minutes to this type of interval - but it's not too difficult.

                       

                      One thing to keep in mind - arguing in favor of the NUMBER data type over INTERVAL - if you need to calculate a SUM over a column, or an AVG, then you will be able to do this with NUMBER but not with INTERVAL. (Not clear why the developers at Oracle didn't write the code to define aggregates like these for INTERVAL data type... this shall remain a mystery.)

                      • 8. Re: Best practice to save lasting time of .... ?
                        mathguy

                        thatJeffSmith-Oracle wrote:

                         

                         

                        Never use VARCHAR2 or CHAR or INTEGER to store date/time data - it will always bite you in the end.

                         

                        I never use words like never/always, but this is a valid exception.

                         

                        I agree - but the OP does not need to store DATE/TIME data, he/she needs to store INTERVAL. I see no reason to avoid NUMBER (surely not INTEGER!) for such data, especially given some of the (self-inflicted) limitations of the INTERVAL data type.

                        • 9. Re: Best practice to save lasting time of .... ?

                          Which practice is the best ? Mayby there is any standard ?

                          You never did answer the questions ask: WHAT FORMAT and values do you have now.

                           

                          The 'standard' for logging is:

                           

                          1. log the start date/time

                          2. log the end date/time

                          3. do the calculations of 'interval' when you query the data

                           

                          So do you have both start and end date/time values? Or do you have a 'duration/interval' value?

                           

                          You can store intervals as intervals or you can convert a numeric duration to an interval when/if you need to:

                          https://docs.oracle.com/database/121/SQLRF/functions129.htm#SQLRF00682

                          The interval data types are INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND. Values of interval data types are sometimes called intervals.

                          . . .

                          Purpose NUMTODSINTERVAL converts n to an INTERVALDAYTOSECOND literal. The argument n can be any NUMBER value or an expression that can be implicitly converted to a NUMBER value.