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

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(at)alumni(dot)caltech(dot)edu
> South Pasadena, California
>
> ************