CODEWITHSUNDEEP
phpmysqlvalidationdatedatetime
Vedran B
Vedran B

Reputation: 33

mysql - INSERT date range into date columns IF dates don't overlap with existing ones

I have the following table structure:

Table name: avail

id (autoincremetn) | acc_id | start_date | end_date
-------------------------------------------------------
1                  | 175    | 2015-05-26 | 2015-05-31 |
-------------------------------------------------------
2                  | 175    | 2015-07-01 | 2015-07-07 |
-------------------------------------------------------

It's used for defining date range availability eg. all dates in between start_date and end_date are unavailable for the given acc_id.

Based on user input I'm closing different ranges but I would like to throw an error IF an user tries to close (submit) a range that has it's start OR end_date somewhere in the range of an already existing one (for the submitted acc_id) in the DB. In this example a start_date: 2015-05-30 end_date: 2015-06-04 would be a good fail candidate.

I've found this QA: MySQL overlapping dates, none conflicting

that pretty much explains how to do it in 2 steps, 2 queries with some PHP logic in between.

But I was wondering if it can be done in one insert statement. I would eventually check for rows affected for success or fail (sub question: is there a more convenient way to check if it failed for some other reason besides date overlap?)

EDIT:

In response to Petr's comment I'll specify further the validation:

any kind of overlapping should be avoided, even the one embracing the whole range or finding itself inside the existing range. Also, if start or end dates equal the existing start or end dates it must be considered an overlap. Sometimes certain acc_id will already have more than one rang in the table so the validation should be done against all entries with a given acc_id.

Upvotes: 2

Views: 2344

Answers (1)

w3re
w3re

Reputation: 582

Sadly, using just MySQL this is impossible. Or at least, practically. The preferred way would be using SQL CHECK constraints, these are in the SQL language standard. However, MySQL does not support them.

See: https://dev.mysql.com/doc/refman/5.7/en/create-table.html

The CHECK clause is parsed but ignored by all storage engines.

It seems PostgreSQL does support CHECK constraints on tables, but I'm not sure how viable it is for you to switch database engine or if that's even worth the trouble just to use that feature.

In MySQL a trigger could be used to solve this problem, which would check for overlapping rows before the insert/update occurs and throw an error using the SIGNAL statement. (See: https://dev.mysql.com/doc/refman/5.7/en/signal.html) However, to use this solution you'd have to use an up-to-date MySQL version.

Apart from pure SQL solutions, this typically is done in application logic, so whichever program is accessing the MySQL database typically checks for these kind of constraints by requesting every row that is violated by the new entry in a SELECT COUNT(id) ... statement. If the returned count is larger than 0 it simply doesn't to the insert/update.

Upvotes: -1

Related Questions