adrianle Posted September 29, 2015 Share Posted September 29, 2015 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? Quote Link to comment Share on other sites More sharing options...
Barand Posted September 29, 2015 Share Posted September 29, 2015 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 | +-------------------------+ Quote Link to comment Share on other sites More sharing options...
requinix Posted September 29, 2015 Share Posted September 29, 2015 If you're going to convert it all the time, would it be better to store the number of seconds instead? Quote Link to comment Share on other sites More sharing options...
adrianle Posted September 29, 2015 Author Share Posted September 29, 2015 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! Quote Link to comment Share on other sites More sharing options...
requinix Posted September 29, 2015 Share Posted September 29, 2015 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; Quote Link to comment Share on other sites More sharing options...
Barand Posted September 29, 2015 Share Posted September 29, 2015 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) 1 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.