calculate time between two date fields

[ThomasB]

Tried to calculate the duration of two date times today and found this thread. However I think the example above is wrong.
I came up with this solution:

$totalMinutes = datetime\diff(incidentEndtime, incidentstarttime, 'minutes');
$hours = number\format(number\floor(($totalMinutes / 60), 0));
$minutes = number\format(number\floor(($totalMinutes - ($hours * 60)), 0));

impactDuration = string\concatenate($hours,' Hours ', $minutes, ' Minutes');​

90 minutes in the example from Reaper_ZA would have resulted into 1,5 hours and then rounded up to 2 hours

Feel free to correct me, but so far it seems to work.

[Reaper_ZA]

Ok I actually figured it out

$totalMinutes = datetime\diff(departureTime, arrivalTime, 'minutes');
$hours = number\format(($totalMinutes / 60), 0);
$minutes = (($totalMinutes / 60 ) - $hours) * 60;

calloutTotalTime = string\concatenate($hours, ' Hours', $minutes, ' Minutes');

[Reaper_ZA]

Hi There! Maximus

I know this is resurrecting a post from the Graveyard... But trying to do a similar setup on the newer version of the CRM system, and ran into a snag.
I tried the code as suggested but does not give me the correct output. I have since modified it but still not 100% where I want it to be.


I also could not find much amout the 'U' value you have set in the code. I assumed it was possible for Unix time but can't find anything in the documentation about it.


You don't perhaps have a updated solution?

If not, I'm sure I will find a way to do it somehow. haha

[ashuk38]

Absolutely Spot on, works a treat... Thanks Maximus your help has saved the last strands of hair i have left... Thanks its much appreciated..

[Maximus]

This should work:

$totalTime = (datetime\format(dateEnd, 'UTC', 'U') - datetime\format(dateStart, 'UTC', 'U')) / 60;
$hours = number\floor($totalTime / 60);
$minutes = ($totalTime / 60 - $hours) * 60;
totalDrivingTime = string\concatenate($hours, ' hour(s) ',$minutes, ' minute(s)')

[ashuk38]

Hi Maximus,

Your a star, many thanks for your help. Not sure what to tweak as its not quite there, as you look at the screenshot. It needs to read 1 hour 12 minutes in totalDrivingTime. Anyway i think where i was going wrong is that i had it as an integer field, since changed to varchar and seems to be light at the end of the tunnel. If you can help me with the final tweak that would be great.

Thank you so much for your help, it's much appreciated.

[Maximus]

Hi,
here is a sample of how you can count the hours and minutes in totalDrivingTime (totalDrivingTime should be a varchar type). Use this Formula:

$totalTime = (datetime\format(tripEndWK1, 'UTC', 'U') - datetime\format(tripStartWK1, 'UTC', 'U')) / 60;
$hours = $totalTime / 60;
$minutes = $totalTime % 60;
​​​​​​totalDrivingTime = string\concatenate($hours, ' hour(s) ',$minutes, ' minute(s)')

Note: I didn't test it.

[ashuk38]

Hi Guys

Added formula to entity as follows:-

totalDrivingTime = (datetime\format(tripEndWK1, 'UTC', 'U') - datetime\format(tripStartWK1, 'UTC', 'U')) / 60;

This gives me the total minutes successfully, however if this goes over an hour i would like to display Hours & Minutes as well as taking into account going over midnight. Can someone help me with what i need to add to this line?

Thanks guys

[etw5200]

yes i know the simple math, but i didnt know the code the way espocrm wants it written. Thnaks

[yuri]

This is a simple math.

floor(270 / 60) = 4 hours

(270 / 60 - floor(270 / 60)) * 60 = 30 minutes

$durationMinutes = (datetime\format(endTime, 'UTC', 'U') - datetime\format(startTime, 'UTC', 'U')) / 60;

$hours = number\floor($durationMinutes / 60);
$minutes = ($durationMinutes / 60 - $hours) * 60;

duration = string\concatenate($hours, ':', $minutes);

Note: I didn't test it.

[etw5200]

what i mean is that if 240 seconds / 60 = 4, then i need it to take 4 /60 again to give me decimal 0.66

[etw5200]

that works, but it only shows a whole number. so if the time is 7 minutes it shows as 7 not a decimal. the / 60 is not working.

[yuri]

Fixed:

totalTimeMinutes = (datetime\format(endTime, 'UTC', 'U') - datetime\format(startTime, 'UTC', 'U')) / 60;

[etw5200]

when i close a case, it just shows 0 in totalTimeMinutes when i apply the formula.

[yuri]

By utilizing formla:

totalTimeMinutes = (datetime\format(endTime, 'UTC', 'U') - datetime\format(startTime, 'UTC', 'U')) / 60;

Where totalTimeMinutes - Integer field;