Check consecutive dates in comma seperated string

Adamhumbug · April 12, 2021

HI All,

I have some dates stored as a comma sep string in a database (col = adates)

The contents looks like the below

2021-04-04 07:00,2021-04-05 07:00,2021-04-06 07:00,2021-04-07 07:00,2021-04-08 07:00,2021-04-09 07:00,2021-04-10 07:00,2021-04-11 07:00,2021-04-12 07:00,2021-04-13 07:00

I am trying to select rows with 10 dates or less that are consecutive.

I have done the number of dates with char_count but am struggling with the consecutive part.

The table structure is

id, azones

Any help with this will be really appreciated as i will also need to find the people where dates are not consecutive.

Barand · April 12, 2021

If you store data in that manner you deserve to have problems.

You can use a query to get those with < 10 dates but you will need to resort to php arrays for the rest.

Adamhumbug · April 12, 2021

I agree that this is less than ideal.

As horrible as it sounds, would i be able to substring them and str_to_date them to get some of the way there?

Adamhumbug · April 12, 2021

This unfortunately needs to to be entirely sql if that is even possible. If its not then we are where we are.

Edited April 12, 2021 by Adamhumbug

Barand · April 12, 2021

32 minutes ago, Adamhumbug said:

This unfortunately needs to to be entirely sql

Then normalize your data and you stand a chance. Or, as previously stated, be prepared to use php arrays. Arrays/delimited strings of data and SQL don't play nice. Fine if all you want to do is store them as string and retrieve them as a string.

You might be able to extract the dates using SUBSTRING_INDEX but I wouldn't attempt it with so many dates and especially when the number of dates is variable.

Barand · April 12, 2021

In a couple of weeks time, when your scalp is bleeding from all the head-scratching trying to do it with only SQL, have a go with this PHP/SQL hybrid.


//
//  GENERATE TEST DATA
//
$pdo->exec("DROP TABLE IF EXISTS ahtest");
$pdo->exec("CREATE TABLE ahtest( id int not null auto_increment primary key, adates varchar(255))");

$data = [];
for ($i=0; $i<20; $i++) {
    $numdates = rand(8,12);
    $dt = new DateTime('2021-04-01');
    $dates = [];
    $incs = [1,1,1,1,1,2,1,1,1,1,1,2];   // 16.6% chance of non-consecutive dates
    for ($j=0; $j<$numdates; $j++) {
        $inc = $incs[array_rand($incs)];
        $dates[] = $dt->format('Y-m-d h:i');
        $dt->modify("+ $inc days");
    }
    $data[] = sprintf("('%s')", join(',', $dates));
}
$pdo->exec("INSERT INTO ahtest (adates) VALUES " . join(',', $data));

//
//  GET RECORDS WITH < 10 DATES
//
$res = $pdo->query("SELECT id
                         , adates
                    FROM ahtest
                    WHERE LENGTH(adates) < 160
                    ");
$tdata = '';
foreach ($res as $r) {
    $datelist = $r['adates'];
    $consec = isConsec($datelist) ? "<i class='fas fa-check'></i>" : "<i class='fas fa-times'></i>";
    $tdata .= "<tr><td>{$r['id']}</td>
                   <td>$datelist</td>
                   <td>$consec</td>
              </tr>
              ";
}

function isConsec(&$datelist)
{
    $dates = explode(',', $datelist);
    $newdates[] = $dates[0];
    $result = 1;
    $k = count($dates);
    for ($i=1; $i<$k; $i++) {
        $a = new DateTime($dates[$i-1]);
        $b = new DateTime($dates[$i]);
        if ($a->diff($b)->days != 1) {
            $newdates[] = "<span class='nonconsec'>{$dates[$i]}</span>";
            $result = 0;
        }
        else $newdates[] = $dates[$i];
    }
    $datelist = join(', ', $newdates);
    return $result;
}
?>


<!DOCTYPE html>
<html lang="en">
<head>
<title>Dates Example</title>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<link rel="stylesheet" href="https://www.w3schools.com/w3css/4/w3.css">
<link rel="stylesheet" href="https://use.fontawesome.com/releases/v5.15.1/css/all.css">
<style type='text/css'>
    .nonconsec {
        font-weight: 600;
        color: red;
    }
</style>
</head>
<body>
<div class="w3-container">
    <div class="w3-panel w3-black w3-padding">
        <h1>Dates Example - AdamHumbug</h1>
    </div>
    <table class='w3-table-all'>
        <tr class='w3-blue'>
            <th>ID</th>
            <th>Dates</th>
            <th>Consecutive</th>
        </tr>
        <?=$tdata?>
    </table>
</div>
</body>
</html>

Gives

Barand · April 13, 2021

I'll eat my words. I couldn't resist the challenge so, having slept on it, I wrote a an SQL function "isConsecutive(dates)" to find records where there are fewer than 10 dates and they are consecutive.

TEST DATA and QUERY

TABLE: ahtest
+----+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | adates                                                                                                                                                                                                      |
+----+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|  1 | 2021-04-01 12:00,2021-04-02 12:00,2021-04-03 12:00,2021-04-04 12:00,2021-04-05 12:00,2021-04-06 12:00,2021-04-07 12:00,2021-04-08 12:00                                                                     |
|  2 | 2021-04-01 12:00,2021-04-02 12:00,2021-04-03 12:00,2021-04-04 12:00,2021-04-05 12:00,2021-04-06 12:00,2021-04-08 12:00,2021-04-10 12:00,2021-04-11 12:00,2021-04-12 12:00,2021-04-13 12:00,2021-04-14 12:00 |
|  3 | 2021-04-01 12:00,2021-04-03 12:00,2021-04-04 12:00,2021-04-05 12:00,2021-04-06 12:00,2021-04-07 12:00,2021-04-08 12:00,2021-04-10 12:00,2021-04-11 12:00,2021-04-12 12:00                                   |
|  4 | 2021-04-01 12:00,2021-04-02 12:00,2021-04-03 12:00,2021-04-04 12:00,2021-04-05 12:00,2021-04-06 12:00,2021-04-07 12:00,2021-04-08 12:00,2021-04-10 12:00,2021-04-11 12:00                                   |
|  5 | 2021-04-01 12:00,2021-04-02 12:00,2021-04-03 12:00,2021-04-04 12:00,2021-04-05 12:00,2021-04-06 12:00,2021-04-07 12:00,2021-04-08 12:00,2021-04-09 12:00,2021-04-11 12:00,2021-04-12 12:00,2021-04-13 12:00 |
|  6 | 2021-04-01 12:00,2021-04-02 12:00,2021-04-03 12:00,2021-04-05 12:00,2021-04-06 12:00,2021-04-07 12:00,2021-04-08 12:00,2021-04-09 12:00                                                                     |
|  7 | 2021-04-01 12:00,2021-04-02 12:00,2021-04-03 12:00,2021-04-04 12:00,2021-04-06 12:00,2021-04-08 12:00,2021-04-09 12:00,2021-04-11 12:00,2021-04-12 12:00,2021-04-13 12:00                                   |
|  8 | 2021-04-01 12:00,2021-04-02 12:00,2021-04-03 12:00,2021-04-04 12:00,2021-04-05 12:00,2021-04-06 12:00,2021-04-07 12:00,2021-04-08 12:00                                                                     |
|  9 | 2021-04-01 12:00,2021-04-02 12:00,2021-04-03 12:00,2021-04-05 12:00,2021-04-06 12:00,2021-04-07 12:00,2021-04-08 12:00,2021-04-09 12:00,2021-04-11 12:00,2021-04-13 12:00,2021-04-15 12:00                  |
| 10 | 2021-04-01 12:00,2021-04-02 12:00,2021-04-03 12:00,2021-04-04 12:00,2021-04-05 12:00,2021-04-06 12:00,2021-04-08 12:00,2021-04-09 12:00,2021-04-10 12:00,2021-04-11 12:00,2021-04-13 12:00,2021-04-14 12:00 |
| 11 | 2021-04-01 12:00,2021-04-02 12:00,2021-04-04 12:00,2021-04-06 12:00,2021-04-07 12:00,2021-04-08 12:00,2021-04-09 12:00,2021-04-10 12:00,2021-04-11 12:00,2021-04-12 12:00,2021-04-13 12:00,2021-04-14 12:00 |
| 12 | 2021-04-01 12:00,2021-04-02 12:00,2021-04-03 12:00,2021-04-04 12:00,2021-04-05 12:00,2021-04-06 12:00,2021-04-07 12:00,2021-04-09 12:00,2021-04-10 12:00,2021-04-11 12:00,2021-04-12 12:00                  |
| 13 | 2021-04-01 12:00,2021-04-02 12:00,2021-04-03 12:00,2021-04-04 12:00,2021-04-06 12:00,2021-04-07 12:00,2021-04-08 12:00,2021-04-09 12:00,2021-04-10 12:00,2021-04-11 12:00,2021-04-13 12:00                  |
| 14 | 2021-04-01 12:00,2021-04-02 12:00,2021-04-03 12:00,2021-04-04 12:00,2021-04-05 12:00,2021-04-06 12:00,2021-04-07 12:00,2021-04-08 12:00                                                                     |
| 15 | 2021-04-01 12:00,2021-04-02 12:00,2021-04-04 12:00,2021-04-05 12:00,2021-04-06 12:00,2021-04-07 12:00,2021-04-08 12:00,2021-04-09 12:00,2021-04-10 12:00,2021-04-11 12:00                                   |
| 16 | 2021-04-01 12:00,2021-04-03 12:00,2021-04-04 12:00,2021-04-05 12:00,2021-04-06 12:00,2021-04-07 12:00,2021-04-08 12:00,2021-04-10 12:00,2021-04-11 12:00                                                    |
| 17 | 2021-04-01 12:00,2021-04-02 12:00,2021-04-04 12:00,2021-04-05 12:00,2021-04-06 12:00,2021-04-07 12:00,2021-04-08 12:00,2021-04-09 12:00,2021-04-10 12:00,2021-04-11 12:00                                   |
| 18 | 2021-04-01 12:00,2021-04-02 12:00,2021-04-03 12:00,2021-04-04 12:00,2021-04-05 12:00,2021-04-06 12:00,2021-04-07 12:00,2021-04-08 12:00                                                                     |
| 19 | 2021-04-01 12:00,2021-04-02 12:00,2021-04-03 12:00,2021-04-04 12:00,2021-04-05 12:00,2021-04-06 12:00,2021-04-07 12:00,2021-04-08 12:00,2021-04-10 12:00                                                    |
| 20 | 2021-04-01 12:00,2021-04-02 12:00,2021-04-03 12:00,2021-04-04 12:00,2021-04-06 12:00,2021-04-08 12:00,2021-04-10 12:00,2021-04-11 12:00,2021-04-12 12:00,2021-04-13 12:00,2021-04-14 12:00,2021-04-15 12:00 |
+----+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

mysql> SELECT id
    ->      , adates
    -> FROM ahtest
    -> WHERE isConsecutive(adates);
+----+-----------------------------------------------------------------------------------------------------------------------------------------+
| id | adates                                                                                                                                  |
+----+-----------------------------------------------------------------------------------------------------------------------------------------+
|  1 | 2021-04-01 12:00,2021-04-02 12:00,2021-04-03 12:00,2021-04-04 12:00,2021-04-05 12:00,2021-04-06 12:00,2021-04-07 12:00,2021-04-08 12:00 |
|  8 | 2021-04-01 12:00,2021-04-02 12:00,2021-04-03 12:00,2021-04-04 12:00,2021-04-05 12:00,2021-04-06 12:00,2021-04-07 12:00,2021-04-08 12:00 |
| 14 | 2021-04-01 12:00,2021-04-02 12:00,2021-04-03 12:00,2021-04-04 12:00,2021-04-05 12:00,2021-04-06 12:00,2021-04-07 12:00,2021-04-08 12:00 |
| 18 | 2021-04-01 12:00,2021-04-02 12:00,2021-04-03 12:00,2021-04-04 12:00,2021-04-05 12:00,2021-04-06 12:00,2021-04-07 12:00,2021-04-08 12:00 |
+----+-----------------------------------------------------------------------------------------------------------------------------------------+

THE FUNCTION

DELIMITER $$
CREATE FUNCTION `isConsecutive`(dates varchar(255)) RETURNS int(11)
BEGIN
    DECLARE k INTEGER DEFAULT 1;
    DECLARE da DATE DEFAULT SUBSTRING_INDEX(dates, ',', 1);
    DECLARE db DATE ;
    DECLARE num INTEGER DEFAULT (LENGTH(dates)+1) DIV 17;
    DECLARE strx VARCHAR(255) DEFAULT SUBSTRING_INDEX(dates, ',', -(num-k));
    DECLARE isconsec INTEGER DEFAULT 1;
    
    IF num >= 10 THEN
		RETURN 0;
	END IF;
    
    WHILE LENGTH(strx) > 0 DO
        SET db = SUBSTRING_INDEX(strx, ',', 1);
        if DATEDIFF(db, da) <> 1 THEN
            SET isconsec = 0;
        END IF;
        SET k = k + 1;
        SET da = SUBSTRING_INDEX(strx, ',', 1);
        SET strx = SUBSTRING_INDEX(strx, ',', -(num-k));
    END WHILE;
RETURN isconsec;
END$$

DELIMITER ;

Edited April 13, 2021 by Barand

Adamhumbug · April 20, 2021

On 4/13/2021 at 11:28 AM, Barand said:

I'll eat my words. I couldn't resist the challenge so, having slept on it, I wrote a an SQL function "isConsecutive(dates)" to find records where there are fewer than 10 dates and they are consecutive.

TEST DATA and QUERY


TABLE: ahtest
+----+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | adates                                                                                                                                                                                                      |
+----+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|  1 | 2021-04-01 12:00,2021-04-02 12:00,2021-04-03 12:00,2021-04-04 12:00,2021-04-05 12:00,2021-04-06 12:00,2021-04-07 12:00,2021-04-08 12:00                                                                     |
|  2 | 2021-04-01 12:00,2021-04-02 12:00,2021-04-03 12:00,2021-04-04 12:00,2021-04-05 12:00,2021-04-06 12:00,2021-04-08 12:00,2021-04-10 12:00,2021-04-11 12:00,2021-04-12 12:00,2021-04-13 12:00,2021-04-14 12:00 |
|  3 | 2021-04-01 12:00,2021-04-03 12:00,2021-04-04 12:00,2021-04-05 12:00,2021-04-06 12:00,2021-04-07 12:00,2021-04-08 12:00,2021-04-10 12:00,2021-04-11 12:00,2021-04-12 12:00                                   |
|  4 | 2021-04-01 12:00,2021-04-02 12:00,2021-04-03 12:00,2021-04-04 12:00,2021-04-05 12:00,2021-04-06 12:00,2021-04-07 12:00,2021-04-08 12:00,2021-04-10 12:00,2021-04-11 12:00                                   |
|  5 | 2021-04-01 12:00,2021-04-02 12:00,2021-04-03 12:00,2021-04-04 12:00,2021-04-05 12:00,2021-04-06 12:00,2021-04-07 12:00,2021-04-08 12:00,2021-04-09 12:00,2021-04-11 12:00,2021-04-12 12:00,2021-04-13 12:00 |
|  6 | 2021-04-01 12:00,2021-04-02 12:00,2021-04-03 12:00,2021-04-05 12:00,2021-04-06 12:00,2021-04-07 12:00,2021-04-08 12:00,2021-04-09 12:00                                                                     |
|  7 | 2021-04-01 12:00,2021-04-02 12:00,2021-04-03 12:00,2021-04-04 12:00,2021-04-06 12:00,2021-04-08 12:00,2021-04-09 12:00,2021-04-11 12:00,2021-04-12 12:00,2021-04-13 12:00                                   |
|  8 | 2021-04-01 12:00,2021-04-02 12:00,2021-04-03 12:00,2021-04-04 12:00,2021-04-05 12:00,2021-04-06 12:00,2021-04-07 12:00,2021-04-08 12:00                                                                     |
|  9 | 2021-04-01 12:00,2021-04-02 12:00,2021-04-03 12:00,2021-04-05 12:00,2021-04-06 12:00,2021-04-07 12:00,2021-04-08 12:00,2021-04-09 12:00,2021-04-11 12:00,2021-04-13 12:00,2021-04-15 12:00                  |
| 10 | 2021-04-01 12:00,2021-04-02 12:00,2021-04-03 12:00,2021-04-04 12:00,2021-04-05 12:00,2021-04-06 12:00,2021-04-08 12:00,2021-04-09 12:00,2021-04-10 12:00,2021-04-11 12:00,2021-04-13 12:00,2021-04-14 12:00 |
| 11 | 2021-04-01 12:00,2021-04-02 12:00,2021-04-04 12:00,2021-04-06 12:00,2021-04-07 12:00,2021-04-08 12:00,2021-04-09 12:00,2021-04-10 12:00,2021-04-11 12:00,2021-04-12 12:00,2021-04-13 12:00,2021-04-14 12:00 |
| 12 | 2021-04-01 12:00,2021-04-02 12:00,2021-04-03 12:00,2021-04-04 12:00,2021-04-05 12:00,2021-04-06 12:00,2021-04-07 12:00,2021-04-09 12:00,2021-04-10 12:00,2021-04-11 12:00,2021-04-12 12:00                  |
| 13 | 2021-04-01 12:00,2021-04-02 12:00,2021-04-03 12:00,2021-04-04 12:00,2021-04-06 12:00,2021-04-07 12:00,2021-04-08 12:00,2021-04-09 12:00,2021-04-10 12:00,2021-04-11 12:00,2021-04-13 12:00                  |
| 14 | 2021-04-01 12:00,2021-04-02 12:00,2021-04-03 12:00,2021-04-04 12:00,2021-04-05 12:00,2021-04-06 12:00,2021-04-07 12:00,2021-04-08 12:00                                                                     |
| 15 | 2021-04-01 12:00,2021-04-02 12:00,2021-04-04 12:00,2021-04-05 12:00,2021-04-06 12:00,2021-04-07 12:00,2021-04-08 12:00,2021-04-09 12:00,2021-04-10 12:00,2021-04-11 12:00                                   |
| 16 | 2021-04-01 12:00,2021-04-03 12:00,2021-04-04 12:00,2021-04-05 12:00,2021-04-06 12:00,2021-04-07 12:00,2021-04-08 12:00,2021-04-10 12:00,2021-04-11 12:00                                                    |
| 17 | 2021-04-01 12:00,2021-04-02 12:00,2021-04-04 12:00,2021-04-05 12:00,2021-04-06 12:00,2021-04-07 12:00,2021-04-08 12:00,2021-04-09 12:00,2021-04-10 12:00,2021-04-11 12:00                                   |
| 18 | 2021-04-01 12:00,2021-04-02 12:00,2021-04-03 12:00,2021-04-04 12:00,2021-04-05 12:00,2021-04-06 12:00,2021-04-07 12:00,2021-04-08 12:00                                                                     |
| 19 | 2021-04-01 12:00,2021-04-02 12:00,2021-04-03 12:00,2021-04-04 12:00,2021-04-05 12:00,2021-04-06 12:00,2021-04-07 12:00,2021-04-08 12:00,2021-04-10 12:00                                                    |
| 20 | 2021-04-01 12:00,2021-04-02 12:00,2021-04-03 12:00,2021-04-04 12:00,2021-04-06 12:00,2021-04-08 12:00,2021-04-10 12:00,2021-04-11 12:00,2021-04-12 12:00,2021-04-13 12:00,2021-04-14 12:00,2021-04-15 12:00 |
+----+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

mysql> SELECT id
    ->      , adates
    -> FROM ahtest
    -> WHERE isConsecutive(adates);
+----+-----------------------------------------------------------------------------------------------------------------------------------------+
| id | adates                                                                                                                                  |
+----+-----------------------------------------------------------------------------------------------------------------------------------------+
|  1 | 2021-04-01 12:00,2021-04-02 12:00,2021-04-03 12:00,2021-04-04 12:00,2021-04-05 12:00,2021-04-06 12:00,2021-04-07 12:00,2021-04-08 12:00 |
|  8 | 2021-04-01 12:00,2021-04-02 12:00,2021-04-03 12:00,2021-04-04 12:00,2021-04-05 12:00,2021-04-06 12:00,2021-04-07 12:00,2021-04-08 12:00 |
| 14 | 2021-04-01 12:00,2021-04-02 12:00,2021-04-03 12:00,2021-04-04 12:00,2021-04-05 12:00,2021-04-06 12:00,2021-04-07 12:00,2021-04-08 12:00 |
| 18 | 2021-04-01 12:00,2021-04-02 12:00,2021-04-03 12:00,2021-04-04 12:00,2021-04-05 12:00,2021-04-06 12:00,2021-04-07 12:00,2021-04-08 12:00 |
+----+-----------------------------------------------------------------------------------------------------------------------------------------+

THE FUNCTION


DELIMITER $$
CREATE FUNCTION `isConsecutive`(dates varchar(255)) RETURNS int(11)
BEGIN
    DECLARE k INTEGER DEFAULT 1;
    DECLARE da DATE DEFAULT SUBSTRING_INDEX(dates, ',', 1);
    DECLARE db DATE ;
    DECLARE num INTEGER DEFAULT (LENGTH(dates)+1) DIV 17;
    DECLARE strx VARCHAR(255) DEFAULT SUBSTRING_INDEX(dates, ',', -(num-k));
    DECLARE isconsec INTEGER DEFAULT 1;
    
    IF num >= 10 THEN
		RETURN 0;
	END IF;
    
    WHILE LENGTH(strx) > 0 DO
        SET db = SUBSTRING_INDEX(strx, ',', 1);
        if DATEDIFF(db, da) <> 1 THEN
            SET isconsec = 0;
        END IF;
        SET k = k + 1;
        SET da = SUBSTRING_INDEX(strx, ',', 1);
        SET strx = SUBSTRING_INDEX(strx, ',', -(num-k));
    END WHILE;
RETURN isconsec;
END$$

DELIMITER ;

WOW!!! - Thank you VERY much for this!!

Sign In

Check consecutive dates in comma seperated string

Recommended Posts

Adamhumbug

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

Adamhumbug

Link to comment

Share on other sites

Adamhumbug

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

Adamhumbug

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information