I’m a bit confused by this conversation. Open- and closed-ended ranges behave
as I would expect.
select tsrange('2016-01-10 17:00', '2016-01-11 17:00', '[)') &&
tsrange('2016-01-11 17:00', '2016-01-12 17:00', '[]')
false
select tsrange('2016-01-10 17:00', '2016-01-11 17:00', '[]') &&
tsrange('2016-01-11 17:00', '2016-01-12 17:00', '[]')
true
On Oct 15, 2021, 11:27 -0700, Brian Dunavant <[email protected]>, wrote:
> Think of it this way. When someone says they have a meeting from 1-2 and
> another from 2-3, do those meetings overlap? They do not, because we're
> actually saying the first meeting is from 1:00 through 1:59:59.99999. The
> Postgres date ranges are the same way. The starting point is inclusive, but
> the ending time is exclusive. So [1:00,2:00), and [2:00,3:00), do not
> overlap.
>
> > On Fri, Oct 15, 2021 at 2:16 PM Ron <[email protected]> wrote:
> > > On 10/15/21 8:59 AM, Adrian Klaver wrote:
> > > > On 10/15/21 06:52, Ron wrote:
> > > >> On 10/14/21 7:02 PM, Adrian Klaver wrote:
> > > >> [snip]
> > > >>> or the third example in the docs:
> > > >>>
> > > >>> SELECT (DATE '2001-02-16', DATE '2001-12-21') OVERLAPS
> > > >>> (DATE '2001-10-30', DATE '2002-10-30');
> > > >>> Result: true
> > > >>> SELECT (DATE '2001-02-16', INTERVAL '100 days') OVERLAPS
> > > >>> (DATE '2001-10-30', DATE '2002-10-30');
> > > >>> Result: false
> > > >>> SELECT (DATE '2001-10-29', DATE '2001-10-30') OVERLAPS
> > > >>> (DATE '2001-10-30', DATE '2001-10-31');
> > > >>> Result: false
> > > >>
> > > >> Why /don't/ they overlap, given that they share a common date?
> > > >
> > > > Per the docs:
> > > >
> > > > https://www.postgresql.org/docs/current/functions-datetime.html
> > > >
> > > > " Each time period is considered to represent the half-open interval
> > > > start
> > > > <= time < end, unless start and end are equal in which case it
> > > > represents
> > > > that single time instant."
> > > >
> > > > Which I read as
> > > >
> > > > (DATE '2001-10-29', DATE '2001-10-30') ends at '2001-10-29'
> > > >
> > > > and
> > > >
> > > > (DATE '2001-10-30', DATE '2001-10-31') starts at DATE '2001-10-30'
> > > >
> > > > so no overlap.
> > >
> > > I was afraid you were going to say that. It's completely bizarre, but
> > > seems
> > > to be a "thing" in computer science.
> > >
> > > --
> > > Angular momentum makes the world go 'round.
> > >
> > >
