How to calculate time in seconds

[adrianle]

I've got a mysql table where I want to enter some amount of time, say 5 minutes (5:00) into a column, which will then be pulled by a page and a calculation done (5 minutes x 60 seconds = 300 seconds).   I'm unsure what type of mysql field type I should use to hold this data. Any ideas?

[Barand]

Use a TIME type field (format hh:mm:ss) for maximum functionality. If you need it converted to seconds use TIME_TO_SEC() function

mysql> SELECT TIME_TO_SEC('00:05:30');
+-------------------------+
| TIME_TO_SEC('00:05:30') |
+-------------------------+
|                     330 |
+-------------------------+

[requinix]

If you're going to convert it all the time, would it be better to store the number of seconds instead?

[adrianle]

The time value is coming from a TIME column type in the table, so the example mysql> SELECT TIME_TO_SEC('00:05:30'); doesn't help much I'm afraid. I've tried to use this function with the column name plugged into the select statement but without success.   To answer Anemic's question.. that would require having to calculate out the time slice into seconds before entering it into the table. The point of this exercise is to have PHP calculate the total number of seconds for us!

[requinix]

The point of this thread was to find out what data type to use in MySQL. Barand said TIME which is, technically speaking, the appropriate format at it models the actual data you're working with. You talked about how your code would convert the value into seconds which is why I asked about simply storing the number of seconds.

 

If your new question is about how you can have PHP convert "HH:MM:SS" into a number of seconds, the simplest answer is the most obvious:

list($h, $m, $s) = explode(":", $time);
$seconds = 3600 * $h + 60 * $h + $s;

[Barand]

 I've tried to use this function with the column name plugged into the select statement but without success.  

 

Then you are doing something wrong

mysql> CREATE TABLE test_time (thetime TIME);
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO test_time (thetime) VALUES ('01:30:10'),('00:05:00');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT TIME_TO_SEC(thetime) as sec FROM test_time;
+------+
| sec  |
+------+
| 5410 |
|  300 |
+------+
2 rows in set (0.00 sec)