Thread

Re: [HACKERS] date/time problem in v6.5.3 and 7.0.0 ...

Jose Soares <jose@sferacarta.com> — 2000-01-19T13:39:59Z

This table shows every legal operation between datetimes and intervals and
vice versa
allowed by SQL92:

-----------------------------------------
1st operand|operator|2nd operand|result
-----------+--------+-----------+--------
datetime   |   -    |datetime   |interval
datetime   |   +    |interval   |datetime
datetime   |   -    |interval   |datetime
interval   |   +    |datetime   |datetime
interval   |   +    |interval   |interval
interval   |   -    |interval   |interval
interval   |   *    |number     |interval
interval   |   /    |number     |interval
number     |   *    |interval   |interval
-----------+--------+-----------+--------

I wrote some pgPL/SQL functions to create operators between datetimes and
intervals.
Probably there's a better way to achieve that goal, but this works anyway.

José



Thomas Lockhart wrote:

> > > > > I can add days to now(), but not subtract?
> > > The problem is that it is meaningful to subtract two absolute times,
> > > giving a delta time as a result, *and* it is meaningful to subtract a
> > > delta time from an absolute time, giving another absolute time as a
> > > result.
> > It would be nice if we could decide '30 days' is a delta time because
> > it is not suitable for an absolute time representation. Would it be
> > hard?
>
> Hmm. I'm not sure how hard it would be. The places where types need to
> be matched to operators are fairly well isolated in the parser.
> However, we would need a new kind of input function which does not
> throw an elog(ERROR), but rather just returns failure if the input
> does not get decoded. Then, we could accumulate a list of successfully
> decoded types (based on our list of candidate operators), and if that
> list is of length one then we have a match.
>
> One way to implement this would be to define an additional input
> routine (which the existing input routine would use) which returns an
> error rather than throwing an elog() error. Then, our parser could use
> this additional routine to discriminate between the candidate types.
> The input routine could be found in a similar way to our existing
> "implicit coersion" code, which assumes a specific function name for
> each type.
>
> The downside to this is that we have built up one additional
> assumption about the form and contents of our system tables. But,
> since it adds functionality that probably isn't a bad thing.
>
> I do know I'm not likely to find time to work on it for the next
> release...
>
>                       - Thomas
>
> --
> Thomas Lockhart                         lockhart@alumni.caltech.edu
> South Pasadena, California
>
> ************