logo标签列表

从日期范围生成天数

sqlmysqldatetimebetween
160

我想要运行一个查询，例如

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

并返回像这样的数据：

天数
----------
2010年01月20日
2010年01月21日
2010年01月22日
2010年01月23日
2010年01月24日
11
这个问题没有其他附属问题。上述问题是关于掌握SQL课程的问题。 - Pentium10
你只需要根据所选日期范围获取一个日期数组吗？ - Derek Adair
1
我在思考一个用法，为了找到你的问题... 如果你得到一个任务，在你的表中填写一些缺失的记录。而且你必须为每一天运行一个查询，我在想类似这样的东西 insert into table select ... as days date between '' and '' - Pentium10
16
一个使用示例是生成统计数据，并包括没有数据的日期行。如果要进行分组，实际上在SQL中生成所有信息并将其添加到所需的任何格式中会比将数据原样转储到语言中，并开始循环和添加空值更快。 - Nanne
1
@Nanne 这正是我保存这个问题的原因。我需要将上述内容 LEFT JOIN 到可能不存在某些日期数据中。 - Josh Diehl
涵盖了“获取两个日期之间的日期列表”（尽管这个问题的焦点更窄，具体而言，另一个问题是与关系型数据库无关的）。 - outis
30个回答
2

对于任何想将此保存为视图的人（MySQL不支持视图中嵌套的SELECT语句）：

create view zero_to_nine as
    select 0 as n 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 date_range as
    select curdate() - INTERVAL (a.n + (10 * b.n) + (100 * c.n)) DAY as date
    from zero_to_nine as a
    cross join zero_to_nine as b
    cross join zero_to_nine as c;

您可以随后执行以下操作。
select * from date_range

获取

date
---
2017-06-06
2017-06-05
2017-06-04
2017-06-03
2017-06-02
...
2

试试这个。

SELECT TO_DATE('20160210','yyyymmdd') - 1 + LEVEL AS start_day 
from DUAL
connect by level <= (TO_DATE('20160228','yyyymmdd') + 1) - TO_DATE('20160210','yyyymmdd') ;
2

正如许多出色的答案已经提到（或者至少暗示），一旦你有了一组要处理的数字，这个问题就很容易解决。

注意: 以下是T-SQL代码，但它只是我对这里和互联网上广泛提到的一般概念的特定实现。将代码转换为您选择的方言应该相对简单。

怎么做？考虑以下查询：

SELECT DATEADD(d, N, '0001-01-22')
FROM Numbers -- A table containing the numbers 0 through N
WHERE N <= 5;

以上查询生成了日期范围1/22/0001 - 1/27/0001，非常简单。在上述查询中有两个关键信息：起始日期为0001-01-22，偏移量为5。如果我们将这两个信息结合起来，就可以得到结束日期。因此，给定两个日期，生成一个范围可以分解如下：
- 找到两个给定日期之间的差异（偏移量），很容易： -- 返回125 SELECT ABS(DATEDIFF(d, '2014-08-22', '2014-12-25')) 使用ABS()确保日期顺序无关紧要。
- 生成一组有限的数字，也很容易： -- 返回数字0-2 SELECT N = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1 FROM(SELECT 'A' AS S UNION ALL SELECT 'A' UNION ALL SELECT 'A') 注意，我们实际上不关心我们在这里选择什么。我们只需要一个集合来处理，以便计算其中的行数。我个人使用TVF，有些人使用CTE，还有些人使用数字表，你明白我的意思。我主张使用最高效的解决方案，同时也要理解它。
结合这两种方法将解决我们的问题。
DECLARE @date1 DATE = '9001-11-21';
DECLARE @date2 DATE = '9001-11-23';

SELECT D = DATEADD(d, N, @date1)
FROM (
    SELECT N = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1
    FROM (SELECT 'A' AS S UNION ALL SELECT 'A' UNION ALL SELECT 'A') S
) Numbers
WHERE N <= ABS(DATEDIFF(d, @date1, @date2));

上面的例子是可怕的代码，但演示了所有内容如何结合在一起。

更有趣

我经常需要做这种事情，所以我将逻辑封装到两个TVF中。第一个生成一系列数字，第二个使用此功能生成一系列日期。数学是为了确保输入顺序无关，并且因为我想使用GenerateRangeSmallInt中提供的所有数字范围。

以下函数需要约16ms的CPU时间才能返回最大范围为65536的日期。

CREATE FUNCTION dbo.GenerateRangeDate (   
    @date1 DATE,   
    @date2 DATE   
)   
RETURNS TABLE
WITH SCHEMABINDING   
AS   
RETURN (
    SELECT D = DATEADD(d, N + 32768, CASE WHEN @date1 <= @date2 THEN @date1 ELSE @date2 END)
    FROM dbo.GenerateRangeSmallInt(-32768, ABS(DATEDIFF(d, @date1, @date2)) - 32768)
);

GO

CREATE FUNCTION dbo.GenerateRangeSmallInt (
    @num1 SMALLINT = -32768
  , @num2 SMALLINT = 32767
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN (
    WITH Numbers(N) AS (
        SELECT N FROM(VALUES
            (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 16
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 32
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 48
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 64
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 80
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 96
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 112
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 128
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 144
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 160
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 176
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 192
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 208
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 224
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 240
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 256
        ) V (N)
    )
    SELECT TOP(ABS(CAST(@num1 AS INT) - CAST(@num2 AS INT)) + 1)
           N = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) + CASE WHEN @num1 <= @num2 THEN @num1 ELSE @num2 END - 1
    FROM Numbers A
       , Numbers B
);
2

您希望获取一个日期范围。

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

可能的解决方案:

 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'

说明

MySQL有一个date_add函数，可以用于日期时间计算。

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

将会为您提供

2010-01-21

datediff 函数可以告诉您需要多久重复此操作。

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

该函数返回

 4

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

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

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

这将导致
row
1.0
2.0
3.0
4.0

现在可以使用这些行来创建从给定开始日期开始的日期列表。为了包括开始日期，我们从行-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'
2
在AWS MySQL中，更通用的答案是：
select datetable.Date
from (
    select date_format(adddate(now(),-(a.a + (10 * b.a) + (100 * c.a))),'%Y-%m-%d') 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 now() - INTERVAL 14 Day and Now()
order by datetable.Date DESC
1

生成两个日期字段之间的日期

如果您熟悉SQL CTE查询，则此解决方案将帮助您解决问题

以下是示例

我们有一个表中的日期

表名：“testdate”

STARTDATE   ENDDATE
10/24/2012  10/24/2012
10/27/2012  10/29/2012
10/30/2012  10/30/2012

需要的结果：

STARTDATE
10/24/2012
10/27/2012
10/28/2012
10/29/2012
10/30/2012

解决方案：

WITH CTE AS
  (SELECT DISTINCT convert(varchar(10),StartTime, 101) AS StartTime,
                   datediff(dd,StartTime, endTime) AS diff
   FROM dbo.testdate
   UNION ALL SELECT StartTime,
                    diff - 1 AS diff
   FROM CTE
   WHERE diff<> 0)
SELECT DISTINCT DateAdd(dd,diff, StartTime) AS StartTime
FROM CTE

说明：CTE递归查询解释

  • 查询的第一部分：

    SELECT DISTINCT convert(varchar(10), StartTime, 101) AS StartTime, datediff(dd, StartTime, endTime) AS diff FROM dbo.testdate

    解释：第一列是“开始日期”，第二列是开始和结束日期之间的天数差异，并将其视为“diff”列。

  • 查询的第二部分：

    UNION ALL SELECT StartTime, diff-1 AS diff FROM CTE WHERE diff<>0

    解释：Union all将继承上述查询的结果，直到结果为空。因此，“StartTime”结果从生成的CTE查询中继承，并从diff中减去1，因此它看起来像3、2和1，直到0。

例如：

STARTDATE   DIFF
10/24/2012  0
10/27/2012  0
10/27/2012  1
10/27/2012  2
10/30/2012  0

结果规范

STARTDATE       Specification
10/24/2012  --> From Record 1
10/27/2012  --> From Record 2
10/27/2012  --> From Record 2
10/27/2012  --> From Record 2
10/30/2012  --> From Record 3

  • 查询的第三部分

    SELECT DISTINCT DateAdd(dd,diff, StartTime) AS StartTime FROM CTE

    它将在“startdate”中添加“diff”天，因此结果应如下所示

结果

STARTDATE
10/24/2012
10/27/2012
10/28/2012
10/29/2012
10/30/2012
1

比接受的答案更短，但思路相同：

(SELECT TRIM('2016-01-05' + INTERVAL a + b DAY) date
FROM
(SELECT 0 a UNION SELECT 1 a UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
UNION SELECT 8 UNION SELECT 9 ) d,
(SELECT 0 b UNION SELECT 10 UNION SELECT 20
UNION SELECT 30 UNION SELECT 40) m
WHERE '2016-01-05' + INTERVAL a + b DAY  <=  '2016-01-21')
1

如果你需要超过几天的时间，你需要一个表格。

在MySQL中创建日期范围

然后，

select from days.day, count(mytable.field) as fields from days left join mytable on day=date where date between x and y;
3
为什么您要发布这个内容？因为上面的回复并不需要表格，已经提供了解决方案。请问需要翻译成哪种语言呢？ - Pentium10
1

动态生成这些日期是个好主意。但是，如果涉及到很大的范围，我会感到不太自在，所以我最终采用了以下解决方案：

  1. 创建一个表"DatesNumbers"，用于保存用于日期计算的数字：
CREATE TABLE DatesNumbers (
    i MEDIUMINT NOT NULL,
    PRIMARY KEY (i)
)
COMMENT='Used by Dates view'
;

使用上述技术填充表格，数字范围为-59999到40000。该范围将给出从当前日期前59999天（约164年）到后109年的日期。
INSERT INTO DatesNumbers
SELECT 
    a.i + (10 * b.i) + (100 * c.i) + (1000 * d.i) + (10000 * e.i) - 59999 AS i
FROM 
  (SELECT 0 AS i 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,
  (SELECT 0 AS i 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,
  (SELECT 0 AS i 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,
  (SELECT 0 AS i 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,
  (SELECT 0 AS i 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 e
;

创建了一个名为“Dates”的视图：
SELECT
      i,
      CURRENT_DATE() + INTERVAL i DAY AS Date
FROM
    DatesNumbers

就是这样。

  • (+) 查询易读
  • (+) 不会在查询时生成即时数字
  • (+) 提供过去和未来的日期，并且没有像this post中的视图联合。
  • (+) 只能使用WHERE i < 0或WHERE i > 0（PK）筛选“仅限过去”或“仅限未来”日期
  • (-) 使用了“临时”表格和视图
1

使用递归公共表达式，针对MySQL 8.0.1和MariaDB 10.2.2的另一种解决方案：

with recursive dates as (
    select '2010-01-20' as date
    union all
    select date + interval 1 day from dates where date < '2010-01-24'
)
select * from dates;
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接