rethink database design on duration_hours & duration_minutes in database

[f1-outsourcing]

Issue

59 seconds are being truncated from calls, actually all seconds are.

Expected Behavior

just one field duration with seconds or interval

Actual Behavior

59 sec of calls are just dropped!

Possible Fix

just one field duration with seconds or interval

Context

3rd party plugins/modules have to modify the database to fix this. But this is just a super weird design choice, because someone in the past was not able to extract the required data properly via sql. (Mostly weird table design choices are because of lack of knowledge on sql or coding)

Your Environment

suitecrm7 and suitecrm8 have this.

[pgorod]

How are you entering these values, is it manually? You time your calls and want to register that down to the seconds, is that it?

[f1-outsourcing]

I have this module that reads the calls from asterisk. The average call length seems to be 4 minutes, so cutting a maximum of 59 seconds would make your analysis of calling hours of someone up to 25% incorrect.

[pgorod]

Ok, so the original design choice seems to be made for people entering the call durations manually, where seconds are overkill, and minutes is quite reasonable.

Having a 3rd party module with a different logic and different assumptions makes it convenient to add seconds; so that is a customization, the 3rd party module can do it.

I don't disagree with you in principle, it would have been better database design to do this in the beginning with a better data type internally (even if the form only had minutes). But not I don't see that it is feasible to change this for such a small reason. There are loads of backward-compatibility issues to worry about, unfortunately.

[f1-outsourcing]

@pgorod

What about this:
Luckily they created duration_hours and duration_minutes. What about introducing a field duration and when duration is modified a trigger automatically sets values in duration_hours and duration_minutes. This way newer code can just use the duration field while older code is getting still good data from duration_hours and duration_minutes.

After a few years you can then drop the duration_hours and duration_minutes fields.