How To: Creating a calculated field to work out number of minutes between two datetime fields in Sugar

  • Time to read 1 minute
Sugar Tips and Tricks

SugarCRM out of the box is able to fairly comfortably support the time between two hours in terms of service desk operations, however, we recently had a client that charges down to the minute, and as such needed to come up with a solution in order to satisfy these needs. It goes without saying that it could be achieved with custom logic, however, there was an urgency that dictated it would not be possible to wait. Therefore, we had to try and achieve it with Sugar's own logic.

A quick couple of internet searches brought up something close to what was needed but there wasn't anything bullet-proof so I stuck my thinking cap on and got to work.

To begin with, I laid the groundwork by using the hoursUntil function, and dividing that by 60 for the number of minutes, but obviously the way that time works, I was coming up with numbers such as 0.67 for 40 minutes, and so on. Though this is fine (and actually how we calculate time spent on Cases) it doesn't do much for onward use in terms of deductions from a Contract, for example.

With that in mind, I decided that there would need to be something a little more intricate...though I'll admit I didn't realise quite how intricate this would end up.

It occurred to me that we can probably make these datetime fields into numbers as our end goal is to have a number anyway so it's then about finding the best way of doing it.

Step forwards 'timestamp' - told you it was intricate!

What timestamp does is convert the datetime into a unix timestamp...in essence the number of seconds since 01/01/1970 (UTC)...for example my date of birth (and time of birth) is 697666020 - it's up to the reader to go check out when I was born!

Once we've got our numbers, we want to deduct the timestamp of our end date from our start date subtract(timestamp($start_time)),timestamp($end_time) but this bears no fruit as we must tell Sugar that these are numbers we're dealing with: subtract(number(timestamp($start_time)),number(timestamp($end_time)))

However this obviously only then gives us the number of seconds between our two figures, so we must go a little further...divide(subtract(number(timestamp($start_time)),number(timestamp($end_time)),60)

Now every result is not going to perfectly align with multiples of 60 so as a final element we need to get it down to a whole number.

I've managed to keep you on the page for long enough so here's what you came for:

abs(divide(subtract(number(timestamp($start_time)),number(timestamp($end_time))),60))