Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL storing duration time - datatype?

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.

like image 901
Juds Avatar asked Dec 21 '10 10:12

Juds


People also ask

What is the data type for time duration?

The xs:duration data type represents a duration of time that is expressed by the Gregorian year, month, day, hour, minute, and second components. Derived from data type xdt:anyAtomicType. The range that can be represented by this data type is from -P83333333333333Y3M11574074074DT1H46M39.

Which data type is used to store time in MySQL?

The DATETIME type is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in ' YYYY-MM-DD hh:mm:ss ' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59' . The TIMESTAMP data type is used for values that contain both date and time parts.

Is there datatype to store time?

The best data type would be DATETIME.

What should be the datatype of duration in SQL?

SQL Server accepts datetime values in ISO 8601 and ANSI formats, which are: yyyy-mm-ddThh:mm:ss. nnnnnn and yyyy-mm-dd hh:mm:ss. nnnnnn, respectively.


1 Answers

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.
like image 158
Riedsio Avatar answered Sep 20 '22 15:09

Riedsio