Skip to content

Instantly share code, notes, and snippets.

@onishi
Created October 4, 2011 06:50
Show Gist options
  • Save onishi/1261037 to your computer and use it in GitHub Desktop.
Save onishi/1261037 to your computer and use it in GitHub Desktop.
uninterrupted day count by MySQL Stored Procedure
CREATE TABLE entry (
blog_id INT,
created datetime
);
DROP PROCEDURE IF EXISTS set_uninterrupted_count;
DELIMITER //
CREATE PROCEDURE set_uninterrupted_count(IN id INT, OUT uninterrupted_count INT)
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE date, next_date, tmp_date date;
DECLARE count INT DEFAULT 0;
DECLARE cur CURSOR FOR
SELECT
DATE(created), DATE(created + INTERVAL 1 DAY)
FROM
entry
WHERE
blog_id = id ORDER BY created DESC;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN cur;
date_loop: REPEAT
FETCH cur INTO date, next_date;
IF NOT done THEN
IF tmp_date IS NULL THEN
SET count = count + 1;
ELSEIF next_date = tmp_date THEN
SET count = count + 1;
ELSE
LEAVE date_loop;
END IF;
SET tmp_date = date;
END IF;
UNTIL done END REPEAT;
SET uninterrupted_count = count;
END;
//
DELIMITER ;
CALL set_uninterrupted_count(1, @count);
SELECT @count;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment