Re: IDL modifications request

[Vivien Malerba <malerba gears linuxave net>]

On Sun, Sep 24, 2000 at 11:22:44AM +0100, Nick Gorham wrote:
> 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
> 

Under Postgres there are two kinds of intervals: the one I just mentionned
above, and the one you are mentionning which is just the time difference
between two dates. In the first case the dates are given (and the new type
would be nice for this) and in the second case, just the time separating the 
dates is given (what you are talking about).

For what you are talking, under postgres, it is represented as a timestamp,
and I don't have any idea of how to restrict it under ODBC. But I agree with
you that it should be taken care of.

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


Cheers,

Vivien