MySQL 如何使用日期范围填充表格？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10132024/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to populate a table with a range of dates?
提问by Pr0no
I need a MySQL table to hold ALL DATES between 2011-01-01 and 2011-12-31. I have created a table with one column names "_date", type DATE.
我需要一个 MySQL 表来保存 2011-01-01 和 2011-12-31 之间的所有日期。我创建了一个列名为“_date”的表，类型为 DATE。
With what query can I populate the table with all the desired dates (instead of having to enter them by hand)?
用什么查询可以用所有所需的日期填充表（而不必手动输入它们）？
回答by Leniel Maccaferri
Try this:
尝试这个：
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 to play with it: http://sqlfiddle.com/#!2/65d13/1
这是使用它的 SQL Fiddle：http://sqlfiddle.com/#!2/65d13/1
EDIT (to check if date already exists) as asked by Andrew Fox.
按照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 to play with it: http://sqlfiddle.com/#!2/66f86/1
这是使用它的 SQL Fiddle：http://sqlfiddle.com/#!2/66f86/1
回答by IvanD
I did not want my SQL query to require external dependencies (needing to have a calendar table, procedure for populating a temporary table with dates, etc.) The original idea for this query came from http://jeffgarretson.wordpress.com/2012/05/04/generating-a-range-of-dates-in-mysql/which I had slightly optimized for clarity and ease of use.
我不希望我的 SQL 查询需要外部依赖（需要有一个日历表、用日期填充临时表的过程等）这个查询的最初想法来自http://jeffgarretson.wordpress.com/2012 /05/04/generate-a-range-of-dates-in-mysql/，为了清晰度和易用性，我对其进行了略微优化。
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
It is simple to optimize and scale this table for other uses. You can easily get rid of the tens and hundreds tables if you only need one week of data.
为其他用途优化和扩展此表很简单。如果您只需要一周的数据，您可以轻松摆脱数十个和数百个表。
If you need a larger set of numbers, it is easy to add a thousands table. You only need to copy and paste the table with hundreds and add a zero to 9 numbers.
如果您需要更大的一组数字，添加千位表很容易。您只需要复制和粘贴数百个表格并添加一个零到 9 个数字。
回答by computingfreak
if you're in a situation like me where procedures are prohibited, and your sql user does not have permissions for insert, therefore insert not allowed, but you want to generate a list of dates in a specific period, say current year to do some aggregation, use this
如果您处于像我这样的程序被禁止的情况，并且您的 sql 用户没有插入权限，因此不允许插入，但您想生成特定时期内的日期列表，比如当年做一些聚合，使用这个
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'
回答by Zon
I found this paste-and-go variant working:
我发现这个粘贴和去变体工作：
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');
回答by manish
I recently had a need to create a calendar_datetable as below:
我最近需要创建一个calendar_date表，如下所示：
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`));
I then populated it with all possible dates between January 1, 2001and December 31, 2100(both inclusive) using the query below:
然后，我使用以下查询在January 1, 2001和之间December 31, 2100（包括两者）填充了所有可能的日期：
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;
On my local MySQL database, the INSERTquery took just a few seconds. Hope this helps someone.
在我本地的 MySQL 数据库上，INSERT查询只用了几秒钟。希望这可以帮助某人。
回答by nnichols
If you have a table with a large enough contiguous set of ids you could use -
如果您的表具有足够大的连续 id 集，您可以使用 -
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 be aware that this might get you in trouble during leap-years (having 366 days)
注意：但请注意，这可能会在闰年（有 366 天）期间给您带来麻烦
回答by Zhili Yin
Thanks to IvanD. I've got a better solution which allowes you to create a specified calendar table. For example, if I'm trying to create a table of 2014-04, it looks like this:
感谢 IvanD。我有一个更好的解决方案，它允许您创建一个指定的日历表。例如，如果我试图创建一个 2014-04 的表，它看起来像这样：
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')
回答by independent.guru
This can be achieved in PHP by using a simple for loop. There are a couple of ways to do it. One way would be to to place the original date in a variable and have the loop run through it for every day by adding +1 day on each loop, for instance, you'll start on 01/01/2011 and then the loop will add 0 the first time, 1 day the next, followed by 2 days so forth and so forth to the $i variable. You could then print out the days or add them to your database. In this case $i would represent the counter with 0 being the starting point, <=365 is how many loops you want to go through which is equal to or less than the number of days and $i++ adds +1 to the $i variable on each loop.
这可以通过使用简单的 for 循环在 PHP 中实现。有几种方法可以做到这一点。一种方法是将原始日期放在一个变量中，并通过在每个循环上添加 +1 天来让循环每天运行它，例如，您将从 01/01/2011 开始，然后循环将第一次添加 0，下一次添加 1 天，然后是 2 天，依此类推到 $i 变量中。然后您可以打印出这些天数或将它们添加到您的数据库中。在这种情况下，$i 将表示以 0 为起点的计数器，<=365 是您要经历的循环次数等于或小于天数，$i++ 将 +1 添加到 $i 变量在每个循环上。
date('Y-m-d' converts the date into yyyy-mm-dd. Using a capital Y gives you a full 4 digit year whereas using a lower case y will give you the last 2 digits of the year. You want to keep it in this order to add it in a date field in mySQL.
date('Ymd' 将日期转换为 yyyy-mm-dd。使用大写 Y 为您提供完整的 4 位数年份，而使用小写 y 将为您提供年份的最后 2 位数。您想将其保留在此以便将其添加到 mySQL 的日期字段中。
strtotime($originalDate parses the date into a Unix time stamp and the ."+".$i." day") basically adds the value of $i in days to the date.
strtotime($originalDate 将日期解析为 Unix 时间戳和 ."+".$i." day") 基本上将 $i 的天数添加到日期中。
Finally there's the mysqli query. $db represents the database connection variable, this will need to be changed to whatever variable you have set for the connection. This is followed by the actual query. Just exchange the word table for your table name and the date before VALUES to you date row name and you're ready to go.
最后是 mysqli 查询。$db 代表数据库连接变量，这将需要更改为您为连接设置的任何变量。接下来是实际的查询。只需将表格名称的单词 table 和 VALUES 之前的日期交换为日期行名称，您就可以开始了。
The following is an example:
下面是一个例子：
<?php
for($i=0;$i<=365;$i++){
$originalDate = "01/01/2011";
$date = date('Y-m-d',strtotime($originalDate . "+".$i." day"));
mysqli_query($db, "INSERT INTO table (date)VALUES('$date')");
}
Another way to achieve this using the for function would be to include the strtotime dates directly in the for actions as an oppose to the counter variables, which is an even shorter piece of code. Replace $i=0 (the starting counter point) with the starting day point, follow that with the less than or equal to the end day point (the number of loops) then finally with your plus +1 to the first statement placed into a variable ready for use.
使用 for 函数实现此目的的另一种方法是将 strtotime 日期直接包含在 for 操作中作为计数器变量的对立面，这是一段更短的代码。用起始日点替换 $i=0（起始计数器点），然后用小于或等于结束日点（循环数）然后最后用加号 +1 将第一个语句放入 a变量准备使用。
Finally, convert the date into Y-m-d format ready to be placed into the database and run the query.
最后，将日期转换为 Ymd 格式准备好放入数据库并运行查询。
Again, as with the first example, this can be printed or placed directly into 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("Y-m-d", $startdate);
mysqli_query($db, "INSERT INTO tracking (date)VALUES('$date')");
}
I've probably made it sound more confusing than it is, but hope it will at least give you an idea on how it works.
我可能让它听起来比实际更令人困惑，但希望它至少能让您了解它是如何工作的。
回答by ilgoom
Inspired by IvanD`s great number join i come to this:
受到 IvanD 的大量加入的启发，我来到了这个：
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')
回答by Dmytro L
INSERT INTO my_dates (\`_date\`) SELECT DATE_ADD('2011-01-01', INTERVAL @_tmp:=@_tmp+1 day) \`_date\`
FROM (SELECT @_tmp:=-1 d 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) a /\*10^1\*/
JOIN (SELECT 0 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) b /\*10^2\*/
JOIN (SELECT 0 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) c /\*10^3\*/
WHERE @_tmp+1 BETWEEN 0 AND DATEDIFF('2011-12-31', '2011-01-01');
