How to populate a table with a date range?

Question

I need a MySQL table to hold ALL DATES between 2011-01-01 and 2011-12-31. I created a table with one column name "_date", enter DATE.

With what query can I populate the table with all the desired dates (instead of manually entering them)?

Answer 1

Try the following:

DROP PROCEDURE IF EXISTS filldates; DELIMITER | CREATE PROCEDURE filldates(dateStart DATE, dateEnd DATE) BEGIN WHILE dateStart <= dateEnd DO INSERT INTO tablename (_date) VALUES (dateStart); SET dateStart = date_add(dateStart, INTERVAL 1 DAY); END WHILE; END; | DELIMITER ; CALL filldates('2011-01-01','2011-12-31'); 

Here's the SQL Fiddle script: http://sqlfiddle.com/#!2/65d13/1

EDIT (to check if a date exists) as requested by Andrew Fox .

 CREATE PROCEDURE filldates(dateStart DATE, dateEnd DATE) BEGIN DECLARE adate date; WHILE dateStart <= dateEnd DO SET adate = (SELECT mydate FROM MyDates WHERE mydate = dateStart); IF adate IS NULL THEN BEGIN INSERT INTO MyDates (mydate) VALUES (dateStart); END; END IF; SET dateStart = date_add(dateStart, INTERVAL 1 DAY); END WHILE; END;// 

Here's the SQL Fiddle script: http://sqlfiddle.com/#!2/66f86/1

Answer 2

I didn’t want external dependencies in my SQL query (you must have a calendar table, a procedure for filling in a temporary table with dates, etc.). The initial idea for this request came from http://jeffgarretson.wordpress.com/2012/05/04/generating-a-range-of-dates-in-mysql/ , which I slightly optimized for clarity and ease of use.

 SELECT (CURDATE() - INTERVAL c.number DAY) AS date FROM (SELECT singles + tens + hundreds number FROM ( SELECT 0 singles UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 ) singles JOIN (SELECT 0 tens UNION ALL SELECT 10 UNION ALL SELECT 20 UNION ALL SELECT 30 UNION ALL SELECT 40 UNION ALL SELECT 50 UNION ALL SELECT 60 UNION ALL SELECT 70 UNION ALL SELECT 80 UNION ALL SELECT 90 ) tens JOIN (SELECT 0 hundreds UNION ALL SELECT 100 UNION ALL SELECT 200 UNION ALL SELECT 300 UNION ALL SELECT 400 UNION ALL SELECT 500 UNION ALL SELECT 600 UNION ALL SELECT 700 UNION ALL SELECT 800 UNION ALL SELECT 900 ) hundreds ORDER BY number DESC) c WHERE c.number BETWEEN 0 and 364 

Simple optimization and scaling of this table for other purposes. You can easily get rid of tens and hundreds of tables if you need only one week of data.

If you need a larger set of numbers, it's easy to add a table of thousands. You just need to copy and paste the table in hundreds and add zero to 9 numbers.

Answer 3

I found this option with paste and move:

 DROP PROCEDURE IF EXISTS FillCalendar; DROP TABLE IF EXISTS calendar; CREATE TABLE IF NOT EXISTS calendar(calendar_date DATE NOT NULL PRIMARY KEY); DELIMITER $$ CREATE PROCEDURE FillCalendar(start_date DATE, end_date DATE) BEGIN DECLARE crt_date DATE; SET crt_date = start_date; WHILE crt_date <= end_date DO INSERT IGNORE INTO calendar VALUES(crt_date); SET crt_date = ADDDATE(crt_date, INTERVAL 1 DAY); END WHILE; END$$ DELIMITER ; CALL FillCalendar('2013-01-01', '2013-01-03'); CALL FillCalendar('2013-01-01', '2013-01-07'); 

Answer 4

If you have a table with a sufficiently large continuous set of identifiers, you can use -

 INSERT INTO tablename (_date) SELECT '2011-01-01' + INTERVAL (id - 1) DAY FROM some_table_with_lots_of_ids WHERE id BETWEEN 1 AND 365 

note: but keep in mind that this can cause you problems during leap years (having 366 days)

Answer 5

Thanks IvanD. I have a better solution that allows you to create a specific calendar table. For example, if I try to create a table 2014-04, it looks like this:

 SELECT (CURDATE() - INTERVAL c.number DAY) AS DATE FROM ( SELECT singles + tens + hundreds number FROM ( SELECT 0 singles UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 ) singles JOIN ( SELECT 0 tens UNION ALL SELECT 10 UNION ALL SELECT 20 UNION ALL SELECT 30 UNION ALL SELECT 40 UNION ALL SELECT 50 UNION ALL SELECT 60 UNION ALL SELECT 70 UNION ALL SELECT 80 UNION ALL SELECT 90 ) tens JOIN ( SELECT 0 hundreds UNION ALL SELECT 100 UNION ALL SELECT 200 UNION ALL SELECT 300 UNION ALL SELECT 400 UNION ALL SELECT 500 UNION ALL SELECT 600 UNION ALL SELECT 700 UNION ALL SELECT 800 UNION ALL SELECT 900 ) hundreds ORDER BY number DESC ) c WHERE c.number BETWEEN DAYOFYEAR(NOW()) - DAYOFYEAR('2014-04-01')- DAY(LAST_DAY('2014-04-01')) +1 AND DAYOFYEAR(NOW()) - DAYOFYEAR('2014-04-01') 

Answer 6

This can be done in PHP using a simple loop. There are several ways to do this. One way is to put the original date in a variable and loop through it every day by adding +1 day to each cycle, for example, you will start on 01/01/2011, and then the cycle will add 0 the first time, 1 next day, then 2 days, etc. to the variable $ i. You can then print the days or add them to your database. In this case, $ i will represent a counter with 0, which is the starting point, <= 365 - how many cycles you want to go through, which is equal to or less than the number of days, and $ i ++ adds a +1 to $ i variable in each cycle .

date ('Ymd' converts the date to yyyy-mm-dd. Using capital Y gives you the full 4-digit year, while in lowercase y you get the last 2 digits of the year. In that order, add it to the date field in mySQL.

strtotime ($ originalDate parses the date at the Unix timestamp and. "+". $ i. "day") basically adds the value of $ i in days to date.

Finally, there is a mysqli query. $ db is a database connection variable, it will need to be changed to any variable that you set for the connection. This is followed by the actual request. Just exchange a word table for your table name and the date preceding VALUES, before the date name and you are ready to go.

The following is an example:

 <?php for($i=0;$i<=365;$i++){ $originalDate = "01/01/2011"; $date = date('Ym-d',strtotime($originalDate . "+".$i." day")); mysqli_query($db, "INSERT INTO table (date)VALUES('$date')"); } 

Another way to achieve this with the for function is to include strtotime dates directly in actions for actions as a counter to counter variables, which is an even shorter piece of code. Replace $ i = 0 (the start point of the counter) with the start point of the day, follow it with a smaller or equal end point of the day (number of cycles), and then finally, with your plus one +1 to the first statement placed in the variable is ready to use .

Finally, convert the date to Ymd format, ready to be placed in the database, and run the query.

Again, as in the first example, this can be printed out or placed directly in your database.

The following is an example:

 <?php for ($startdate = strtotime("2011-01-01"); $startdate <= strtotime("2011-12-31"); $startdate = strtotime("+1 day", $startdate)) { $date= date("Ymd", $startdate); mysqli_query($db, "INSERT INTO tracking (date)VALUES('$date')"); } 

I probably made it more confusing than that, but I hope that it will at least give you an idea of ​​how this works.

Answer 7

if you are in a situation like me, where the procedures are forbidden , and your sql user does not have permissions to insert, so insert is not allowed , but you want to create a list of dates for a certain period , say, to do some aggregation this year, use this

 select * from (select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) gen_date from (select 0 t0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0, (select 0 t1 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1, (select 0 t2 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2, (select 0 t3 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3, (select 0 t4 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v where gen_date between '2017-01-01' and '2017-12-31' 

Answer 8

Inspired by a large number of IvanD, I came to this:

 SELECT DATE_ADD('2015-10-21', INTERVAL c.number DAY) AS DATE FROM ( SELECT singles + tens + hundreds+thousands number FROM ( SELECT 0 singles UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 ) singles JOIN ( SELECT 0 tens UNION ALL SELECT 10 UNION ALL SELECT 20 UNION ALL SELECT 30 UNION ALL SELECT 40 UNION ALL SELECT 50 UNION ALL SELECT 60 UNION ALL SELECT 70 UNION ALL SELECT 80 UNION ALL SELECT 90 ) tens JOIN ( SELECT 0 hundreds UNION ALL SELECT 100 UNION ALL SELECT 200 UNION ALL SELECT 300 UNION ALL SELECT 400 UNION ALL SELECT 500 UNION ALL SELECT 600 UNION ALL SELECT 700 UNION ALL SELECT 800 UNION ALL SELECT 900 ) hundreds JOIN ( SELECT 0 thousands UNION ALL SELECT 1000 UNION ALL SELECT 2000 UNION ALL SELECT 3000 UNION ALL SELECT 4000 UNION ALL SELECT 5000 UNION ALL SELECT 6000 UNION ALL SELECT 7000 UNION ALL SELECT 8000 UNION ALL SELECT 9000 ) thousands ORDER BY number DESC ) c WHERE c.number BETWEEN 0 AND DATEDIFF('2016-10-08', '2015-10-21') 

Answer 9

Recently, I needed to create a calendar_date table, as shown below:

 CREATE TABLE `calendar_date` ( `date` DATE NOT NULL -- A calendar date. , `day` SMALLINT NOT NULL -- The day of the year for the date, 1-366. , `month` TINYINT NOT NULL -- The month number, 1-12. , `year` SMALLINT NOT NULL -- The year. , PRIMARY KEY (`id`)); 

Then I filled it with all possible dates between January 1, 2001 and December 31, 2100 (both inclusive) using the following query:

 INSERT INTO `calendar_date` (`date` , `day` , `month` , `year`) SELECT DATE , INCREMENT + 1 , MONTH(DATE) , YEAR(DATE) FROM -- Generate all possible dates for every year from 2001 to 2100. (SELECT DATE_ADD(CONCAT(YEAR, '-01-01'), INTERVAL INCREMENT DAY) DATE , INCREMENT FROM (SELECT (UNITS + TENS + HUNDREDS) INCREMENT FROM (SELECT 0 UNITS UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) UNITS CROSS JOIN (SELECT 0 TENS UNION SELECT 10 UNION SELECT 20 UNION SELECT 30 UNION SELECT 40 UNION SELECT 50 UNION SELECT 60 UNION SELECT 70 UNION SELECT 80 UNION SELECT 90) TENS CROSS JOIN (SELECT 0 HUNDREDS UNION SELECT 100 UNION SELECT 200 UNION SELECT 300 UNION SELECT 400 UNION SELECT 500 UNION SELECT 600 UNION SELECT 700 UNION SELECT 800 UNION SELECT 900) HUNDREDS ) INCREMENT -- For every year from 2001 to 2100, find the number of days in the year. , (SELECT YEAR , DAYOFYEAR(CONCAT(YEAR, '-12-31')) - DAYOFYEAR(CONCAT(YEAR, '-01-01')) + 1 DAYS FROM -- Generate years from 2001 to 2100. (SELECT (2000 + UNITS + TENS) YEAR FROM (SELECT 0 UNITS UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) UNITS CROSS JOIN (SELECT 0 TENS UNION SELECT 10 UNION SELECT 20 UNION SELECT 30 UNION SELECT 40 UNION SELECT 50 UNION SELECT 60 UNION SELECT 70 UNION SELECT 80 UNION SELECT 90) TENS ) YEAR WHERE YEAR BETWEEN 2001 AND 2100 ) YEAR WHERE INCREMENT BETWEEN 0 AND DAYS - 1 ORDER BY YEAR , INCREMENT) DATE; 

In my local MySQL database, an INSERT query took only a few seconds. Hope this helps someone.