MySQL 从日期范围生成天数

Question

提问by Pentium10

I would like to run a query like

我想运行一个查询

select ... as days where `date` is between '2010-01-20' and '2010-01-24'

And return data like:

并返回数据，如：

days
----------
2010-01-20
2010-01-21
2010-01-22
2010-01-23
2010-01-24

Answer 1

回答by RedFilter

This solution uses no loops, procedures, or temp tables. The subquery generates dates for the last 10,000 days, and could be extended to go as far back or forward as you wish.

此解决方案不使用循环、过程或临时表。子查询生成过去 10,000 天的日期，并且可以扩展到任意远的后退或前进。

select a.Date 
from (
    select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a) + (1000 * d.a) ) DAY as Date
    from (select 0 as a 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) as a
    cross join (select 0 as a 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) as b
    cross join (select 0 as a 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) as c
    cross join (select 0 as a 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) as d
) a
where a.Date between '2010-01-20' and '2010-01-24'

Output:

输出：

Date
----------
2010-01-24
2010-01-23
2010-01-22
2010-01-21
2010-01-20

Notes on Performance

性能注意事项

Testing it out here, the performance is surprisingly good: the above query takes 0.0009 sec.

在这里测试一下，性能出奇的好：上面的查询需要 0.0009 秒。

If we extend the subquery to generate approx. 100,000 numbers (and thus about 274 years worth of dates), it runs in 0.0458 sec.

如果我们扩展子查询以生成大约。100,000 个数字（因此大约 274 年的日期价值），它在 0.0458 秒内运行。

Incidentally, this is a very portable technique that works with most databases with minor adjustments.

顺便说一句，这是一种非常便携的技术，只需稍作调整即可与大多数数据库一起使用。

SQL Fiddle example returning 1,000 days

SQL Fiddle 示例返回 1,000 天

Answer 2

回答by Stéphane

Here is another variation using views:

这是使用视图的另一种变体：

CREATE VIEW digits AS
  SELECT 0 AS digit 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;

CREATE VIEW numbers AS
  SELECT
    ones.digit + tens.digit * 10 + hundreds.digit * 100 + thousands.digit * 1000 AS number
  FROM
    digits as ones,
    digits as tens,
    digits as hundreds,
    digits as thousands;

CREATE VIEW dates AS
  SELECT
    SUBDATE(CURRENT_DATE(), number) AS date
  FROM
    numbers;

And then you can simply do (see how elegant it is?):

然后你可以简单地做（看看它有多优雅？）：

SELECT
  date
FROM
  dates
WHERE
  date BETWEEN '2010-01-20' AND '2010-01-24'
ORDER BY
  date

Update

更新

It is worth noting that you will only be able to generate past dates starting from the current date. If you want to generate any kind of dates range (past, future, and in between), you will have to use this view instead:

值得注意的是，您只能从当前日期开始生成过去的日期。如果要生成任何类型的日期范围（过去、未来和中间），则必须改用此视图：

CREATE VIEW dates AS
  SELECT
    SUBDATE(CURRENT_DATE(), number) AS date
  FROM
    numbers
  UNION ALL
  SELECT
    ADDDATE(CURRENT_DATE(), number + 1) AS date
  FROM
    numbers;

Answer 3

回答by Dmitry Gusev

Accepted answer didn't work for PostgreSQL (syntax error at or near "a").

接受的答案对 PostgreSQL 不起作用（“a”处或附近的语法错误）。

The way you do this in PostgreSQL is by using generate_seriesfunction, i.e.:

在 PostgreSQL 中这样做的方法是使用generate_series函数，即：

SELECT day::date
FROM generate_series('2010-01-20', '2010-01-24', INTERVAL '1 day') day;

    day
------------
 2010-01-20
 2010-01-21
 2010-01-22
 2010-01-23
 2010-01-24
(5 rows)

Answer 4

回答by Joshua

Using a recursive Common Table Expression (CTE), you can generate a list of dates, then select from it. Obviously you normally wouldn't want to create three million dates, so this just illustrates the possibilities. You could simply limit the date range inside the CTE and omit the where clause from the select statement using the CTE.

使用递归公用表表达式 (CTE)，您可以生成日期列表，然后从中进行选择。显然您通常不想创建三百万个日期，所以这只是说明了可能性。您可以简单地限制 CTE 内的日期范围，并从使用 CTE 的 select 语句中省略 where 子句。

with [dates] as (
    select convert(datetime, '1753-01-01') as [date] --start
    union all
    select dateadd(day, 1, [date])
    from [dates]
    where [date] < '9999-12-31' --end
)
select [date]
from [dates]
where [date] between '2013-01-01' and '2013-12-31'
option (maxrecursion 0)

On Microsoft SQL Server 2005, generating the CTE list of all possible dates took 1:08. Generating one hundred years took less than a second.

在 Microsoft SQL Server 2005 上，生成所有可能日期的 CTE 列表需要 1:08。生成一百年只用了不到一秒钟。

Answer 5

回答by SUHAIL AG

MSSQL Query

MSSQL 查询

select datetable.Date 
from (
    select DATEADD(day,-(a.a + (10 * b.a) + (100 * c.a)),getdate()) AS Date
    from (select 0 as a 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) as a

    cross join (select 0 as a 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) as b

    cross join (select 0 as a 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) as c
) datetable
where datetable.Date between '2014-01-20' and '2014-01-24' 
order by datetable.Date DESC

Output

输出

Date
-----
2014-01-23 12:35:25.250
2014-01-22 12:35:25.250
2014-01-21 12:35:25.250
2014-01-20 12:35:25.250

Answer 6

回答by Travis

For Access 2010- multiple steps required; I followed the same pattern as posted above, but thought I could help someone in Access. Worked great for me, I didn't have to keep a seeded table of dates.

对于 Access 2010- 需要多个步骤；我遵循与上面发布的相同的模式，但认为我可以在 Access 中帮助某人。对我来说效果很好，我不必保留种子日期表。

Create a table called DUAL (similar to how the Oracle DUAL table works)

创建一个名为 DUAL 的表（类似于 Oracle DUAL 表的工作方式）

ID (AutoNumber)
DummyColumn (Text)
Add one row values (1,"DummyRow")

ID（自动编号）
DummyColumn（文本）
添加一行值 (1,"DummyRow")

Create a query named "ZeroThru9Q"; manually enter the following syntax:

创建一个名为“ZeroThru9Q”的查询；手动输入以下语法：

SELECT 0 AS a
FROM dual
UNION ALL
SELECT 1
FROM dual
UNION ALL
SELECT 2
FROM dual
UNION ALL
SELECT 3
FROM dual
UNION ALL
SELECT 4
FROM dual
UNION ALL
SELECT 5
FROM dual
UNION ALL
SELECT 6
FROM dual
UNION ALL
SELECT 7
FROM dual
UNION ALL
SELECT 8
FROM dual
UNION ALL
SELECT 9
FROM dual;

Create a query named "TodayMinus1KQ" (for dates before today); manually enter the following syntax:

创建一个名为“TodayMinus1KQ”的查询（用于今天之前的日期）；手动输入以下语法：

SELECT date() - (a.a + (10 * b.a) + (100 * c.a)) AS MyDate
FROM
  (SELECT *
   FROM ZeroThru9Q) AS a,

  (SELECT *
   FROM ZeroThru9Q) AS b,

  (SELECT *
   FROM ZeroThru9Q) AS c

Create a query named "TodayPlus1KQ" (for dates after today); manually enter the following syntax:

创建一个名为“TodayPlus1KQ”的查询（用于今天之后的日期）；手动输入以下语法：

SELECT date() + (a.a + (10 * b.a) + (100 * c.a)) AS MyDate
FROM
  (SELECT *
   FROM ZeroThru9Q) AS a,

  (SELECT *
   FROM ZeroThru9Q) AS b,

  (SELECT *
   FROM ZeroThru9Q) AS c;

Create a union query named "TodayPlusMinus1KQ" (for dates +/- 1000 days):

创建一个名为“TodayPlusMinus1KQ”的联合查询（对于日期 +/- 1000 天）：

SELECT MyDate
FROM TodayMinus1KQ
UNION
SELECT MyDate
FROM TodayPlus1KQ;

Now you can use the query:

现在您可以使用查询：

SELECT MyDate
FROM TodayPlusMinus1KQ
WHERE MyDate BETWEEN #05/01/2014# and #05/30/2014#

Answer 7

回答by OMG Ponies

The old school solution for doing this without a loop/cursor is to create a NUMBERStable, which has a single Integer column with values starting at 1.

在没有循环/游标的情况下执行此操作的老派解决方案是创建一个NUMBERS表，该表具有单个 Integer 列，其值从 1 开始。

CREATE TABLE  `example`.`numbers` (
  `id` int(10) unsigned NOT NULL auto_increment,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

You need to populate the table with enough records to cover your needs:

您需要使用足够的记录填充表以满足您的需求：

INSERT INTO NUMBERS (id) VALUES (NULL);

Once you have the NUMBERStable, you can use:

一旦你有了NUMBERS桌子，你就可以使用：

SELECT x.start_date + INTERVAL n.id-1 DAY
  FROM NUMBERS n
  JOIN (SELECT STR_TO_DATE('2010-01-20', '%Y-%m-%d') AS start_date 
          FROM DUAL) x
 WHERE x.start_date + INTERVAL n.id-1 DAY <= '2010-01-24'

The absolute low-tech solution would be:

绝对的低技术解决方案是：

SELECT STR_TO_DATE('2010-01-20', '%Y-%m-%d')
 FROM DUAL
UNION ALL
SELECT STR_TO_DATE('2010-01-21', '%Y-%m-%d')
 FROM DUAL
UNION ALL
SELECT STR_TO_DATE('2010-01-22', '%Y-%m-%d')
 FROM DUAL
UNION ALL
SELECT STR_TO_DATE('2010-01-23', '%Y-%m-%d')
 FROM DUAL
UNION ALL
SELECT STR_TO_DATE('2010-01-24', '%Y-%m-%d')
 FROM DUAL

What would you use it for?

你会用它做什么？

To generate lists of dates or numbers in order to LEFT JOIN on to. You would to this in order to see where there are gaps in the data, because you are LEFT JOINing onto a list of sequencial data - null values will make it obvious where gaps exist.

生成日期或数字列表以便左连接。您会这样做是为了查看数据中存在差距的位置，因为您将左连接到序列数据列表 - 空值将使存在差距的地方变得显而易见。

Answer 8

回答by Márcio Souza Júnior

Procedure + temporary table:

程序+临时表：

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `days`(IN dateStart DATE, IN dateEnd DATE)
BEGIN

    CREATE TEMPORARY TABLE IF NOT EXISTS date_range (day DATE);

    WHILE dateStart <= dateEnd DO
      INSERT INTO date_range VALUES (dateStart);
      SET dateStart = DATE_ADD(dateStart, INTERVAL 1 DAY);
    END WHILE;

    SELECT * FROM date_range;
    DROP TEMPORARY TABLE IF EXISTS date_range;

END

Answer 9

回答by user3780177

thx Pentium10 - you made me join stackoverflow :) - this is my porting to msaccess - think it'll work on any version:

谢谢 Pentium10 - 你让我加入 stackoverflow :) - 这是我移植到 msaccess - 认为它可以在任何版本上运行：

SELECT date_value
FROM (SELECT a.espr1+(10*b.espr1)+(100*c.espr1) AS integer_value,
dateadd("d",integer_value,dateserial([start_year], [start_month], [start_day])) as date_value
FROM (select * from 
    (
    select top 1 "0" as espr1 from MSysObjects
    union all
    select top 1 "1" as espr2 from MSysObjects
    union all
    select top 1 "2" as espr3 from MSysObjects
    union all
    select top 1 "3" as espr4 from MSysObjects
    union all
    select top 1 "4" as espr5 from MSysObjects
    union all
    select top 1 "5" as espr6 from MSysObjects
    union all
    select top 1 "6" as espr7 from MSysObjects
    union all
    select top 1 "7" as espr8 from MSysObjects
    union all
    select top 1 "8" as espr9 from MSysObjects
    union all
    select top 1 "9" as espr9 from MSysObjects
    ) as a,
    (
    select top 1 "0" as espr1 from MSysObjects
    union all
    select top 1 "1" as espr2 from MSysObjects
    union all
    select top 1 "2" as espr3 from MSysObjects
    union all
    select top 1 "3" as espr4 from MSysObjects
    union all
    select top 1 "4" as espr5 from MSysObjects
    union all
    select top 1 "5" as espr6 from MSysObjects
    union all
    select top 1 "6" as espr7 from MSysObjects
    union all
    select top 1 "7" as espr8 from MSysObjects
    union all
    select top 1 "8" as espr9 from MSysObjects
    union all
    select top 1 "9" as espr9 from MSysObjects
    ) as b,
    (
    select top 1 "0" as espr1 from MSysObjects
    union all
    select top 1 "1" as espr2 from MSysObjects
    union all
    select top 1 "2" as espr3 from MSysObjects
    union all
    select top 1 "3" as espr4 from MSysObjects
    union all
    select top 1 "4" as espr5 from MSysObjects
    union all
    select top 1 "5" as espr6 from MSysObjects
    union all
    select top 1 "6" as espr7 from MSysObjects
    union all
    select top 1 "7" as espr8 from MSysObjects
    union all
    select top 1 "8" as espr9 from MSysObjects
    union all
    select top 1 "9" as espr9 from MSysObjects
    ) as c   
)  as d) 
WHERE date_value 
between dateserial([start_year], [start_month], [start_day]) 
and dateserial([end_year], [end_month], [end_day]);

referenced MSysObjects just 'cause access need a table countin' at least 1 record, in a from clause - any table with at least 1 record would do.

引用 MSysObjects 只是“因为访问需要一个表计数”至少 1 条记录，在 from 子句中 - 任何至少有 1 条记录的表都可以。

Answer 10

回答by Wolfgang Fahl

You'd like to get the a date range.

您想获取日期范围。

In your example you'd like to get the dates between '2010-01-20' and '2010-01-24'

在您的示例中，您希望获取 '2010-01-20' 和 '2010-01-24' 之间的日期

possible solution:

可能的解决方案：

 select date_add('2010-01-20', interval row day) from
 ( 
    SELECT @row := @row + 1 as row FROM 
    (select 0 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 6 union all select 7 union all select 8 union all select 9) t,
    (select 0 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 6 union all select 7 union all select 8 union all select 9) t2, 
    (select 0 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 6 union all select 7 union all select 8 union all select 9) t3, 
    (select 0 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 6 union all select 7 union all select 8 union all select 9) t4, 
    (SELECT @row:=-1) r
 ) sequence
 where date_add('2010-01-20', interval row day) <= '2010-01-24'

Explanation

解释

MySQL has a date_addfunction so

MySQL 有一个date_add函数所以

select date_add('2010-01-20', interval 1 day)

will give you

会给你

2010-01-21

The datedifffunction would let you know often you'd have to repeat this

该DATEDIFF功能将让你经常知道你不得不重复这个

select datediff('2010-01-24', '2010-01-20')

which returns

返回

Getting a list of dates in a date range boils down to creating a sequence of integer numbers see generate an integer sequence in MySQL

获取日期范围内的日期列表归结为创建整数序列参见在 MySQL 中生成整数序列

The most upvoted answer here has taken a similar approach as https://stackoverflow.com/a/2652051/1497139as a basis:

这里最受欢迎的答案采用了与https://stackoverflow.com/a/2652051/1497139类似的方法作为基础：

SELECT @row := @row + 1 as row FROM 
(select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t,
(select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t2, 
(select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t3, 
(select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t4, 
(SELECT @row:=0) r
limit 4

which will result in

这将导致

row
1.0
2.0
3.0
4.0

The rows can now be used to create a list of dates from the given start date. To include the start date we start with row -1;

这些行现在可用于从给定的开始日期创建日期列表。为了包括开始日期，我们从第 -1 行开始；

select date_add('2010-01-20', interval row day) from
 ( 
    SELECT @row := @row + 1 as row FROM 
    (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t,
    (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t2, 
    (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t3, 
    (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t4, 
    (SELECT @row:=-1) r
 ) sequence
 where date_add('2010-01-20', interval row day) <= '2010-01-24'

MySQL 从日期范围生成天数

提问by Pentium10

回答by RedFilter

回答by Stéphane

回答by Dmitry Gusev

回答by Joshua

回答by SUHAIL AG

回答by Travis

回答by OMG Ponies

What would you use it for?

你会用它做什么？

回答by Márcio Souza Júnior

回答by user3780177

回答by Wolfgang Fahl

相关推荐

最近更新

标签

MySQL 从日期范围生成天数

提问by Pentium10

回答by RedFilter

回答by Stéphane

回答by Dmitry Gusev

回答by Joshua

回答by SUHAIL AG

回答by Travis

回答by OMG Ponies

What would you use it for?

你会用它做什么？

回答by Márcio Souza Júnior

回答by user3780177

回答by Wolfgang Fahl

相关推荐

Visual Studio 2012 上的 mySQL 数据源

MySQL 为什么在我第一次授予用户权限时会创建“GRANT USAGE”？

MySQL 如何在2个不同的数据库上左连接2个表？

组合 MySQL 中的多个查询结果（按列）

相关推荐

最近更新

标签