Select Start and End date - Databases

TopAnswers Databases

Meta

Databases

TeX

Code Golf

APL

C++

.net

db<>fiddle

Java

*nix

PHP

PowerShell

Python

Rust

टेक्-मराठी

Typst

Web Client Dev

Web Server Dev

Select Start and End date

mysql mysql-5.6 add tag

Anonymous 1749

```
SELECT * FROM `calende_bookings` 

id       ||     id_item      ||     the_date      ||     id_state
1717                1             2021-06-21                 3
1718                1             2021-06-22                 1
1719                1             2021-06-23                 1
1720                1             2021-06-24                 1
1721                1             2021-06-25                 1
1722                1             2021-06-26                 1
1723                1             2021-06-27                 2
1724                1             2021-08-12                 3
1725                1             2021-08-13                 1
1726                1             2021-08-14                 1
1727                1             2021-08-15                 2
1728                2             2021-10-03                 3

```
Hello, I need SQL select StartDate like date with id_state=3 and EndDate like first id_state=1, date before id_state=2. So in this example that will be StartDate with id=1717 like StartDate and date with id=1722 like EndDate. Than again date with id_state=3 and date with id_state=1 before id_state=2 .... I am able to extract start date, but end date I am "blind".


```
		$result = $db->prepare("SELECT id_item, id_state, the_date, DATE_FORMAT (the_date, '%d-%m-%Y') AS from_the_date FROM calende_bookings WHERE the_date > now() AND id_item = 1 AND (id_state = 3 || id_state = 1) ORDER by the_date ASC");
		$result->execute();
		for($i=0; $row = $result->fetch(); $i++){
	        $returnArray[] = $row['from_the_date']; 
		}


?>
```

Single date is outcome, not range.

```
var disabledArrFromTo = [ 
  "21-06-2021","22-06-2021","23-06-2021","24-06-2021"] .....
```


I need range dates like outcome.

```
var disabledArrFromTo = [ 
  { "from": "21-06-2021", "to": "26-06-2021" }, 
  { "from": "12-08-2021", "to": "14-08-2021" }]
```

Top Answer

Andriy M

This is a *Gaps and Islands* problem. You want to determine "islands" of consecutive dates based on certain conditions. Once you have identified them, you can apply `MIN()` and `MAX()` to each island to obtain the boundaries ("from" and "to" dates).

In most major RDBMS products (which includes MySQL starting from version 8), this kind of problem is solved using window functions. Those are not supported in MySQL 5.6, but you can try using variables instead.

Here is my attempt:

<>https://dbfiddle.uk/?rdbms=mysql_5.6&fiddle=9a5e88a7473cbfb6acc00b454c113778&hide=7

Your original query is placed in a derived table called `main`. You can see that it additionally uses a variable, `@rn`, which is initialised in a separate derived table called `setvar`. (The actual aliases do not matter, you just have to specify some alias for each derived table as required by MySQL.) This is what `main` evaluates to:

rn | id_item | id_state | the_date  
-: | ------: | -------: | :---------
 1 |       1 |        3 | 2021-06-21
 1 |       1 |        1 | 2021-06-22
 1 |       1 |        1 | 2021-06-23
 1 |       1 |        1 | 2021-06-24
 1 |       1 |        1 | 2021-06-25
 1 |       1 |        1 | 2021-06-26
 2 |       1 |        3 | 2021-08-12
 2 |       1 |        1 | 2021-08-13
 2 |       1 |        1 | 2021-08-14

As you can see, the `rn` column, generated with the help of the `@rn` variable, identifies each island, each date range. The way it works is, after the rowset is sorted by date, the variable is increased each time when a row with the `id_state` of 3 is encountered. When `id_state` is 1, the variable is not increased. So consecutive dates get the same `rn` value as the last `id_state` of 3.

Now that each range has its own identifier, you can use that identifier to group the rows. In the main query, you can see that the rows are grouped by `id_item` (might be unnecessary since rows are filtered by `id_item` anyway, but I chose to have it as a grouping term because I am also returning this column in the output) and `rn`, which gives us the result you can see above just under the query.

1 Answer