calculate datetime difference

A place for users and developers of the Xataface to discuss and receive support.

calculate datetime difference

Postby mandra » Thu Jul 15, 2010 11:09 am

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!
mandra
 
Posts: 10
Joined: Fri Jun 18, 2010 8:56 am

Re: calculate datetime difference

Postby chichi » Thu Jul 15, 2010 11:31 am

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
chichi
 
Posts: 28
Joined: Fri Jul 09, 2010 3:05 am

Re: calculate datetime difference

Postby mandra » Fri Jul 16, 2010 4:21 am

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'");
}
}
?>
mandra
 
Posts: 10
Joined: Fri Jun 18, 2010 8:56 am

Re: calculate datetime difference

Postby shannah » Tue Jul 20, 2010 10:10 am

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"
shannah
 
Posts: 4457
Joined: Wed Dec 31, 1969 5:00 pm


Return to Xataface Users

Who is online

Users browsing this forum: Bing [Bot], Google [Bot] and 0 guests

cron
Powered by Dataface
© 2005-2007 Steve Hannah All rights reserved