TimeDiff MySQL function not showing negative values

David Registro shared this problem 7 years ago
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.


218e5c2757b99858f22804c2d47fce98


In YF:

704967a8ce7a7454f7bae7584687ae1e1cf6e2fddd4602cabdb0731c0a0e8e9a

Replies (1)

photo
1

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.

134a0769066cadfa024818c6f4735d5b


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.

be7b7045b35e95762dab40a91aa76684


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

6714aa7a3b49b2c579b53984be7a46d5


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

Leave a Comment
 
Attach a file