Find Date and Time difference between two different modules

KReporter
1

Hello,

I want date and

time difference between opportunities created date and call created date under opportunity. Does anyone know if it is possible?
Kindly let me know if any custom function for this.

2

you can evaluate that using formulas. Select the date from the opportunity in one field and the date from the call in another field and then evaluate the two calculate the difference. You can find more about using formulas here http://www.spicecrm.io/download/customizing-queries-in-kreporter/

3

Thank you for reply. I store both date values as “date1” and “date2” and create a formula {date1} - {date2} but i get only “0” value for all results. Kindly tell me what am i doing wrong?

4

the formula evaluates your statement as php code as it is stated in the document. So you ned to handle the dates as string and then convert them to date objects and do the math. You cannot just like that subtract two strings. Use function like date_diff to do that.

5

Thank you for reply.
I tried DateDiff function but both values are in different modules and tables. So i tried to apply join function in custom formula but still not get any result. I want difference between date with time.

6

Hello,
I used the join and it’s work this time. Thanks.

7

Hello,
Is there any way to see only first record in group view.
We have many calls under one opportunity and we want to see only first calls detail in report. Does anyone know this.

8

Maybe you can share the formula you used here … might be good for others that look for a similar solution. Thank you… :slight_smile:

9

Hello @spicecrm,

I applied the inner join conditions as per your custom query document.

Regard

10

Hello @spicecrm,

I am mentioning similar mysql query to find time difference between two fields from two different modules(cases and cases audit). Kindly check it.

SELECT TIMEDIFF(cases_audit.date_created, cases.date_entered)
FROM cases INNER JOIN cases_audit ON cases.id = cases_audit.parent_id WHERE cases_audit.date_created= {t}.{f}

Regard