php mysql time elapsed calculation

Question

I have an app [iphone], that sends to a server some times [using json], so the times look like hh:mm 24 hour format, the time gets saved in the db as varchar,

I need to calculate the elapsed time = endTime - startTime

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

so how to calculate the elapsed time, with out changing the varchar type of field in my db?, can I convert this hh:mm to an int? for the operation?, and then showing it again as a hh:mm, possibly to save in other table?

thanks a lot!

Denis de Bernardy · Accepted Answer · 2011-06-08 00:34:41Z

3

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

You can cast it, but you'd be better off having that as a datetime to start with.

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

answered Jun 8, 2011 at 0:34

Denis de Bernardy

79.1k14 gold badges138 silver badges158 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user680786 · Accepted Answer · 2011-06-08 00:36:28Z

2

Easy:

$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

answered Jun 8, 2011 at 0:36

user680786

Comments

Community · Accepted Answer · 2020-06-20 09:12:55Z

2

In 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:

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

1307495400

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Jun 8, 2011 at 0:33

Michael Robinson

29.6k12 gold badges109 silver badges131 bronze badges

Collectives™ on Stack Overflow

php mysql time elapsed calculation

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related