40

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.

Riedsio
  • 9,758
  • 1
  • 24
  • 33
Juds
  • 507
  • 2
  • 5
  • 7

3 Answers3

54

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.
Riedsio
  • 9,758
  • 1
  • 24
  • 33
  • 3
    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). – Gerald Sep 03 '16 at 14:14
0

I wouldn't use time as you would be limited to 24 hours. The easiest would just to store an integer in minutes (or seconds depending on the resolution you need).

Tristram Gräbener
  • 9,601
  • 3
  • 34
  • 50
  • 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. – Juds Dec 21 '10 at 11:20
  • @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? :) – Dan J Dec 21 '10 at 20:38
  • 6
    time isn't limited to 24 hours, see above – Ashley Mar 29 '13 at 11:44
-3

Consider storing both values as a UNIX-epoch-delta.

I generally prefer to use a signed (64b) bigint (for secondly resolution), or a (signed) (64b) double (if fractional seconds are needed), or a signed (32b) int (if scaled down to minutely or hourly).

Make the unit explicit in the name of the column, for example with a suffix like "_epoch_minutely", for example: "started_epoch_minutely", "finished_epoch_minutely".

druid62
  • 109
  • 3
  • ...not sure what you mean. Is there some documentation on "`UNIX-epoch-delta`"? An example would also be helpful. Also, *Welcome to [so]!* (Check out [answer].) – ashleedawg Jul 20 '19 at 08:32
  • 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. – druid62 Sep 23 '19 at 11:41
  • This is a bit pointless as it replicates exactly what the standard timestamp functionality built In to MySQL already does. – Synchro Jun 29 '22 at 16:49