php time calculation

[arjdat]

i have a db table with four columns: adin, edin, out and diff

first three columns' values are inserted manually.

i want the diff column to show difference between out time and maximum of edin time and adin time and insert the record into diff column in my phpmyadmin.

[Barand]

A. What format are those columns?

 

B. What have you tried?

[arjdat]

columns are in datetime format; i did most of job in dreamweaver including the connection and form to insert values and showing the result in table by dynamic table; so the codes are not written by me but by dreamweaver itself.

[JonnoTheDev]

Show us some of your data as it is displayed in the table and then show us the values you expect to be in your empty fields.

[arjdat]

ED IN AD IN OUT DIFF 12:20:00 12:10:00 12:23:00 00:00:00

 

when i use integer format, diff column gives exact result i.e. out-max(edin, adin); when i want them in time format it does not work; like in the example given above, diff should be 00:03:00 or even better 00:03 (omitting the second part and giving only hr; mm). below is the code i use for getting the value and put into diff column

 

 

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {

  $insertSQL = sprintf("INSERT INTO chk (edin, adin, `out`, diff) VALUES (%s, %s, %s, %s)",

                       GetSQLValueString($_POST['edin'], "date"),

                       GetSQLValueString($_POST['adin'], "date"),

                       GetSQLValueString($_POST['out'], "date"),

  GetSQLValueString($_POST['out'] - max($_POST['adin'], $_POST['edin']), "date"));

[JonnoTheDev]

Firstly you should use MySQL TIME type for your 'diff' field. It would be even better if you could use the DATETIME format for the other 3 fields and include the date in the data. If you are using a VARCHAR then change it.

 

To insert the missing data using the INSERT query you must first use PHP to calculate the time difference. To do this you will need the date. There is an issue. If the start time is say 5pm and the end time is 2am the following morning you will need the actual end date as just adding the current day to your time values will not work. So, $_POST['edin'] must be a datetime and $_POST['out'] must be a datetime YYYY-MM-DD HH::MM::SS

 

It is then easy to find the time difference using a simple function. If you are using PHP 5.3 then there are objects that can do this much easier.

<?php
// function get the time difference between start and end date
function get_time_diff($start, $end)
{
 $diff = strtotime($end) - strtotime($start);
 $hr = floor($diff / 3600);
 $remaining = $diff - $hr * 3600;
 return sprintf('%02d', $hr) . gmdate(':i:s', $remaining);
}

// this should be what is in your $_POST['edin'] variable
$start = '2013-07-01 12:20:00';
// this should be what is in your $_POST['out'] variable
$end = '2013-07-01 12:23:00';
// should print 00:03:00
$out = get_time_diff($start, $end);
echo $out;
?>

Edited July 3, 2013 by neil.johnson

[AbraCadaver]

Why are you storing a calculation in the DB? You should store the values that are used in the calculation and then calculate the value when you need to use it.

[JonnoTheDev]

Why are you storing a calculation in the DB? You should store the values that are used in the calculation and then calculate the value when you need to use it.

 

Not really. What if you wanted to pull a report on all people who worked longer than 5 hours?

[AbraCadaver]

Not really. What if you wanted to pull a report on all people who worked longer than 5 hours?

Yes really.  That's basic database.  You run the calculation in the query: http://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html

 

Also, don't use math to calculate date/time differences, use the time change aware functions or datetime class.  Your get_time_diff() example fails if the dates on are on opposite sides of a daylight savings time change.

[JonnoTheDev]

Did mention that there are better methods if php 5.3 is available