Thread: tinterval or interval
I'm working on a scheduler, and my first inclination was to use tinterval
(since I happen to have data available in YYYY-MM-DD HH:MM-HH:MM anyway).
After some trial and error, I've finally discovered the && overlap operator
(it's mentioned in the regression tests but not in the manual), which I
need.
However, I also need to know how long a tinterval is. The | is nice for
getting the start time in a tinterval, but I can't figure out how to get
the stop time, or an interval out of tinterval. Is there a way?
Barring that, how could I efficiently use a timestamp/interval pair to
prevent overlaps (better, overlaps are ok if a certain resource is shared).
It seems that tinterval is deprecated, but it comes very close to fitting my
needs.
mrc
--
Mike Castle Life is like a clock: You can work constantly
dalgoda@ix.netcom.com and be right all the time, or not work at all
www.netcom.com/~dalgoda/ and be right at least twice a day. -- mrc
We are all of us living in the shadow of Manhattan. -- Watchmen
On Fri, Nov 24, 2000 at 11:43:09AM -0600, Mike Castle wrote:
> However, I also need to know how long a tinterval is. The | is nice for
> getting the start time in a tinterval, but I can't figure out how to get
> the stop time, or an interval out of tinterval. Is there a way?
Ok... found the functions tinterval{rel,start,end}.
Are these "supported" functions? Should I feel safe in using them and they
won't go away in the future?
mrc
--
Mike Castle Life is like a clock: You can work constantly
dalgoda@ix.netcom.com and be right all the time, or not work at all
www.netcom.com/~dalgoda/ and be right at least twice a day. -- mrc
We are all of us living in the shadow of Manhattan. -- Watchmen
Mike Castle <dalgoda@ix.netcom.com> writes:
> Are these "supported" functions? Should I feel safe in using them and they
> won't go away in the future?
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.
regards, tom lane
On Fri, Nov 24, 2000 at 03:03:44PM -0500, Tom Lane wrote:
> Mike Castle <dalgoda@ix.netcom.com> writes:
> > Are these "supported" functions? Should I feel safe in using them and they
> > won't go away in the future?
>
> 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).
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?
mrc
--
Mike Castle Life is like a clock: You can work constantly
dalgoda@ix.netcom.com and be right all the time, or not work at all
www.netcom.com/~dalgoda/ and be right at least twice a day. -- mrc
We are all of us living in the shadow of Manhattan. -- Watchmen
On Fri, Nov 24, 2000 at 02:52:31PM -0600, Mike Castle wrote:
> 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?
Ok. Apparently it's time to invest in a good SQL book before proceeding.
I did a web search and found SQL specs online (well, not the final
versions, but probably close enough).
And I figured out how to use the overlaps operator:
test=# select ('2000-11-21 08:00'::timestamp, '2000-11-21 09:00'::timestamp)
overlaps ('2000-11-21 08:59'::timestamp, '2000-11-21 10:00'::timestamp);
overlaps
----------
t
(1 row)
Probably an easier way to write that, but in the mean time....
I have noticed a difference between how overlaps work compared to && for
tinterval. With tinterval [8:00-9:00] is considered to overlap with
[9:00-10:00], while with overlaps, they don't:
test=# select tinterval '["2000-11-21 08:00" "2000-11-21 09:00"]' &&
tinterval '["2000-11-21 09:00" "2000-11-21 10:00"]';
?column?
----------
t
(1 row)
test=# select ('2000-11-21 08:00'::timestamp, '2000-11-21 09:00'::timestamp)
overlaps ('2000-11-21 09:00'::timestamp, '2000-11-21 10:00'::timestamp);
overlaps
----------
f
(1 row)
Since I don't yet have a good book.... should that be considered to overlap
or not? My initial thoughts were that tinterval and timestamps/overlaps
would have the same semantics.
mrc
--
Mike Castle Life is like a clock: You can work constantly
dalgoda@ix.netcom.com and be right all the time, or not work at all
www.netcom.com/~dalgoda/ and be right at least twice a day. -- mrc
We are all of us living in the shadow of Manhattan. -- Watchmen