calculate time between two date fields

[etw5200]

i have in cases entity, two fields, start time and end time. how can i have espocrm tell me the amount of time between start time and end time?
Example: start time = 2:00AM, end time = 2:10AM, total time = 00:10

[yuri]

By utilizing formla:

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

Where totalTimeMinutes - Integer field;

[etw5200]

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

[yuri]

Fixed:

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

[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.

[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

[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]

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

[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

[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 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]

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]

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

[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

[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');

[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.