This table shows every legal operation between datetimes and intervals and vice versaallowed 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
************