Category
Report Builder
TimeDiff MySQL function not showing negative values
Resolved
I have 2 time columns, but when trying to do a TimeDiff or SubTime function, if it returns a negative value, Yellowfin shows NULL.
E.g.
In MySQL Workbench.
In YF:
The same problem 
So the problem is is actually with the MySQL JDBC driver, and displaying - values next to a time data type.
When querying MySQL via the workbench, nothing needs to be translated to the driver, which is why you don't face this issue there.
As YF uses the JDBC driver to the connect, you will need to convert the data type from Time to something else. In this example, we will use Numeric as it makes the most sense.
1. Use TimeStampDiff and specify the value to be returned in Minutes.
This calc field was created at the view level using Freehand SQL. Though you could do this at the report level if you wanted to.
2. At the report, I pull in this calc field and format it as "Days, Hours, Mins, Seconds"
Now we have what you saw in MySQL Workbench.
...Though we can go a step further and make it a bit easier to read.
3. Lets use a report level calc field, and divide that result by 60, so we can get it to display in hours only.
Then we will add a suffix of hr/s .
You now have the report showing the - time that matches MySQL workbench, but since it's now a numeric value, you do further calculations, such as showing hours only, or seconds, and even doing AVG's using the YF aggregation options!
If you run into issues with any of the above, please let us know.
Regards,
David
So the problem is is actually with the MySQL JDBC driver, and displaying - values next to a time data type.
When querying MySQL via the workbench, nothing needs to be translated to the driver, which is why you don't face this issue there.
As YF uses the JDBC driver to the connect, you will need to convert the data type from Time to something else. In this example, we will use Numeric as it makes the most sense.
1. Use TimeStampDiff and specify the value to be returned in Minutes.
This calc field was created at the view level using Freehand SQL. Though you could do this at the report level if you wanted to.
2. At the report, I pull in this calc field and format it as "Days, Hours, Mins, Seconds"
Now we have what you saw in MySQL Workbench.
...Though we can go a step further and make it a bit easier to read.
3. Lets use a report level calc field, and divide that result by 60, so we can get it to display in hours only.
Then we will add a suffix of hr/s .
You now have the report showing the - time that matches MySQL workbench, but since it's now a numeric value, you do further calculations, such as showing hours only, or seconds, and even doing AVG's using the YF aggregation options!
If you run into issues with any of the above, please let us know.
Regards,
David
Replies have been locked on this page!