> > Thomas Lockhart would be the authority on this, but my impression is
> > that tinterval is deprecated and will eventually go away in favor of
> > the SQL-standard interval type. If you've found functions that exist
> > for tinterval and not for interval, then that's an item for the TODO
> > list --- please submit details.
> Perhaps I'm not picking up things from the documentation, but it appears to
> me that "interval" is only a time length, while "tinterval" is actually for
> specific times. To use a geometric analogy: interval is a length, while
> tinterval is a specific line segment.
> So it seems to me that interval is just way to generic (or rather,
> tinterval already supports things that I want to do, such as testing for
> overlaps).
TINTERVAL is a poorly supported, old and creaky data type. It is based
on ABSTIME, which is not as capable as TIMESTAMP.
> Am I missing something in the documentation that would explain to me how I
> could use a starttime/length combination (something like abstime/interval,
> or timestamp/interval) to check for overlaps like can be done with tinterval?
Maybe. The SQL9x function/operator OVERLAPS is recognized by PostgreSQL
7.x, and probably does what you want.
Of course, now that I'm testing it, something has broken with OVERLAPS
(in 7.0.3 and current sources). I've defined a function overlaps() which
takes four arguments of timestamp type. The parser recognizes the
OVERLAPS syntax, and converts that to a function call syntax. I've also
defined a few more functions in pg_proc.h in the "SQL language" to map
variations of arguments, say (timestamp,interval,timestamp,interval), to
the underlying single implementation. Pretty sure that I tested this
exhaustively (?). That mapping now fails (hmm, remind me to add this to
the regression tests) with a parser error.
Test cases would be:
select ('today', 'tomorrow') OVERLAPS ('yesterday', 'now');
and
select ('today', interval '1 day') OVERLAPS ('yesterday', interval '18
hours');
(the second one fails). Now that I look, this breakage was introduced in
March when "we" expunged operators allowed as identifiers (Tom Lane and
I have blood on our hands on this one ;) See gram.y around line 5409.
Suggestions?
- Thomas
