Converting Seconds to HH:MM:SS

Question

I found a function to convert seconds to HH:MM:SS online, the function is this:

function sec2hms ($sec, $padHours = false) {
$hms = "";
$hours = intval(intval($sec) / 3600);
$hms .= ($padHours)
? str_pad($hours, 2, "0", STR_PAD_LEFT). ':'
: $hours. ':';
$minutes = intval(($sec / 60) % 60);
$hms .= str_pad($minutes, 2, "0", STR_PAD_LEFT). ':';
$seconds = intval($sec % 60);
$hms .= str_pad($seconds, 2, "0", STR_PAD_LEFT);
return $hms;
}

I know this works fine as if I do echo(sec2hms(3600)); it will return as 1:00:00

What I want to happen is to use that function to convert every single entry in the column timeran and then display it in a HTML table with the columns username and score, I am not sure how to do this so if someone could help me out then that'd be great. I am currently using the code below to display the table but that only shows time taken in seconds and I would like it to be in HH:MM:SS:

<tr>
<th>Rank</th>
<th>Username</th>
<th>Time Taken (s)</th>
<th>Score</th>
</tr>
</thead>
<tbody>
<?php
$result = mysql_query("SELECT * FROM gamescores ORDER BY Time DESC");
if (mysql_num_rows($result)) {
for($rank=1; $row = mysql_fetch_assoc($result); $rank++) {
    echo "<tr>
    <td>{$rank}</td>
    <td>{$row['username']}</td>
    <td>{$row['timetaken']}</td>
    <td>{$row['score']}</td>

If there is a better way of converting seconds to HH:MM:SS then that would be great too.

You could consider changing the column to be of `TIME` type. You would still need to convert when storing the data, but it might make reporting easier. — Neil, Aug 19 '11 at 20:55
possible duplicate of **[Convert seconds to Hour:Minute:Second](http://stackoverflow.com/a/20870843/67332)** — Glavić, Jan 02 '14 at 00:52

score 6 · Accepted Answer · answered Aug 19 '11 at 20:48

6

Use the MySQL SEC_TO_TIME function.

Example:

mysql> SELECT SEC_TO_TIME(2378);
        -> '00:39:38'

So you can write your SQL query as:

SELECT username, score, SEC_TO_TIME(timeran) as timetaken FROM gamescores ORDER BY Time DESC

Your code then becomes:

<?php
$result = mysql_query("SELECT username, score, SEC_TO_TIME(timeran) as timetaken FROM gamescores ORDER BY Time DESC");
if (mysql_num_rows($result)) {
for($rank=1; $row = mysql_fetch_assoc($result); $rank++) {
    echo "<tr>
    <td>{$rank}</td>
    <td>{$row['username']}</td>
    <td>{$row['timetaken']}</td>
    <td>{$row['score']}</td>

answered Aug 19 '11 at 20:48

JJ.

5,425
3
26
31

@JJ I had tried that previously and that didn't return what I wanted, it returned `Resource ID #8` – Jamie Aug 19 '11 at 20:50
I will try what you just edited your post with, thanks. – Jamie Aug 19 '11 at 20:51
1

Just managed to get it working, thank you, that does work faster then the other answer. – Jamie Aug 19 '11 at 21:00

score 0 · Answer 2 · answered Aug 19 '11 at 20:50

0

You need to wrap your output with the function. Put this code in the appropriate place.

echo "<td>". sec2hms($row['timetaken']) . "</td>";

answered Aug 19 '11 at 20:50

Jarrod Nettles

6,193
6
28
46

This works, but if you can make SEC_TO_TIME work, it will run much faster than this solution, especially for large datasets. – JJ. Aug 19 '11 at 20:55
Agreed, but it was good for him to learn how to do it this way as well. Jamie, please mark an answer as accepted if it is correct - welcome to S.O. – Jarrod Nettles Aug 19 '11 at 20:57

Converting Seconds to HH:MM:SS

2 Answers2

Linked

Related