MySQL storing duration time - datatype?

Question

I need to calculate the time a user spends on site. It is difference between logout time and login time to give me something like "Mr X spent 4 hours and 43 minutes online". So to store the4 hours and 43 minutes i declared it like this: duration time NOT NULL

Is this valid or a better way to store this? I need to store in the DB because I have other calculations I need to use this for + other use cases.

Solution

Storing it as an integer number of seconds will be the best way to go.

• The UPDATE will be clean and simple - i.e. duration = duration + $increment
• As Tristram noted, there are limitations to using the TIME field - e.g. "TIME values may range from '-838:59:59' to '838:59:59'"
• The days/hours/minutes/seconds display formatting won't be hardcoded.
• The execution of your other calculations will almost surely be clearer when working with an integer "number of seconds" field.

Comments

[Juds]

integer wont give exact time like 4 hours, 45 minutes 32 seconds unless i store in seconds? I was hoping to auto format the entry directly inthe DB as x hours, x minutes. (maybe add x seconds too) instead of storing it as 474747474 seconds which then again requires conversion into a human readable form.

[Dan J]

@Juds I'd argue that the convenience of having a formatted time interval in the DB is outweighed by the many other factors @Tristram and @Riedsio have mentioned. Why not let interval formatting be a presentation-layer concern? :)

[Ashley]

time isn't limited to 24 hours, see above

[Gerald]

I'd definitely go with TIME. I can't think of anybody spending 34 days in a row online. If so, I'm ready to let this rare case run into an error. UPDATE statements can be easily done using ADDTIME(). With this you can also add like 30 minutes and 23 seconds without transferring that into seconds. Nothing is hardcoded, you can always do TIME_TO_SEC() – if you really need that (what I doubt).

[ashleedawg]

...not sure what you mean. Is there some documentation on "UNIX-epoch-delta"? An example would also be helpful. Also, Welcome to Stack Overflow! (Check out How to Answer.)

[druid62]

A "UNIX-epoch-delta" is just the UNIX time() value, which is the number of seconds since the UNIX-epoch, which is 1970-01-01 00:00:00 UTC.

[Synchro]

This is a bit pointless as it replicates exactly what the standard timestamp functionality built In to MySQL already does.