MySQL Bugs: #75925: ODBC 5.3(a) Driver Invalid time(hours) format

Bug #75925	ODBC 5.3(a) Driver Invalid time(hours) format
Submitted:	17 Feb 2015 8:14	Modified:	14 Mar 2015 22:34
Reporter:	Ameda NA	Email Updates:
Status:	Verified	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	5.3.4	OS:	Windows (7 64bit)
Assigned to:		CPU Architecture:	Any
Tags:	odbc invalid time format

Description:
MySQL ODBC Driver 5.3 Tree (GA) 2014-07-18 returns errors on basic sql date/time queries.

How to repeat:
SELECT (TIMEDIFF(NOW(), table1.entered) as expression1;

>ERROR [22007] [MySQL][ODBC 5.3(a) Driver][mysqld-5.7.5-m15-log]Invalid time(hours) format. Use interval types instead

Interfaced via New System.Data.Odbc.OdbcConnection (ANSI)

Suggested fix:
ODBC 5.2.7 (GA) returns h:m:s

23:38:54
00:02:54
00:06:23
00:08:16
15:32:31

Hello Ameda NA,

Thank you for the bug report.
I tried to reproduce the issue at my end using Visual Studio 2013 (C#.Net), MySQL Connector/ODBC 5.3.4 but couldn't trace out any issue in time format.
Could you please list out the steps you tried out at your end? Please provide repeatable test case in order for us to confirm this issue at our end.

Thanks,
Chiranjeevi.

Result time format screenshot

Attachment: 75925.PNG (image/png, text), 5.11 KiB.

replica database

Attachment: alpha.sql (application/octet-stream, text), 3.39 KiB.

SELECT t0.id, jobno, street1, street2, t0.idpostcode1, t0.idpostcode2, idcargo, idbusiness0, idbusiness1, idbusiness2, t0.entered, iddriver1, scheduled, t0.notes, t0.price, TIMEDIFF(NOW(), t0.entered)  AS Ex1, scheduled, items, contact, claim, `order`, rego, phone, sms FROM `sale-trackers` AS t0

New System.Data.Odbc.OdbcCommand > DRIVER={MySQL ODBC 5.3 ANSI Driver};

Hello Ameda NA,

Thank you for your feedback.
Verified this behavior on Visual Studio 2013 (C#.Net) and MySQL Connector/ODBC 5.3.4.

Thanks,
Chiranjeevi.

Driver behavior needs addressed in [5.3] and published.

Hello,

Is this bug already solved? In which version of the driver? We are using the ODBC driver in version 5.3.4 and I also tried with the version 5.3.8 but the behavior is the same...

Thank you,

Ben

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')