Forum Stats

  • 3,852,382 Users
  • 2,264,100 Discussions
  • 7,905,056 Comments

Discussions

Categories

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

2877737
2877737 Member Posts: 45
edited Mar 19, 2018 11:25PM in SQL Developer

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.

Tagged:
2877737
· Share on TwitterShare on Facebook

Best Answer

Answers

  • thatJeffSmith-Oracle
    thatJeffSmith-Oracle Distinguished Product Manager Posts: 8,764 Employee
    edited Mar 19, 2018 12:44PM

    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.

    2877737
    · Share on TwitterShare on Facebook
  • 2877737
    2877737 Member Posts: 45
    edited Mar 19, 2018 1:29PM

    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.

    · Share on TwitterShare on Facebook
  • thatJeffSmith-Oracle
    thatJeffSmith-Oracle Distinguished Product Manager Posts: 8,764 Employee
    edited Mar 19, 2018 1:31PM Answer ✓

    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.

    2877737
    · Share on TwitterShare on Facebook
  • 2877737
    2877737 Member Posts: 45
    edited Mar 19, 2018 2:32PM

    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.

    · Share on TwitterShare on Facebook
  • thatJeffSmith-Oracle
    thatJeffSmith-Oracle Distinguished Product Manager Posts: 8,764 Employee
    edited Mar 19, 2018 3:48PM

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

    2877737
    · Share on TwitterShare on Facebook
  • mathguy
    mathguy Member Posts: 10,675 Blue Diamond
    edited Mar 19, 2018 5:35PM 
    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".

    · Share on TwitterShare on Facebook
  • mathguy
    mathguy Member Posts: 10,675 Blue Diamond
    edited Mar 19, 2018 5:41PM

    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.)

    · Share on TwitterShare on Facebook
  • mathguy
    mathguy Member Posts: 10,675 Blue Diamond
    edited Mar 19, 2018 5:44PM 
    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.

    · Share on TwitterShare on Facebook
  • Unknown
    Unknown
    edited Mar 19, 2018 11:25PM 
    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 <span class="codeinlineitalic">n</span> to an INTERVALDAYTOSECOND literal. The argument <span class="codeinlineitalic">n</span> can be any NUMBER value or an expression that can be implicitly converted to a NUMBER value.
    · Share on TwitterShare on Facebook
This discussion has been closed.