Thread: ODBC (6.40.0006): SQL_TIME binary format (bug? feature?)

I have a table (PGSQL 6.5.0):

CREATE TABLE TimePeriods (       ID           SERIAL  PRIMARY KEY   ,       MarketID        INT  NOT NULL REFERENCES
Markets,       WeekDay         INT  NOT NULL   , /* 1-7, Sun-Mon */       TimeOpen        TIME  NOT NULL   ,
TimeClose      TIME  NOT NULL
 
);

I do (WinNT 4.0 sp 4, ODBC v. 6.40.0006)

select * from timeperiods

The TimeOpen and TimeClose are stored as 4 byte values in the database. But
the ODBC driver returns them as 8 byte values in a completely unexpected
format! The usual binary representation of an SQL_TIME is (high to low) 1
byte hours, 1 byte minutes, 1 byte seconds and the lowest byte 1/100s of a
second.

Here I get (low to high): int16 hours, int16 minutes, int16 seconds, and
int16 0xCDCD. The result - 1/100s are lost and a lot of wasted network
bandwidth. What is the point of formatting TIME like that? Why does it clip
fractions of a second? Is it a bug or a feature? If it's a bug, how can I
help fixing it? If it's a feature, why is it needed?

Gene Sokolov.

> I have a table (PGSQL 6.5.0):
> CREATE TABLE TimePeriods (
>         ID           SERIAL  PRIMARY KEY   ,
>         MarketID        INT  NOT NULL REFERENCES Markets ,
>         WeekDay         INT  NOT NULL   , /* 1-7, Sun-Mon */
>         TimeOpen        TIME  NOT NULL   ,
>         TimeClose       TIME  NOT NULL
> );
> The TimeOpen and TimeClose are stored as 4 byte values in the database.

?? The TIME type is stored internally as a double precision float
afaik.

> the ODBC driver returns them as 8 byte values in a completely unexpected
> format! The usual binary representation of an SQL_TIME is (high to low) 1
> byte hours, 1 byte minutes, 1 byte seconds and the lowest byte 1/100s of a
> second.

Usual where?

> Here I get (low to high): int16 hours, int16 minutes, int16 seconds, and
> int16 0xCDCD. The result - 1/100s are lost and a lot of wasted network
> bandwidth. What is the point of formatting TIME like that? Why does it clip
> fractions of a second? Is it a bug or a feature? If it's a bug, how can I
> help fixing it? If it's a feature, why is it needed?

I'm not sure where int16 is coming from. Is it the case that the ODBC
driver must return TIME fields in this format? You seem to think not,
so without looking at the code I have no explanation.
                        - Thomas

-- 
Thomas Lockhart                lockhart@alumni.caltech.edu
South Pasadena, California