Bug #110466 ODBC Connector does not support fraction of time
Submitted: 22 Mar 2023 13:07 Modified: 22 Mar 2023 15:15
Reporter: Markus Kollind Email Updates:
Status: Not a Bug Impact on me:
None 
Category:Connector / ODBC Severity:S4 (Feature request)
Version:8.0.32 OS:Any
Assigned to: CPU Architecture:Any

[22 Mar 2023 13:07] Markus Kollind
Description:
The MySQL ODBC driver does not return the fractions of a second when fetching time values with fractions from MySQL. 

How to repeat:
Create a table with a time value with fractions, e.g.:
create table time_test (t time, tfrac time(4));
insert into time_test(t, tfrac) values ('1:2:3.123','1:2:3.123');

Install the MySQL ODBC driver. 
Read the created table through an ODBC connection (e.g., Excel, powershell). 

Doing: 
     select * from time_test
will result in: 
     t          tfrac
     -          -----
     01:02:03   01:02:03
[22 Mar 2023 14:40] MySQL Verification Team
Hi Mr. Kollind,

Thank you for your feature request.

However, it is not a bug in our C/ODBC. Your SQL is wrong. It should be like this:

CREATE TABLE fractest( c1 TIME(2) );
INSERT INTO fractest VALUES ('17:51:04.777);

TIME domain that does not include a number in parentheses, does not support fractions.

Not a bug.
[22 Mar 2023 15:15] Markus Kollind
The example (slightly stupidly) contains two columns: one without fractions and one with fractions. 

Both return the same value when using the ODBC driver. 

For the provided example, an ODBC call still just returns this: 
   c1
   --
   17:51:04
and do not contain any fractions.
[27 Mar 2023 7:35] Bogdan Degtyariov
Markus,

Sinisa is right, the TIME structure for ODBC does not have fractions:

typedef struct tagTIME_STRUCT
{
        SQLUSMALLINT   hour;
        SQLUSMALLINT   minute;
        SQLUSMALLINT   second;
} TIME_STRUCT;

The timestamp does:

typedef struct tagTIMESTAMP_STRUCT
{
        SQLSMALLINT    year;
        SQLUSMALLINT   month;
        SQLUSMALLINT   day;
        SQLUSMALLINT   hour;
        SQLUSMALLINT   minute;
        SQLUSMALLINT   second;
        SQLUINTEGER    fraction;
} TIMESTAMP_STRUCT;

You need to create TIMESTAMP(2) column and most importantly make sure the client binds the data buffer as SQL_C_TIMESTAMP.