How to Ensure Entries with Non-Overlapping Time Ranges

How to ensure entries with non-overlapping time ranges?

You were on the right track. But the syntax for exclusion constraints is slightly different.

Depending on the undisclosed table definition, you may need to install the extension (additional module) btree_gist first. Once per db. It's needed for this solution to provide the required operator class for type integer:

CREATE EXTENSION btree_gist;

See:

  • PostgreSQL EXCLUDE USING error: Data type integer has no default operator class
  • How to use (install) dblink in PostgreSQL?

Then:

CREATE TABLE registration  (
  tbl_id integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY
, col_a  integer NOT NULL
, col_b  integer NOT NULL
, valid_from timestamp
, valid_to   timestamp
, CONSTRAINT no_overlap
    EXCLUDE USING gist (col_a with =, col_b with =, tsrange(valid_from, valid_to) WITH &&)
);

Each column needs to be listed with its respective operator.

And you need a range type. You mention separate columns valid_from and valid_to. And you also mention tsrange and valid in the failed command. That's confusing. Assuming two timestamp columns, an expression index with the expression tsrange(valid_from, valid_to) would do it.

Related:

  • Perform this hours of operation query in PostgreSQL
  • Non-overlap, continuous timestamp ranges (tstzrange) for opening hours
  • Postgresql 9.4 query gets progressively slower when joining TSTZRANGE with &&
  • Store the day of the week and time?

Typically, timestamptz (tstzrange) should be chosen over timestamp (tsrange). See:

  • Ignoring time zones altogether in Rails and PostgreSQL
  • Don't use timestamp (without time zone)

Maybe, a superior design would be a one-to-many relationship between your registration table and 1-N entries in a new registration_range table. And some logic to determine the currently valid entry (for any given point in time). Depends on more undisclosed information.

How to record non-overlapping ranges (or time intervals) in R data.table way?

Borrowing the idea from David Aurenburg's solution here

DT[, g := c(0L, cumsum(shift(starts, -1L) > cummax(ends))[-.N]), IDs][,
    .(min(starts), max(ends)), .(g, IDs)]

output:

   g IDs V1 V2
1: 0 ID1  7 11
2: 1 ID1 13 18
3: 0 ID2  1  5
4: 1 ID2 17 19
5: 0 ID3  3  6
6: 1 ID3 13 21

Select date ranges where periods do not overlap

Thanks to @zip and @Gordon for their answers. Both were superior to my initial approach. However, the following solution was faster than both of their approaches in my environment & context:

WITH acceptable_starts AS (
    SELECT [start_date] FROM table1 AS a
    WHERE NOT EXISTS (
        SELECT 1 FROM table2 AS b
        WHERE DATEADD(DAY, 1, a.[end_date]) BETWEEN b.[start_date] AND b.
    UNION ALL
    SELECT DATEADD(DAY, 1, [end_date]) FROM table2 AS a
    WHERE NOT EXISTS (
        SELECT 1 FROM table2 AS b
        WHERE DATEADD(DAY, 1, a.[end_date]) BETWEEN b.[start_date] AND b.[end_date]
    )
),
acceptable_ends AS (
    SELECT [end_date] FROM table1 AS a
    WHERE NOT EXISTS (
        SELECT 1 FROM table2 AS b
        WHERE DATEADD(DAY, -1, a.[start_date]) BETWEEN b.[start_date] AND b.[end_date]
    )
    UNION ALL
    SELECT DATEADD(DAY, -1, [start_date]) FROM table2 AS a
    WHERE NOT EXISTS (
        SELECT 1 FROM table2 AS b
        WHERE DATEADD(DAY, -1, a.[start_date]) BETWEEN b.[start_date] AND b.[end_date]
    )
)
SELECT s.[start_date], MIN(e.[end_date]) AS [end_date]
FROM acceptable_starts
INNER JOIN acceptable_ends
ON s.[start_date] < e.[end_date]

Determine Whether Two Date Ranges Overlap

(StartA <= EndB) and (EndA >= StartB)

Proof:

Let ConditionA Mean that DateRange A Completely After DateRange B

_                        |---- DateRange A ------|
|---Date Range B -----|                          _

(True if StartA > EndB)

Let ConditionB Mean that DateRange A is Completely Before DateRange B

|---- DateRange A -----|                        _ 
_                          |---Date Range B ----|

(True if EndA < StartB)

Then Overlap exists if Neither A Nor B is true -

(If one range is neither completely after the other,

nor completely before the other,
then they must overlap.)

Now one of De Morgan's laws says that:

Not (A Or B) <=> Not A And Not B

Which translates to: (StartA <= EndB) and (EndA >= StartB)

NOTE: This includes conditions where the edges overlap exactly. If you wish to exclude that,

change the >= operators to >, and <= to <

NOTE2. Thanks to @Baodad, see this blog, the actual overlap is least of:

{ endA-startA, endA - startB, endB-startA, endB - startB }

(StartA <= EndB) and (EndA >= StartB)
(StartA <= EndB) and (StartB <= EndA)

NOTE3. Thanks to @tomosius, a shorter version reads:

DateRangesOverlap = max(start1, start2) < min(end1, end2)

This is actually a syntactical shortcut for what is a longer implementation, which includes extra checks to verify that the start dates are on or before the endDates. Deriving this from above:

If start and end dates can be out of order, i.e., if it is possible that startA > endA or startB > endB, then you also have to check that they are in order, so that means you have to add two additional validity rules:

(StartA <= EndB) and (StartB <= EndA) and (StartA <= EndA) and (StartB <= EndB)
or:

(StartA <= EndB) and (StartA <= EndA) and (StartB <= EndA) and (StartB <= EndB)
or,

(StartA <= Min(EndA, EndB) and (StartB <= Min(EndA, EndB))
or:

(Max(StartA, StartB) <= Min(EndA, EndB)

But to implement Min() and Max(), you have to code, (using C ternary for terseness),:

((StartA > StartB) ? StartA : StartB) <= ((EndA < EndB) ? EndA : EndB)

Non-overlap, continuous timestamp ranges (tstzrange) for opening hours

The answer to 1. is clear. To make sure there is no overlap use an exclusion constraint:

CREATE TABLE operating_period (
  id serial PRIMARY KEY                -- PK is NOT NULL automatically
, during tstzrange NOT NULL
, EXCLUDE USING gist (during WITH &&)  -- no overlap
);

This is implemented with a GiST index on during, that supports many types of queries automatically. See:

  • Preventing adjacent/overlapping entries with EXCLUDE in PostgreSQL
  • Perform this hours of operation query in PostgreSQL

Answers to 2. and 3. are not as clear because those really depends on a lot of things. For opening hours I would most likely go with range types in current versions of Postgres. I would also enforce [) bounds for all entries to keep things simple. Details in the first linked answer.

If you should go with (start_at, end_at), you'll be interested in the OVERLAPS operator:

  • Getting results between two dates in PostgreSQL
  • Find overlapping date ranges in PostgreSQL

Either way, the guideline on SO is to ask one question per question, not a whole list ...

MS-SQL - Select Non-overlapping integer ranges

I think NOT EXISTS can simplify your query:

SELECT *
FROM @Test a
WHERE NOT EXISTS(
    SELECT 1
    FROM @Test b
    WHERE a.TimeStart <= b.TimeEnd AND a.TimeEnd >= b.TimeStart 
        AND a.TimeStart >= b.TimeStart
        AND a.TimeID <> b.TimeID
)

Solution based on window function:

WITH cte AS(
    SELECT *,
        MaxEnd = MAX(TimeEnd) OVER (ORDER BY TimeStart ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
    FROM @Test
)
SELECT * 
FROM cte 
WHERE MaxEnd IS NULL OR TimeStart > MaxEnd

Determine whether the time range of one table entry overlaps with another KDB+/Q

A bit of a long-winded solution here but I think this covers your use case:

q)raze{update overlap:{any(x within'z)|y within'z}'[startRange;endRange]{x where y<>til count x}'[;i]count[i]#enlist flip(startRange;endRange)from x}each{select from table where RIC=x}each`A.N`GOOG.O
RIC    startRange                    endRange                      overlap
--------------------------------------------------------------------------
A.N    2022.01.03D09:31:54.000000000 2022.01.03D09:31:59.000000000 1
A.N    2022.01.03D09:32:04.000000000 2022.01.03D09:32:09.000000000 0
A.N    2022.01.03D09:31:54.100000000 2022.01.03D09:31:59.100000000 1
GOOG.O 2022.01.03D09:31:54.000000000 2022.01.03D09:31:59.000000000 1
GOOG.O 2022.01.03D09:31:54.100000000 2022.01.03D09:31:59.100000000 1

To break this answer down, firstly we needed the time ranges to check for an overlap. We start with all the time ranges for a given RIC:

q)`overlap xcols raze{update overlap:count[i]#enlist flip(startRange;endRange)from x}each{select from table where RIC=x}each`A.N`GOOG.O
overlap                                                                                                                                                                          ..
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------..
(2022.01.03D09:31:54.000000000 2022.01.03D09:31:59.000000000;2022.01.03D09:32:04.000000000 2022.01.03D09:32:09.000000000;2022.01.03D09:31:54.100000000 2022.01.03D09:31:59.100000..
(2022.01.03D09:31:54.000000000 2022.01.03D09:31:59.000000000;2022.01.03D09:32:04.000000000 2022.01.03D09:32:09.000000000;2022.01.03D09:31:54.100000000 2022.01.03D09:31:59.100000..
(2022.01.03D09:31:54.000000000 2022.01.03D09:31:59.000000000;2022.01.03D09:32:04.000000000 2022.01.03D09:32:09.000000000;2022.01.03D09:31:54.100000000 2022.01.03D09:31:59.100000..
(2022.01.03D09:31:54.000000000 2022.01.03D09:31:59.000000000;2022.01.03D09:31:54.100000000 2022.01.03D09:31:59.100000000)                                                        ..
(2022.01.03D09:31:54.000000000 2022.01.03D09:31:59.000000000;2022.01.03D09:31:54.100000000 2022.01.03D09:31:59.100000000)                                                        ..

We want to exclude the time range for the entry we're working from:

q)`overlap xcols raze{update overlap:{x where y<>til count x}'[;i]count[i]#enlist flip(startRange;endRange)from x}each{select from table where RIC=x}each`A.N`GOOG.O
overlap                                                                                                                   RIC    startRange                    endRange          ..
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------..
(2022.01.03D09:32:04.000000000 2022.01.03D09:32:09.000000000;2022.01.03D09:31:54.100000000 2022.01.03D09:31:59.100000000) A.N    2022.01.03D09:31:54.000000000 2022.01.03D09:31:5..
(2022.01.03D09:31:54.000000000 2022.01.03D09:31:59.000000000;2022.01.03D09:31:54.100000000 2022.01.03D09:31:59.100000000) A.N    2022.01.03D09:32:04.000000000 2022.01.03D09:32:0..
(2022.01.03D09:31:54.000000000 2022.01.03D09:31:59.000000000;2022.01.03D09:32:04.000000000 2022.01.03D09:32:09.000000000) A.N    2022.01.03D09:31:54.100000000 2022.01.03D09:31:5..
,2022.01.03D09:31:54.100000000 2022.01.03D09:31:59.100000000                                                              GOOG.O 2022.01.03D09:31:54.000000000 2022.01.03D09:31:5..
,2022.01.03D09:31:54.000000000 2022.01.03D09:31:59.000000000                                                              GOOG.O 2022.01.03D09:31:54.100000000 2022.01.03D09:31:5..

And finally see if startRange or endRange are within these time ranges:

q)`overlap xcols raze{update overlap:{any(x within'z)|y within'z}'[startRange;endRange]{x where y<>til count x}'[;i]count[i]#enlist flip(startRange;endRange)from x}each{select from table where RIC=x}each`A.N`GOOG.O
overlap RIC    startRange                    endRange
--------------------------------------------------------------------------
1       A.N    2022.01.03D09:31:54.000000000 2022.01.03D09:31:59.000000000
0       A.N    2022.01.03D09:32:04.000000000 2022.01.03D09:32:09.000000000
1       A.N    2022.01.03D09:31:54.100000000 2022.01.03D09:31:59.100000000
1       GOOG.O 2022.01.03D09:31:54.000000000 2022.01.03D09:31:59.000000000
1       GOOG.O 2022.01.03D09:31:54.100000000 2022.01.03D09:31:59.100000000

EDIT:

A faster solution

q)raze{update overlap:{$[any x within'z;1b;any y within'z]}'[startRange;endRange]{x where y<>til count x}'[;i]count[i]#enlist flip(startRange;endRange)from x}each{select from table where RIC=x}each`A.N`GOOG.O

Related Topics

Multiple Webrequest in Same Session

How to Get the Value of a Session Variable Inside a Static Method

ASP.NET MVC 3: Defaultmodelbinder with Inheritance/Polymorphism

Where Is Mono.Unix.Native

Windows Form Application Exception

File Exists by File Name Pattern

Show Data in ASP.NET HTML Table

Xamarin Alarmmanager Android

Exception from Hresult: 0X80040013: When Integrating R with C#:

C# Datetime.Ticks Equivalent in Java

How to Prevent Xss (Cross Site Scripting) Whilst Allowing HTML Input

Capturing Count from an SQL Query

Creating Simple C++.Net Wrapper. Step-By-Step

Entity Framework the Underlying Provider Failed on Open

How to Consume a Blazor Component as a Web Component Within a Regular Non-Blazor HTML Page

Block Request for Multiple Unsuccessful Logins for a Period of Time

Open Source HTML to PDF Renderer with Full CSS Support

C# SQLparameters Short Hand


Leave a reply



Submit