Re: IDL modifications request

[Nick Gorham <nick lurcher org>]

Vivien wrote:

> Hi!
>
> I'm in the process of updating the Postgres provider, and I think we should
> modify the IDL for date/times data types:
> 1) The struct DbTime would need an information about the timezone (if the
> information is given)
> 2) The same for struct DbTimestamp.
> 3) A new type would be nice: to store intervals: from a date/time to another.

INTERVALS would be useful for ODBC as well, but not quite in that format,
SQL92's intervals come in two types YEAR-MONTH and DAY-SECOND's , so

INTERVAL '10-5' YEAR TO MONTH would indicate a interval of ten years and 5
months, and INTERVAL '10 5:12' DAY TO MINUTE would indicate ten days 5 hours and
12 minutes. the YEAR MONTH and DAY SECONDS types are distinct and cannot be
mixed. Just having a interval as two timestamps would make it diffucult (though
not impossible ) to encode these types. ODBC uses the following structure to
store them

typedef enum
{
    SQL_IS_YEAR                     = 1,
    SQL_IS_MONTH                    = 2,
    SQL_IS_DAY                      = 3,
    SQL_IS_HOUR                     = 4,
    SQL_IS_MINUTE                   = 5,
    SQL_IS_SECOND                   = 6,
    SQL_IS_YEAR_TO_MONTH            = 7,
    SQL_IS_DAY_TO_HOUR              = 8,
    SQL_IS_DAY_TO_MINUTE            = 9,
    SQL_IS_DAY_TO_SECOND            = 10,
    SQL_IS_HOUR_TO_MINUTE           = 11,
    SQL_IS_HOUR_TO_SECOND           = 12,
    SQL_IS_MINUTE_TO_SECOND         = 13
} SQLINTERVAL;

typedef struct tagSQL_YEAR_MONTH
{
        SQLUINTEGER     year;
        SQLUINTEGER     month;
} SQL_YEAR_MONTH_STRUCT;

typedef struct tagSQL_DAY_SECOND
{
        SQLUINTEGER     day;
        SQLUINTEGER     hour;
        SQLUINTEGER     minute;
        SQLUINTEGER     second;
        SQLUINTEGER     fraction;
} SQL_DAY_SECOND_STRUCT;

typedef struct tagSQL_INTERVAL_STRUCT
{
    SQLINTERVAL     interval_type;
    SQLSMALLINT     interval_sign;
    union {
        SQL_YEAR_MONTH_STRUCT       year_month;
        SQL_DAY_SECOND_STRUCT       day_second;
    } intval;

} SQL_INTERVAL_STRUCT;

Bit of a mess I know, but they are a complex type to represent in any other way.

--
Nick Gorham
"I wish there was a knob on the TV to turn up the intelligence,
There's a knob called brightness, but it doesn't work."
- Eugene P. Gallagher