Restricting Overlapping Time Periods in MySQL

I previously wrote about avoiding overlapping time periods in Postgres here: avaitla16.com/application-time-periods-in-p..

Postgres' tsmultirange types make restriction and update quite clean. MySQL doesn't have btree_gist constraints or range types so we need to use a more primitive approach to raise errors when someone attempts to insert or update an interval that might overlap.

First the table and initial records:

DROP TABLE IF EXISTS prices;
CREATE TABLE prices (
    id int AUTO_INCREMENT PRIMARY KEY,
    gas_station_id int,
    gas_price_in_cents int
        CONSTRAINT positive_price CHECK (gas_price_in_cents > 0),
    ts_start TIMESTAMP NOT NULL,
    ts_end TIMESTAMP NOT NULL
) ENGINE=InnoDB;


INSERT INTO prices (gas_station_id, gas_price_in_cents, ts_start, ts_end) VALUES
                   (1, 2000, '2000-01-01 00:00', '2000-06-01 00:00');

INSERT INTO prices (gas_station_id, gas_price_in_cents, ts_start, ts_end) VALUES
                   (1, 2000, '2000-06-01 00:00', '2000-12-01 00:00');

Consider the following interval that overlaps the first segment even though the primary id is the same:

INSERT INTO prices (gas_station_id, gas_price_in_cents, ts_start, ts_end) VALUES
                   (1, 2000, '2000-03-01 00:00', '2000-4-01 00:00');

We can follow the guidance here: stackoverflow.com/questions/325933/determin.. to determine if there are indeed overlaps:

Given an interval [StartA, EndA) and [StartB, EndB) the overlap check is (StartA <= EndB) and (EndA > StartB).

This check on inserts is expressed as:

-- Determine if there are overlaps on insert
SELECT * FROM prices
WHERE gas_station_id = 1
  AND ('2000-03-01 00:00' <= ts_end AND '2000-07-01 00:00' > ts_start);

id, gas_station_id, gas_price_in_cents, ts_start, ts_end
1,1,2000,2000-01-01 00:00:00,2000-06-01 00:00:00
2,1,2000,2000-06-01 00:00:00,2000-12-01 00:00:00

We can also check for updates of existing intervals as follows (we simply exclude the interval being update from overlap checking):

-- Determine if there are overlaps on update
SELECT * FROM prices
WHERE gas_station_id = 1 AND id != 1
  AND ('2000-03-01 00:00' <= ts_end AND '2000-06-01 00:00' > ts_start);

With these main criteria we can build triggers for UPDATE and INSERT.

The INSERT Trigger:

CREATE TRIGGER prices_insert_overlap
    BEFORE INSERT
    ON prices FOR EACH ROW
BEGIN
    DECLARE rowcount INT;

    SELECT COUNT(*) INTO rowcount FROM prices
    WHERE gas_station_id = NEW.gas_station_id
      AND (NEW.ts_start <= ts_end AND NEW.ts_end >= ts_start);

    IF rowcount > 0 THEN
        signal sqlstate '45000' set message_text = 'Interval Overlaps Existing Intervals';
    END IF;

END;

The UPDATE trigger:

CREATE TRIGGER prices_update_overlap
    BEFORE UPDATE
    ON prices FOR EACH ROW
BEGIN
    DECLARE rowcount INT;

    SELECT COUNT(*) INTO rowcount FROM prices
    WHERE gas_station_id = NEW.gas_station_id AND id != OLD.id
      AND (NEW.ts_start <= ts_end AND NEW.ts_end >= ts_start);

    IF rowcount > 0 THEN
        signal sqlstate '45000' set message_text = 'Interval Overlaps Existing Intervals';
    END IF;

END;

This can be replicated for other tables as well that need exclusion constraints.