While incrementing datetime field in MYSQL

0

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

asked by anonymous 23.06.2015 / 01:08

2 answers

0

My understanding is that there is no standard way of doing this; you need to use some proprietary mechanism of your DBMS, and MySQL is especially poor in that regard. You can do 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

You may want to put appropriate indexes in Calendar and / or edit PopulateCalendar to avoid trouble when you have create dates that already exist.

( SQLFiddle )

    
23.06.2015 / 01:25
0

The answer lies in this topic: link

I only found the question after asking.

    
23.06.2015 / 01:25