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

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.

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.

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

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

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

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.

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.