mysql将十进制时间转换为xx：xx格式

I am trying to convert a decimal time into an actual time format with hours and minutes, ie: in xx:xx hours.

My query is:

select SUM(vt.vluchtdec) AS vluchttijddecimal

from tbl_vluchtgegevens vg

left join tbl_vluchttijd vt
on vg.vluchttijddec = vt.vluchttijdID

WHERE vg.vertrekdatum <=NOW();

And I am echoing

. $row['vluchttijddecimal'] .

I have also tried this, but this also still gives me my response in a decimal format:

$result = mysql_query("select SUM(vt.vluchtdec) AS vluchttijddecimal

from tbl_vluchtgegevens vg

left join tbl_vluchttijd vt
on vg.vluchttijddec = vt.vluchttijdID

WHERE vg.vertrekdatum <=NOW();");

while($row = mysql_fetch_array($result))
{
$dec = $row['vluchttijddecimal'];
function 
convertTime($dec)
{
// start by converting to seconds
$seconds = $dec * 3600;
// we're given hours, so let's get those the easy way
$hours = floor($dec);
// since we've "calculated" hours, let's remove them from the seconds variable
$seconds -= $hours * 3600;
// calculate minutes left
$minutes = floor($seconds / 60);
// remove those from seconds as well
$seconds -= $minutes * 60;
// return the time formatted HH:MM:SS
return lz($hours).":".lz($minutes).":".lz($seconds);
}

// lz = leading zero
function lz($num)
{
return (strlen($num) < 2) ? "0{$num}" : $num;
}

echo "" .$dec."";

In MS Access I would do something like this:

CInt([vluchttijddecimal]) & ":" & Format([vluchttijddecimal]*60 Mod 60;"00")

But this does not work or I don't know how to do so in MySQL / php.

I think I have calculated your time values... although it was kinda pain.

It appears your "decimal time" is "hours.minutes"? Rather horrible and definitely not a good format: for dealing with time its best to stick to integers that specify either a total of minutes/seconds/hours or whatever granularity you need.

But assuming it is hours.minutes, you should be able to do it like this in PHP:

while($row = mysql_fetch_array($result))
{
$dec = $row['vluchttijddecimal'];
return sprintf("%2d:%2d", floor($dec), floor(($dec - floor($dec))*100));
}

Hopefully I am correct in assuming that you mean, for example that 2.5 hours = 2H 30mins. If so, then your 'time' is a time interval and is best represented by the DateInterval class.

This function will do what you want:-

/**
 * Converts a 'decimal time' in the format 1.5hours to DateInterval object
 * 
 * @param Int $decimalTime
 * @return DateInterval
 */
function decTimeToInterval($decimalTime)
{
    $hours = floor($decimalTime);
    $decimalTime -= $hours;
    $minutes = floor($decimalTime * 60);
    $decimalTime -= ($minutes/60);
    $seconds = floor($decimalTime * 3600);

    $interval = new \DateInterval("PT{$hours}H{$minutes}M{$seconds}S");
    return $interval;
}

echo decTimeToInterval(512.168)->format("%H:%I:%S");

See it working

If you want to add times in the format 'H:i' without converting them to and from decimals, you can do it like this:-

function sumTimes($time1, $time2)
{
    list($hours1, $minutes1) = explode(':', $time1);
    list($hours2, $minutes2) = explode(':', $time2);
    $totalHours = $hours1 + $hours2;
    $totalMinutes = $minutes1 + $minutes2;
    if($totalMinutes >= 60){
        $hoursInMins = floor($totalMinutes/60);
        $totalHours += $hoursInMins;
        $totalMinutes -= ($hoursInMins * 60);
    }
    return "$totalHours:$totalMinutes";
}

echo sumTimes('12:54', '100:06') . PHP_EOL;
echo sumTimes('12:54', '100:20') . PHP_EOL;

See it working

You can do this in you SQL statement something like this:

SELECT CONCAT(CEIL(mydecimal),':', LPAD(Floor(mydecimal*60 % 60),2,'0')) as formated text

Where mydecimal is your unformatted field name

For anyone that is interested... This is how you would convert decimal time (Where 0.1 == 6 minutes) to hours and minutes (0.2333 == 14 minutes) in MYSQL alone. no PHP is needed. This also accounts for the need to round seconds to minutes.

SELECT CONCAT(FLOOR(timeInDec),':', LPAD(ROUND((timeInDec - FLOOR(timeInDec)) * 60) % 60,2,0)) AS TimeInHoursMinutes
FROM YourTable;

Replace timeInDec with the column name that contains the decimal time you would like to convert.

This will return 0:06 for 0.1000 decimal value so leading zeros are accounted for in single digit minutes.

This is what I used for my Payroll System:

SELECT If(total_late>0, LPAD(CONCAT(REPLACE(FLOOR(total_late/60) + FORMAT(total_late%60*0.01,2), '.', ':'), ':00'), 8, 0), '00:00:00') FROM MyTable

I multiplied it by 0.01 because my variables are in Seconds. Eg. 60.00 = 1min