Invalid date when query returns a time from MySQL?

Get Help
1

Hi,

I have a SQL query running on a MySQL DB that returns a time value (result of time diff function), this works perfectly in MySQL workbench but running the same query in metabase results in invalid date

So as a test I run the below much simpler query to return a time from a datetime value, again this works in MySQL Workbench but Metabase returns invalid date ?

Is this a glitch in metabase? Is there a way around this?

select time(offloaded_time) from productiondata where offloaded_time is not null order by Offloaded_Time desc limit 1

2

It’s possibly a glitch - I’ve no experience with using time values in Metabase (just datetime so far). I think I saw some fixes regarding time, datetime and the date type hierarchy (which really isn’t a hierarchy).

Depending on your needs you may be able to work around this by converting your timediff to a suitable simple scalar type like seconds or something.

Did you forget to paste your SQL? You can still edit to post.

3

Sorry, now SQL above - as I mentioned this is greatly simplified just to demonstrate the problem, the actual code it is result of a timediff function.

I have worked around it using timestampdiff and returning number of seconds, its a bit of a cludge though so if there is a proper fix it would be good

4

This is also an issue I’m dealing with. Also, if the duration of the time is more than 24 hours, a javascript error is thrown of “java.sql.SQLException: Illegal hour value ‘72’ for java.sql.Time type in value '72:55:00.”

Built in abilities to handle time diff, and averaging durations would be AWESOME and could possibly address this issue.