CODEWITHSUNDEEP
phppostgresqldatetimesql-insertcheck-constraints
user6138023
user6138023

Reputation:

Prevent insertion of overlapping timestamp ranges to database

Hi there I hope anyone can help me.

lets say that I have on my table the following

name text startTime timestamp without time zone endTime timestamp without time zone

here is what I am trying to do:

I am trying to check if the time that is about to be added to the database will overlap the time that is already on the table.

sample:

name            startTime               endTime
---------|------------------------|----------------------
tommy    |   2019-07-10 08:30:00  |   2019-07-10 10:30:00
tommy    |   2019-07-10 10:31:00  |   2019-07-10 11:30:00  
tommy    |   2019-07-10 07:30:00  |   2019-07-10 09:00:00 <=== if I click enter to enter this tird schedulle for this user, the sistem will give me a conflict message because this user already has a schedulle that start from 2019-07-10 08:30:00 to 2019-07-10 10:30:00 on our first row of the table.

As for my codes to insert on the database all I have is this simple php

$sql = "insert into horarios (name, startTime, endTime) values ('$name', '$startTime', '$endTime');";
$res = @pg_query ($con, $sql);
if ($res == NULL){
    echo "Query failed.";
    exit (0);
}

Upvotes: 1

Views: 209

Answers (1)

GMB
GMB

Reputation: 222642

Let the database do the heavy lifting! You can use timestamp ranges and an exclusion constraint to prevent insertion of overlapping date ranges:

alter table horarios
    add constraint horarios_range_overlap
    exclude using gist (tsrange(startTime, endTime) with &&)

The database uses the timestamps as bounds to build a tsrange, and then ensures that there is no overlap, using operator &&. Conflicting records are rejected.

By default, the timestamp range is inclusive on the lower bound and exclusive at the upper bound - but this can be modified.

Upvotes: 3

Related Questions