2. PHPRunner General questions
  3. Calculate differences in two times with adjustments
This topic is locked

Calculate differences in two times with adjustments

 		2/13/2008 10:47:32 PM
PHPRunner General questions
J
 joeedel author

Hello,

I would like to calculate the difference in two times with the option to adjust the outcome by minutes, So if the students start a test at 2:15 pm and finish at 3:47pm and I want to subtract 7 minutes from the total time because of interruptions the result is 1 hour and 25 minutes. I have 3 fields TimeStart, TimeStop, and Adjustment here is what I think it should look like but am having problems:

[codebox]SELECT TIMEDIFF(`TimeStart`,`TimeStop`) - (`Adjustment`60) as `TotalTime`[/codebox]

I would assume that the a timestamp would be the the best type of field for TimeStart/TimeStop as it would save the time in seconds then multiply the Adjustment field 60 to get seconds then subtract but I am having issues because I cant seem to make a timestamp type field in phprunner or correctly subtract times with TIME or DATETIME type fields.
Thanks,

Joe
J
 		Jane 2/14/2008

Joe,
to setup field as Time select Time on the "View as" settings dialog on the Visual Editor tab.

If you want to convert seconds to time on the page dynamically use Custom format on the "View as" settings dialog.
J
 		joeedel author 2/15/2008

Joe,

to setup field as Time select Time on the "View as" settings dialog on the Visual Editor tab.

If you want to convert seconds to time on the page dynamically use Custom format on the "View as" settings dialog.


I am familiar with how to set a time field, maybe I am not asking my question correctly, my question is more of a best practice sort of thing. In the past I have always stored Dates/Times as unix timestamps in the appropriate mysql field usually of an INT type, this has made for easy addition and subtraction of times. For Example DATE: 02 / 15 / 2008 @ 20:09 = 1203124140 and DATE: 02 / 15 / 2008 @ 21:09 = 1203127740, subtract the first timestamp of 1203124140 from the second 1203127740 and I can easily see that 3600 seconds have passed between the two times then I can divide by 60 or whatever to get minutes, hours, days. Furthermore it is also very easy to adjust the end result by adding additional seconds using a 3rd field. So my question is two part, part one - Can phprunner do this save a date/time as a timestamp then retrieve it as a readable date time (I am confident that it can) and how? part two - Is this method a "best practice" or is there a better way or other more preferred method that is used? I am open to any suggestions.
Thanks,

Joe
J
 		joeedel author 2/17/2008

I think I have it figured out, I am using the code below where both the TimeStart and TimeStop fields are DATETIME fields to figure out the difference in times. If anyone has any input please let me know.

[codebox]((UNIX_TIMESTAMP(`TimeStop`) - UNIX_TIMESTAMP(`TimeStart`) + (`Adjustment` * 60)) / 3600) as `Hours`[/codebox]