While incrementing datetime field in MYSQL

Asked

Viewed 748 times

0

I need to do a while between an initial date and a final date, that is, while the initial date is less than the final, will return a query, how can I increment this while to make it run?

mysql while

  • What do you mean, "return a query"? You want a table with all dates between A and B?

  • This is because the records are saved using the date as reference...so it would bring one record per day, referring to the dates that would run in while.

2 answers

0


My understanding is that there is no standard way to do this; you need to use some proprietary mechanism of your DBMS, and Mysql is especially poor in that sense. You can make a stored Procedure:

DELIMITER GO

CREATE TABLE `Calendar` (
  `Date` DATE NOT NULL PRIMARY KEY)
GO

CREATE PROCEDURE `PopulateCalendar`(`startDate` DATE, `endDate` DATE)
BEGIN
  WHILE `startDate` < `endDate` DO
    INSERT `Calendar` VALUES (`startDate`);
    SET `startDate` = DATE_ADD(`startDate`, INTERVAL 1 DAY);
  END WHILE;
END
GO

CALL `PopulateCalendar`('2015-01-01', '2024-12-31');
GO

SELECT * FROM `Calendar` WHERE `Date` BETWEEN '2015-04-01' AND '2015-04-30'
GO

Maybe you want to put appropriate indexes on Calendar and/or edit the PopulateCalendar not to give trouble when you have create dates that already exist.

(Sqlfiddle)

0

Browser other questions tagged mysql while

You are not signed in. Login or sign up in order to post.