calculate datetime difference

[mandra]

Hello,
I have a table with a starttime and stoptime column in datetime format. I would like a third "timediff" column to be updated automatically. Is there an easy way to do this, I don't have any experience in php and very little sql and was hoping to be able to write something like:
__sql__ = "SELECT ID, stoptime - starttime FROM table" or
__sql__ = "SELECT TIMEDIFF(stoptime - starttime) FROM table"
in fields.ini or valuelists.ini - that does'nt work though...

Thank you!

[chichi]

Hello mandra,
I guess you have to use the action-functions, each time a record is saved or changed, the field needs to be calculated and placed in the table of the database. Sorry i cant give you more infos on this. I am just starting with this framework and want to learn mor about it, cause I love to big possibilities of this.
chichi

[mandra]

Ok, after looking at
viewtopic.php?f=4&t=5062&p=24641&hilit=triggers+sql#p24641
the following worked for me for a table "logbook" containing the fields "LogID", "startTime", "stopTime" and "elapsedTime".

<?
class tables_logbook {
function after_action_new($params=array()){
$app =& Dataface_Application::getInstance();
$record =& $params['record'];
$ID=$record->val('LogID');
$db = $app->db();
$res = mysql_query("UPDATE logbook SET elapsedTime = TIMEDIFF(stopTime, startTime) WHERE LogID='$ID'");
}
}
?>

[shannah]

The problem with this approach is that it will only store elapsed time when adding a record using the new record form. It won't update the elapsed time when you update the record, or if you add records programmatically, or if you add them via the new related record form.

If you really want to store the elapsedTime as its own physical field in the database (rather than a calculated field) you should probably do the calculation in the beforeSave() trigger:

Code: Select all

function beforeSave(&$record){
    $record->setValue('elapsedTime', $record->val('endTime')-$record->val('startTime'));
}


Note: this example assumed that endTime and startTime are stored in the database as integers so we can do the simple subtraction. If they are stored as dates or timestamps you must first convert them to integers for this arithmetic to work. E.g.:

Code: Select all

$startTimeSeconds = strtotime($record->strval('startTime'));


Another way that may be better is to not store the elapsedTime field in the database at all, but rather leave it as a calculated field. You can do this via the __sql__ directive in the fields.ini file:

Code: Select all

__sql__ = "select t.*, timediff(endTime, startTime) as elapsedTime from mytable t"