MySQL Error Invalid time(hours) format. Use interval types instead

[razmochaev]

Hi, everyone.

I've run into an error while trying to connect to the "work_time" table in our company's database:

DataSource.Error: ODBC: ERROR [22007] [MySQL][ODBC 8.0(w) Driver][mysqld-8.0.28-0ubuntu0.20.04.3]Invalid time(hours) format. Use interval types instead

Here's the screenshot of table's structure:

I didn't happen to find any workaround. Anyone had the same issue? 

[v-cazheng-msft]

Hi @razmochaev,

According to the error message, it should be something related to date type or date/time format. And it’s very likely that it’s work_date, time_from or time_to columns cause this issue. Set work_date as date data type and set time_from/time_to time data type should be proper. For the date/time types supported in Power BI, Please refer to this official doc Data types in Power BI Desktop - Power BI | Microsoft Docs.

Best Regards,

Community Support Team _ Caiyun

[razmochaev]

Hi, @v-cazheng-msft 

I guess date and time types are correct, according to DB settings:

I've found a bug report on that issue in MySQL community: https://bugs.mysql.com/bug.php?id=75925

The workaround is:

this problem is solved!! but you need to add (TIME_FORMAT)(,'%H:%i:%s') 

Below is the example this is in VBA

SQLStr = SQLStr & ", TIME_FORMAT(sec_to_time(sum(case when sub_status ='00:00:00' then pause_sec else 0 end)),'%H:%i:%s')

Maybe, I should try using native query for that case.