check dates on database

Question

On my database table I have 2 columns, start_date and end_date.

Sample data would be:

-------------------------------
start_date     |     end_date
-------------------------------
2017-11-01           2017-11-02
2017-11-03           2017-11-07
2017-11-20           2017-11-28
2017-11-13           2017-12-02
-------------------------------

I need to find if there are 5 consecutive days that are not yet used, which in this case, there is:

(2017-11-08 to 2017-11-13)

I'm using PHP and MySQL.

Thanks in advance!

Possible duplicate of [Difference between two dates in MySQL](https://stackoverflow.com/questions/4759248/difference-between-two-dates-in-mysql) — Michel, Nov 03 '17 at 12:31
@Michel, I'd argue this is not a duplicate, as while it may use that function is the solution, the question is different — kchason, Nov 03 '17 at 12:33
i've tried one similar to Michel's suggestion but really stucked on how to find the dates not used. — Lhen, Nov 03 '17 at 12:36
Are you trying to verify that the specified date range works, or that there is a range somewhere that is 5 days, and what it is? — kchason, Nov 03 '17 at 12:40
Do you need to check the consecutive dates for both the columns? — Akshit Arora, Nov 03 '17 at 12:46
@kchason trying to find consecutive days that are not occupied/used between the dates. — Lhen, Nov 03 '17 at 12:48
ex. nov 1-30, trying to find if there are 5 consecutive days open based on the start and end date — Lhen, Nov 03 '17 at 12:49
@Strawberry, we might need OP to weigh in, but I would assume given that the last date in the table is 2017-12-02, it'd have to be before that, otherwise, we could say any point in 2018 is fair game, no? — kchason, Nov 03 '17 at 13:15
@kchason i think that's a fair assessment, and likewise any point prior to this week. — Strawberry, Nov 03 '17 at 13:21
@badboy If it ain't in the question, it ain't in the question. — Strawberry, Nov 03 '17 at 13:22
Though, to @Strawberry's point, if your first date was the 6th of November, my answer wouldn't identify Nov 1-5 as an acceptable range. It only looks within the range given. — kchason, Nov 03 '17 at 13:25

kchason · Accepted Answer · 2017-11-03T13:12:18.833

You'd need to check for edge cases depending on your actual data and if there were no overlap dates, but this is a good start for the provided data.

Assuming table and data as defined as below:

CREATE TABLE
  `appointments`
 (
     `appointment_id` INT PRIMARY KEY AUTO_INCREMENT,
     `start_date` DATE,
     `end_date` DATE
 );

INSERT INTO
     `appointments`
 (`start_date`, `end_date`)
 VALUES
 ('2017-11-01', '2017-11-02'),
 ('2017-11-03', '2017-11-07'),
 ('2017-11-20', '2017-11-28'),
 ('2017-11-13', '2017-12-02');

If you order the rows, and take the lag from the end date before it, and take any gaps of 5 or more. In SQL Server there are LAG functions, but here's a way of doing the same. Then once you have a table of all rows and their corresponding gaps, you take the start date of that period, and create the gap period from the number of days between. Since TIMESTAMPDIFF is inclusive, you need to subtract a day.

SET @end_date   = NULL;

SELECT 
   DATE_ADD(`start_date`, INTERVAL -(`gap_from_last`-1) DAY) AS `start_date`,
   `start_date` AS `end_date`
FROM
(
SELECT
    `appointment_id`,
    CASE 
      WHEN @end_date IS NULL THEN NULL
      ELSE TIMESTAMPDIFF(DAY, @end_date, `start_date`) 
    END AS `gap_from_last`,
    `start_date`,
    @end_date   := `end_date` AS `end_date`  -- Save the lag date from the row before
FROM
    `appointments`
ORDER BY
    `start_date`,
    `end_date`
) AS `date_gap` -- Build table that has the dates and the number of days between
WHERE
  `gap_from_last` > 5;

Provides:

start_date | end_date
------------------------
2017-11-08 | 2017-11-13

Edit: Oops! Forgot the SQLFiddle (http://sqlfiddle.com/#!9/09cfce/16)

wow, this is exactly what I was looking for! thank you very much! — Lhen, Nov 03 '17 at 13:15

score 0 · Answer 2 · answered Nov 03 '17 at 13:20

0

SELECT x.end_date + INTERVAL 1 DAY unused_start
     , MIN(y.start_date) unused_end 
  FROM appointments x 
  JOIN appointments y 
    ON y.start_date >= x.end_date 
 GROUP 
    BY x.start_date  
HAVING DATEDIFF(MIN(y.start_date),unused_start) >= 5;

answered Nov 03 '17 at 13:20

Strawberry

33,750
13
40
57

check dates on database

2 Answers2