Geek asks and answers

Expired php mysql calculation

I have an application [iphone] that is sent to the server several times [using json], so the time looks like hh: mm 24-hour format, the time is saved in db as varchar.

I need to calculate elapsed time = endTime - startTime

but my problem is that I have time in db as varchar !, no timestamp,

  • so how to calculate elapsed time without changing the type of the varchar field in my db ?, can I convert this hh: mm to int? for operation ?, and then showing it again as hh: mm, perhaps to save it in another table?

Many thanks!

+3
source share
3 answers

Simply:

$start_time = '11:10';
$end_time = '18:55';

$start_time = explode(':', $start_time);
$end_time = explode(':', $end_time);

$elapsed_time = $end_time[0]*60+$end_time[1]-$start_time[0]*60-$start_time[1];
// in minutes.
$elapsed_hours = floor($elapsed_time/60);
$elapsed_minutes = $elapsed_time-$elapsed_hours*60;

print $elapsed_hours.':'.$elapsed_minutes;
// 7:45
+2
source

so how to calculate elapsed time without changing the type of the varchar field in my db?

, .

 cast(endtime as datetime) - cast(starttime as datetime) -- yields an interval
+3

PHP:

$json_time = '13:10';
$json_format = 'H:i';

$date = DateTime::createFromFormat($json_format, $json_time);

$mysql_format = 'Y-m-d H:i:s';
echo "Format: $mysql_format; " . $date->format($mysql_format) . "\n";

echo $date->getTimestamp();

Yeilds:

: Y-m-d H: i: s; 2011-06-08 13:10:00

1307495400

+2

More articles:


All Articles