CODEWITHSUNDEEP
phpmysql
Kentot
Kentot

Reputation: 542

Php Time Format

I have here a mysql query that get the average of the column(the column data type is 'time'). The column values for example are: 00:00:55, 00:00:59, 00:01:03

SELECT AVG(TIME_TO_SEC(column_name)) FROM table_name)AS average_result

In my Php I formatted the result this way:

<?php foreach($display_average as $da){
     echo date("H:i:s", ($da["average_result"]));
}
?>

Outputs: 08:00:59 instead of 00:00:59, Why does this starts with 08? Or did I miss something? Thanks!

Upvotes: 1

Views: 123

Answers (3)

Sami
Sami

Reputation: 8419

Always go for standard/formal approaches. But if anyhow you need it custom, then you can do almost everything with programming. Here we go

Get your time as numbers (number of seconds in your time filed) from database as

SELECT 
AVG
(
    HOUR(column_name) * 3600
    + MINUTE(column_name) * 60
    + SECOND(column_name)
) AS numeric_average_result FROM table_name

Now you can convert number of seconds to proper time as

foreach($display_average as $da)
{
    $r = numToTime($da["numeric_average_result"]);
    echo "<br>".$r;
}


function numToTime($num)
{
    $seconds = $num%60;
    $num = (int)($num/60);
    $minutes = $num%60;
    $hours = (int)($num/60);
    return make2digit($hours).":".make2digit($minutes).":".make2digit($seconds);
}

function make2digit($val)
{
    if(strlen($val) == 1)
       return "0".$val;
    else
       return $val;
}

Upvotes: 0

islq
islq

Reputation: 86

see php manul, about the date_default_timezone_set your timezone is +8

the default date.timezone of PHP is utc, u can change it to date.timezone = PRC

date_default_timezone_set('UTC');
echo date("H:i:s", 59);//00:00:59

//date_default_timezone_set('RPC');
//echo date("H:i:s", 59);//08:00:59

Upvotes: 0

deceze
deceze

Reputation: 522015

Both PHP's date/time functions and MySQL's date/time data types handle wall clock timestamps, not durations; i.e. 00:00:55 means fifty-five seconds past midnight. This is not what you want; you couldn't handle durations longer than 23 hours, 59 minutes, 59 seconds, because the data types and functions you're using are handling clock time, which cannot exceed these values.

Your specific issue stems from timezone settings. Your larger issue is that you need to store simple integer values expressing elapsed seconds or minutes; not timestamps. To format that into a human readable string in PHP you can use the DateInterval class.

Upvotes: 2

Related Questions