Loss of fractional time when writing TIME columns

[hostilefork]

via @gchiu:

ODBC doesn't support fractional time values in the TIME datatype (enforced in DB2)
but Firebird did.

[hostilefork]

Most important question from this: if we can't preserve a fractional time at all, should you get an error the way you would if you tried to store an out of range number? Should you have to round it yourself?

Anyway... from a "transferring values as bytes" standpoint, there's not much that can be done with this and the TIME type in ODBC. Because the structure you use just doesn't have fractional time:

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

The workaround would be to read and write the times textually. Someone on the Firebird bug tracker tried that--or at least tried the reading part. It executed, but gave the wrong fractions. Discussion on the bug suggests the issue was then fixed (in 2016's version of the ODBC driver, 2.0.4).

I'm marking it low priority, but it could be done. In the meantime, clients can do this themselves for servers that support it, by using SQL statements that do the stringification.

---

Note: While there's no space for fraction in TIME, there is space for it in the TIMESTAMP's transfer struct:

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

But in the cases I looked at for TIMESTAMP it seems to be thrown away. Perhaps one would have better luck with DATETIME which seems to be larger in some cases.