[DB-SIG] Date/Time question

[hme@informatik.uni-rostock.de]

You (M.-A. Lemburg) wrote:
> hme@informatik.uni-rostock.de wrote:
> > implementing my Ingres module, I run across the following
> > implementation aspects:  According to the DB API 2.0, there are methods
> > for building several time/date value combinations.  But the DATETIME
> > object seems to represent absolute values only.
> 
> DATETIME is a type object, it doesn't refer to an object holding
> date/time values. In fact, there can be different objects for
> absolute date/time and relative date/time values. DATETIME and the
> other type objects are only used to group the type information in
> the cursor's description field in categories... e.g. mxODBC
> usually maps many different type codes to one type object (that's
> why the DB API spec. says that type codes in the description
> field must only *compare* equal to one of the type objects -- it
> allows the interface designer to create type objects which
> compare equal to a class of type codes rather than just one
> value). See the DBAPITypeObject class in the implementation
> notes of the DB API 2.0 spec for an example.

Thanks for clarifying this.

> > Is this correct?  If so, has someone made a proposal for representing
> > time intervals.  Since Ingres supports arithmetic on absolute and
> > relative time, it would be nice to have both.
> 
> Time intervals are not a SQL standard yet, AFAIK. You can still
> support them in your interface, but they won't be portable
> between DBs -- then again: its hard to write DB independent
> code anyway..
>  
> > Most database systems, that support temporal data, have a notation of
> > `now' and `today', why these values not make default parameters? 
> 
> I'm not sure I understand what you mean here... why should all
> date/time columns default to now() or today() ?

If no values are supplied to input parameter of type DATETIME, e.g.
just using Date() as an input parameter.  When using `now' the dbms is
substituting transaction time, in contrast to building a default
paramter (e.g. using UNIX time()) at PREPARE time.

> > As
> > far as I know some Systems internally represent date/time as strings
> > (Informix, Ingres).  Since there's no string parameter to the date/time
> > methods, conversion takes place two times. 
> 
> Why ? You could implement the date/time constructors in such
> a way that also accepts string on input... note that the
> constructors are only meant for the input side of the interface

Yup, but if it's O.K. to overload the constructors, than it works.
So the API is more a at-least-thing?

> (and then mostly because some DBs don't provide column type
> information in their APIs). The output side is not defined and
> usually varies between DBs.

But the type_code should compare equal to one of the types defined by
the API?

Holger

-- 
Holger Meyer, Uni of Rostock, Dpt. of CS, DB Research Group
hm@guug.de, http://www.informatik.uni-rostock.de/~hme/