2. PHPRunner General questions
  3. Time Arithmetic
This topic is locked

Time Arithmetic

 		12/14/2013 19:18:45
PHPRunner General questions
G
 GregJ author

I'm using PHPRunner 7 /mysql to overhaul an ancient work order system. It is made up of two tables - workorders (master) and timetracking (time details). Each workorders record can have several detail records in timetracking representing blocks of time spent on a job. Technicians enter their start_time and end_time in time format. (1:30 is an hour and a half)
Firstly, I need to be able to subtract start_time from end_time to produce a decimal number of hours saved in a third field (total_hours).
Then secondly, after I have total_hours calculated on each individual timetracking record, I need the total_hours fields from all related timetracking records totaled to a grand total field in the workorders master record for the total time spent on a specific job.
I am by no means an expert in php/mysql (that's why I'm using PHPRunner <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=question&id=21746&image=1&table=forumtopics' class='bbc_emoticon' alt=':)' /> so any help with this much appreciated.
Greg
T
 		Tempus_Erus 12/16/2013

Hi,
Dependant on how you have stored date/time in you db could be dependent on how you calculate the time differences.

http://www.php.net/manual/en/book.datetime.php is a good place to start.
Apologies I cannot be more precise - not an expert myself.
A
G
 		GregJ author 12/16/2013

When I posted here I was overwhelmed by the information that I found online in php and mysql forums. I couldn't find a way to make any of it work in PHPR. Late last night after spending much of the day on it I found a simple solution that works great.
Under the Query tab in PHPR I added a calculated field (the PHPR manual tells how). When creating the formula in the calc field I treated the two time fields as simple numeric fields like this: end_time - start_time. Surprisingly this gave me a number in the calculated field, in this case 90000. At first I thought this was just a wacky number until I realized that if I divided it by 10000 it gave me the number of hours between the two times. Weird, I still haven't figured that one out but it works great so I'm not asking any more questions.
To total all the times in the detail records for a specific work order # I just used the SUM function in another calculated field like this SUM(end_time - start_time)/10000) and that works perfectly too.
To get these totals to show up in the master work order record I linked the two tables on work order # (still under the Query tab). Another calculated field using a combination of my simple formulas gives me a grand totals that I can use in my master table views & reports.
None of these calculated values are stored in the tables, they are created on the fly. I'm open to comments and suggestions from anyone who has more experience on these things but right now I'm just happy that it's working.
F
 		fantasmino 12/16/2013

You can try use datediff

In phpr query tab

datediff(first_date, second_date) AS days