MySQL storing duration time - datatype?

mysql time types duration
Juds picture Juds · Dec 21, 2010 · Viewed 33.5k times · Source

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.

Answer

Riedsio picture Riedsio · Dec 21, 2010

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.

Related questions

Should MySQL have its timezone set to UTC?

Follow up question of https://serverfault.com/questions/191331/should-servers-have-their-timezone-set-to-gmt-utc Should the MySQL timezone be set to UTC or should it be set to be the same timezone as the server or PHP is set? (If it is not UTC) What …

Read More
How to set a maximum execution time for a mysql query?

I would like to set a maximum execution time for sql queries like set_time_limit() in php. How can I do ?

Read More
Convert seconds to human readable time duration

How would I best convert 90060 (seconds) to a string of "25h 1m"? Currently I'm doing this in SQL: SELECT IF( HOUR( sec_to_time( sum(time_to_sec(task_records.time_spent)) ) ) > 0, CONCAT( HOUR(sec_to_time(sum(time_…

Read More