Jump to content

Recommended Posts

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?

Link to comment
https://forums.phpfreaks.com/topic/298372-how-to-calculate-time-in-seconds/
Share on other sites

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 |
+-------------------------+

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!

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;

 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)

  • Like 1
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.