Jump to content

Check consecutive dates in comma seperated string


Recommended Posts

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.

 

Link to post
Share on other sites

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.

Link to post
Share on other sites
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.

Link to post
Share on other sites

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

image.thumb.png.61c840761a06960014f81a6cae76aead.png

Link to post
Share on other sites

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 by Barand
  • Like 2
Link to post
Share on other sites
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!!

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.