当前位置： 文江博客话题详情

获取两个日期之间的日期列表

发布于 2024-07-12 09:21:02 字数 397 浏览 9 评论 0原文

使用标准 mysql 函数有一种方法可以编写一个查询，该查询将返回两个日期之间的天数列表。

例如，给定 2009-01-01 和 2009-01-13 它将返回一个包含以下值的一列表：

 2009-01-01 
 2009-01-02 
 2009-01-03
 2009-01-04 
 2009-01-05
 2009-01-06
 2009-01-07
 2009-01-08 
 2009-01-09
 2009-01-10
 2009-01-11
 2009-01-12
 2009-01-13

编辑：看来我还不清楚。 我想生成这个列表。 我将值存储在数据库中（按日期时间），但希望将它们聚合在左外连接上到上面的日期列表（我预计该连接的右侧部分为空，并且将处理此问题） ）。

原文

Using standard mysql functions is there a way to write a query that will return a list of days between two dates.

eg given 2009-01-01 and 2009-01-13 it would return a one column table with the values:

 2009-01-01 
 2009-01-02 
 2009-01-03
 2009-01-04 
 2009-01-05
 2009-01-06
 2009-01-07
 2009-01-08 
 2009-01-09
 2009-01-10
 2009-01-11
 2009-01-12
 2009-01-13

Edit: It appears I have not been clear. I want to GENERATE this list. I have values stored in the database (by datetime) but want them to be aggregated on a left outer join to a list of dates as above (I am expecting null from the right side of some of this join for some days and will handle this).

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（24）

七禾 2024-07-19 09:21:04

对于 Access（或任何 SQL 语言）

  1. 创建一个包含 2 个字段的表，我们将此表称为 tempRunDates：
    --字段fromDate 和toDate
    --然后仅插入 1 条记录，该记录具有开始日期和结束日期。

  2. 创建另一个表：Time_Day_Ref
    --将日期列表（在 Excel 中制作列表很容易）导入到此表中。
    -- 在我的例子中，字段名称是 Greg_Dt，代表公历日期
    --我的列表是从 2009 年 1 月 1 日到 2020 年 1 月 1 日。

  3. 运行查询：

    选择 Time_Day_Ref.GREG_DT 
  来自 tempRunDates、Time_Day_Ref 
  WHERE Time_Day_Ref.greg_dt>=tempRunDates.fromDate 且 greg_dt<=tempRunDates.toDate;

简单！

For Access (or any SQL language)

  1. Create one table that has 2 fields, we'll call this table tempRunDates:

    --Fields fromDate and toDate

    --Then insert only 1 record, that has the start date and the end date.

  2. Create another table: Time_Day_Ref
    --Import a list of dates (make list in excel is easy) into this table.
    --The field name in my case is Greg_Dt, for Gregorian Date
    --I made my list from jan 1 2009 through jan 1 2020.

  3. Run the query:

    SELECT Time_Day_Ref.GREG_DT
FROM tempRunDates, Time_Day_Ref
WHERE Time_Day_Ref.greg_dt>=tempRunDates.fromDate And greg_dt<=tempRunDates.toDate;

Easy!

回复 原文
追我者格杀勿论 2024-07-19 09:21:04
CREATE FUNCTION [dbo].[_DATES]
(
    @startDate DATETIME,
    @endDate DATETIME
)
RETURNS 
@DATES TABLE(
    DATE1 DATETIME
)
AS
BEGIN
    WHILE @startDate <= @endDate
    BEGIN 
        INSERT INTO @DATES (DATE1)
            SELECT @startDate   
    SELECT @startDate = DATEADD(d,1,@startDate) 
    END
RETURN
END

CREATE FUNCTION [dbo].[_DATES]
(
    @startDate DATETIME,
    @endDate DATETIME
)
RETURNS 
@DATES TABLE(
    DATE1 DATETIME
)
AS
BEGIN
    WHILE @startDate <= @endDate
    BEGIN 
        INSERT INTO @DATES (DATE1)
            SELECT @startDate   
    SELECT @startDate = DATEADD(d,1,@startDate) 
    END
RETURN
END
回复 原文
世界和平 2024-07-19 09:21:04

你可以用这个

SELECT CAST(cal.date_list AS DATE) day_year
FROM (
  SELECT SUBDATE('2019-01-01', INTERVAL 1 YEAR) + INTERVAL xc DAY AS date_list
  FROM (
        SELECT @xi:=@xi+1 as xc from
        (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) xc1,
        (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) xc2,
        (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) xc3,
        (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) xc4,
        (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) xc5,
        (SELECT @xi:=-1) xc0
    ) xxc1
) cal
WHERE cal.date_list BETWEEN '2019-01-01' AND '2019-12-31'
ORDER BY cal.date_list DESC;

You can use this

SELECT CAST(cal.date_list AS DATE) day_year
FROM (
  SELECT SUBDATE('2019-01-01', INTERVAL 1 YEAR) + INTERVAL xc DAY AS date_list
  FROM (
        SELECT @xi:=@xi+1 as xc from
        (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) xc1,
        (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) xc2,
        (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) xc3,
        (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) xc4,
        (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) xc5,
        (SELECT @xi:=-1) xc0
    ) xxc1
) cal
WHERE cal.date_list BETWEEN '2019-01-01' AND '2019-12-31'
ORDER BY cal.date_list DESC;
回复 原文
傾旎 2024-07-19 09:21:04

通常，人们会使用您通常为此目的而保留的辅助数字表，并对此进行一些变化：

SELECT *
FROM (
    SELECT DATEADD(d, number - 1, '2009-01-01') AS dt
    FROM Numbers
    WHERE number BETWEEN 1 AND DATEDIFF(d, '2009-01-01', '2009-01-13') + 1
) AS DateRange
LEFT JOIN YourStuff
    ON DateRange.dt = YourStuff.DateColumn

我已经看到了表值函数的变化等。

您还可以保留一个永久的日期列表。 我们的数据仓库中有它以及一天中的时间列表。

Typically one would use an auxiliary numbers table you usually keep around for just this purpose with some variation on this:

SELECT *
FROM (
    SELECT DATEADD(d, number - 1, '2009-01-01') AS dt
    FROM Numbers
    WHERE number BETWEEN 1 AND DATEDIFF(d, '2009-01-01', '2009-01-13') + 1
) AS DateRange
LEFT JOIN YourStuff
    ON DateRange.dt = YourStuff.DateColumn

I've seen variations with table-valued functions, etc.

You can also keep a permanent list of dates. We have that in our data warehouse as well as a list of times of day.

回复 原文
溇涏 2024-07-19 09:21:04

我们在 HRMS 系统中使用了它，您会发现它很有用

SELECT CAST(DAYNAME(daydate) as CHAR) as dayname,daydate
    FROM
    (select CAST((date_add('20110101', interval H.i*100 + T.i*10 + U.i day) )as DATE) as daydate
      from erp_integers as H
    cross
      join erp_integers as T
    cross
      join erp_integers as U
     where date_add('20110101', interval H.i*100 + T.i*10 + U.i day ) <= '20110228'
    order
        by daydate ASC
        )Days

We used this in our HRMS System you will find it useful

SELECT CAST(DAYNAME(daydate) as CHAR) as dayname,daydate
    FROM
    (select CAST((date_add('20110101', interval H.i*100 + T.i*10 + U.i day) )as DATE) as daydate
      from erp_integers as H
    cross
      join erp_integers as T
    cross
      join erp_integers as U
     where date_add('20110101', interval H.i*100 + T.i*10 + U.i day ) <= '20110228'
    order
        by daydate ASC
        )Days
回复 原文
寒冷纷飞旳雪 2024-07-19 09:21:04

该解决方案适用于 MySQL 5.0
创建一个表 - mytable。
架构并不重要。 重要的是其中的行数。
因此，您可以仅保留一列 INT 类型的列，其中包含 10 行，值 - 1 到 10。

SQL：

set @tempDate=date('2011-07-01') - interval 1 day;
select
date(@tempDate := (date(@tempDate) + interval 1 day)) as theDate
from mytable x,mytable y
group by theDate
having theDate <= '2011-07-31';

限制：
上述查询返回的最大日期数将为
(mytable 中的行数)*(mytable 中的行数) = 10*10 = 100。

您可以通过更改 sql 中的表单部分来增加此范围：
来自 mytable x、mytable y、mytable z
因此，范围为 10*10*10 =1000 等等。

This solution is working with MySQL 5.0
Create a table - mytable.
The schema does not material. What matters is the number of rows in it.
So, you can keep just one column of type INT with 10 rows, values - 1 to 10.

SQL:

set @tempDate=date('2011-07-01') - interval 1 day;
select
date(@tempDate := (date(@tempDate) + interval 1 day)) as theDate
from mytable x,mytable y
group by theDate
having theDate <= '2011-07-31';

Limitation:
The maximum number of dates returned by above query will be
(rows in mytable)*(rows in mytable) = 10*10 = 100.

You can increase this range by changing form part in sql:
from mytable x,mytable y, mytable z
So, the range be 10*10*10 =1000 and so on.

回复 原文
不语却知心 2024-07-19 09:21:04

没有临时表 | 无需手续 | 无函数

下面的示例显示一个月的日期范围 - 从同月的第一天到最后一天。 您可以通过使用 @e `INTERVAL` 值来更改日期范围。

SET @s := CAST('2021-12-01' AS DATE); -- start at 2021-12-01
SET @e := @s + INTERVAL 1 MONTH - INTERVAL 1 DAY; -- ends at 2021-12-31

WITH RECURSIVE d_range AS
(
       SELECT @s AS gdate -- generated dates
       UNION ALL
       SELECT gdate + INTERVAL 1 day
       FROM   d_range
       WHERE  gdate < @e
)
SELECT *
FROM   d_range;

结果：

| gdate       |
+------------+
| 2021-12-01 |
| 2021-12-02 |
| 2021-12-03 |
| 2021-12-04 |
| 2021-12-05 |
| 2021-12-06 |
| 2021-12-07 |
| 2021-12-08 |
| 2021-12-09 |
| 2021-12-10 |
| 2021-12-11 |
| 2021-12-12 |
| 2021-12-13 |
| 2021-12-14 |
| 2021-12-15 |
| 2021-12-16 |
| 2021-12-17 |
| 2021-12-18 |
| 2021-12-19 |
| 2021-12-20 |
| 2021-12-21 |
| 2021-12-22 |
| 2021-12-23 |
| 2021-12-24 |
| 2021-12-25 |
| 2021-12-26 |
| 2021-12-27 |
| 2021-12-28 |
| 2021-12-29 |
| 2021-12-30 |
| 2021-12-31 |
+------------+
-> 31 rows in set (0.037 sec)

注意。 此查询在那里使用 RECURSION，因为在选择较长的日期范围时，31 rows in set (0.037 sec) 速度相当慢
它会变得更慢。

No temp table | No procedure | No functions

The example below present one month date range - from day 1 to the last day of the same month. You can change the date range by playing with @e `INTERVAL` values.

SET @s := CAST('2021-12-01' AS DATE); -- start at 2021-12-01
SET @e := @s + INTERVAL 1 MONTH - INTERVAL 1 DAY; -- ends at 2021-12-31

WITH RECURSIVE d_range AS
(
       SELECT @s AS gdate -- generated dates
       UNION ALL
       SELECT gdate + INTERVAL 1 day
       FROM   d_range
       WHERE  gdate < @e
)
SELECT *
FROM   d_range;

Result:

| gdate       |
+------------+
| 2021-12-01 |
| 2021-12-02 |
| 2021-12-03 |
| 2021-12-04 |
| 2021-12-05 |
| 2021-12-06 |
| 2021-12-07 |
| 2021-12-08 |
| 2021-12-09 |
| 2021-12-10 |
| 2021-12-11 |
| 2021-12-12 |
| 2021-12-13 |
| 2021-12-14 |
| 2021-12-15 |
| 2021-12-16 |
| 2021-12-17 |
| 2021-12-18 |
| 2021-12-19 |
| 2021-12-20 |
| 2021-12-21 |
| 2021-12-22 |
| 2021-12-23 |
| 2021-12-24 |
| 2021-12-25 |
| 2021-12-26 |
| 2021-12-27 |
| 2021-12-28 |
| 2021-12-29 |
| 2021-12-30 |
| 2021-12-31 |
+------------+
-> 31 rows in set (0.037 sec)

Note. This query uses RECURSION there for it is quite slow 31 rows in set (0.037 sec), when selecting longer date ranges
it will get even slower.

回复 原文
三寸金莲 2024-07-19 09:21:04

我会使用与此类似的内容：

DECLARE @DATEFROM AS DATETIME
DECLARE @DATETO AS DATETIME
DECLARE @HOLDER TABLE(DATE DATETIME)

SET @DATEFROM = '2010-08-10'
SET @DATETO = '2010-09-11'

INSERT INTO
    @HOLDER
        (DATE)
VALUES
    (@DATEFROM)

WHILE @DATEFROM < @DATETO
BEGIN

    SELECT @DATEFROM = DATEADD(D, 1, @DATEFROM)
    INSERT 
    INTO
        @HOLDER
            (DATE)
    VALUES
        (@DATEFROM)
END

SELECT 
    DATE
FROM
    @HOLDER

然后 @HOLDER 变量表保存这两个日期之间按天递增的所有日期，准备好加入您心中的内容。

I would use something similar to this:

DECLARE @DATEFROM AS DATETIME
DECLARE @DATETO AS DATETIME
DECLARE @HOLDER TABLE(DATE DATETIME)

SET @DATEFROM = '2010-08-10'
SET @DATETO = '2010-09-11'

INSERT INTO
    @HOLDER
        (DATE)
VALUES
    (@DATEFROM)

WHILE @DATEFROM < @DATETO
BEGIN

    SELECT @DATEFROM = DATEADD(D, 1, @DATEFROM)
    INSERT 
    INTO
        @HOLDER
            (DATE)
    VALUES
        (@DATEFROM)
END

SELECT 
    DATE
FROM
    @HOLDER

Then the @HOLDER Variable table holds all the dates incremented by day between those two dates, ready to join at your hearts content.

回复 原文
始于初秋 2024-07-19 09:21:04

我已经为此奋斗了很长一段时间。 由于这是我搜索解决方案时在 Google 上的第一次点击，因此让我发布到目前为止我所得到的结果。

SET @d := '2011-09-01';
SELECT @d AS d, cast( @d := DATE_ADD( @d , INTERVAL 1 DAY ) AS DATE ) AS new_d
  FROM [yourTable]
  WHERE @d <= '2012-05-01';

将 [yourTable] 替换为数据库中的表。 技巧是您选择的表中的行数必须 >= 您想要返回的日期数。 我尝试使用表占位符 DUAL，但它只会返回一行。

I've been fighting with this for quite a while. Since this is the first hit on Google when I searched for the solution, let me post where I've gotten so far.

SET @d := '2011-09-01';
SELECT @d AS d, cast( @d := DATE_ADD( @d , INTERVAL 1 DAY ) AS DATE ) AS new_d
  FROM [yourTable]
  WHERE @d <= '2012-05-01';

Replace [yourTable] with a table from your database. The trick is that the number of rows in the table you select must be >= the number of dates you want to be returned. I tried using the table placeholder DUAL, but it would only return one single row.

回复 原文
弥繁 2024-07-19 09:21:04
select * from table_name where col_Date between '2011/02/25' AND DATEADD(s,-1,DATEADD(d,1,'2011/02/27'))

在这里，首先在当前结束日期上添加一天，即 2011-02-28 00:00:00，然后减去一秒以使结束日期为 2011-02-27 23:59:59。 通过这样做，您可以获得给定间隔之间的所有日期。

输出：
2011/02/25
2011/02/26
2011/02/27

select * from table_name where col_Date between '2011/02/25' AND DATEADD(s,-1,DATEADD(d,1,'2011/02/27'))

Here, first add a day to the current endDate, it will be 2011-02-28 00:00:00, then you subtract one second to make the end date 2011-02-27 23:59:59. By doing this, you can get all the dates between the given intervals.

output:
2011/02/25
2011/02/26
2011/02/27

回复 原文
秋风の叶未落 2024-07-19 09:21:04
DELIMITER $  
CREATE PROCEDURE popula_calendario_controle()
   BEGIN
      DECLARE a INT Default 0;
      DECLARE first_day_of_year DATE;
      set first_day_of_year = CONCAT(DATE_FORMAT(curdate(),'%Y'),'-01-01');
      one_by_one: LOOP
         IF dayofweek(adddate(first_day_of_year,a)) <> 1 THEN
            INSERT INTO calendario.controle VALUES(null,150,adddate(first_day_of_year,a),adddate(first_day_of_year,a),1);
         END IF;
         SET a=a+1;
         IF a=365 THEN
            LEAVE one_by_one;
         END IF;
      END LOOP one_by_one;
END $

此过程将插入从年初至今的所有日期，只需替换“开始”和“结束”的日期，就可以开始了！

DELIMITER $  
CREATE PROCEDURE popula_calendario_controle()
   BEGIN
      DECLARE a INT Default 0;
      DECLARE first_day_of_year DATE;
      set first_day_of_year = CONCAT(DATE_FORMAT(curdate(),'%Y'),'-01-01');
      one_by_one: LOOP
         IF dayofweek(adddate(first_day_of_year,a)) <> 1 THEN
            INSERT INTO calendario.controle VALUES(null,150,adddate(first_day_of_year,a),adddate(first_day_of_year,a),1);
         END IF;
         SET a=a+1;
         IF a=365 THEN
            LEAVE one_by_one;
         END IF;
      END LOOP one_by_one;
END $

this procedure will insert all dates from the beginning of the year till now, just substitue the days of the "start" and "end", and you are ready to go!

回复 原文
九命猫 2024-07-19 09:21:04

我需要一个包含两个日期之间所有月份的列表以进行统计。 这两个日期是订阅的开始日期和结束日期。
因此列表显示所有月份以及每月的订阅量。

MYSQL

CREATE PROCEDURE `get_amount_subscription_per_month`()
BEGIN
   -- Select the ultimate start and enddate from subscribers
   select @startdate := min(DATE_FORMAT(a.startdate, "%Y-%m-01")), 
          @enddate := max(DATE_FORMAT(a.enddate, "%Y-%m-01")) + interval 1 MONTH
   from subscription a;

   -- Tmp table with all months (dates), you can always format them with DATE_FORMAT) 
   DROP TABLE IF EXISTS tmp_months;
   create temporary table tmp_months (
      year_month date,
      PRIMARY KEY (year_month)
   );


   set @tempDate=@startdate;  #- interval 1 MONTH;

   -- Insert every month in tmp table
   WHILE @tempDate <= @enddate DO
     insert into tmp_months (year_month) values (@tempDate);
     set @tempDate = (date(@tempDate) + interval 1 MONTH);
   END WHILE;

   -- All months
   select year_month from tmp_months;

   -- If you want the amount of subscription per month else leave it out
   select mnd.year_month, sum(subscription.amount) as subscription_amount
   from tmp_months mnd
   LEFT JOIN subscription ON mnd.year_month >= DATE_FORMAT(subscription.startdate, "%Y-%m-01") and mnd.year_month <= DATE_FORMAT(subscription.enddate, "%Y-%m-01")
   GROUP BY mnd.year_month;

 END

I needed a list with all months between 2 dates for statistics. The 2 dates are the start and enddate from a subscription.
So the list shows all months and the amount of subscriptions per month.

MYSQL

CREATE PROCEDURE `get_amount_subscription_per_month`()
BEGIN
   -- Select the ultimate start and enddate from subscribers
   select @startdate := min(DATE_FORMAT(a.startdate, "%Y-%m-01")), 
          @enddate := max(DATE_FORMAT(a.enddate, "%Y-%m-01")) + interval 1 MONTH
   from subscription a;

   -- Tmp table with all months (dates), you can always format them with DATE_FORMAT) 
   DROP TABLE IF EXISTS tmp_months;
   create temporary table tmp_months (
      year_month date,
      PRIMARY KEY (year_month)
   );


   set @tempDate=@startdate;  #- interval 1 MONTH;

   -- Insert every month in tmp table
   WHILE @tempDate <= @enddate DO
     insert into tmp_months (year_month) values (@tempDate);
     set @tempDate = (date(@tempDate) + interval 1 MONTH);
   END WHILE;

   -- All months
   select year_month from tmp_months;

   -- If you want the amount of subscription per month else leave it out
   select mnd.year_month, sum(subscription.amount) as subscription_amount
   from tmp_months mnd
   LEFT JOIN subscription ON mnd.year_month >= DATE_FORMAT(subscription.startdate, "%Y-%m-01") and mnd.year_month <= DATE_FORMAT(subscription.enddate, "%Y-%m-01")
   GROUP BY mnd.year_month;

 END
回复 原文
梦醒灬来后我 2024-07-19 09:21:04

无函数，无过程

SET @s := '2020-01-01';
SET @e := @s + INTERVAL 1 YEAR - INTERVAL 1 DAY; -- set end date to select

SELECT CAST((DATE(@s)+INTERVAL (H+T+U) DAY) AS DATE) d -- generate a list of 400 days starting from @s date so @e can not be more then @s + 
FROM ( SELECT 0 H
    UNION ALL SELECT 100 UNION ALL SELECT 200 UNION ALL SELECT 300
  ) H CROSS JOIN ( SELECT 0 T
    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
  ) T CROSS JOIN ( SELECT 0 U
    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
  ) U
 -- generated lenght of date list can be calculated as fallow 1 * H(3 + 1) * T(9 + 1) * U(9 + 1) = 400
 -- it is crucial to preserve the numbering convention as 1, 2 ... 20, 30..., 100, 200, ... to retrieve chronological date selection
 -- add more UNION 400, 500, 600, ... H(6 + 1) if you want to select from more than 700 days!
WHERE
  (DATE(@s+INTERVAL (H+T+U) DAY)) <= DATE((@e))
ORDER BY d;

UNIONS 定义可供选择的可用日期的长度和范围。 因此，请务必确保其记录足够多。

SELECT 1 * (3 + 1) * (9 + 1) * (9 + 1); -- 400 days starting from from @s

基于这篇文章< br>
经过测试并按预期工作

No Function, No Procedures

SET @s := '2020-01-01';
SET @e := @s + INTERVAL 1 YEAR - INTERVAL 1 DAY; -- set end date to select

SELECT CAST((DATE(@s)+INTERVAL (H+T+U) DAY) AS DATE) d -- generate a list of 400 days starting from @s date so @e can not be more then @s + 
FROM ( SELECT 0 H
    UNION ALL SELECT 100 UNION ALL SELECT 200 UNION ALL SELECT 300
  ) H CROSS JOIN ( SELECT 0 T
    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
  ) T CROSS JOIN ( SELECT 0 U
    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
  ) U
 -- generated lenght of date list can be calculated as fallow 1 * H(3 + 1) * T(9 + 1) * U(9 + 1) = 400
 -- it is crucial to preserve the numbering convention as 1, 2 ... 20, 30..., 100, 200, ... to retrieve chronological date selection
 -- add more UNION 400, 500, 600, ... H(6 + 1) if you want to select from more than 700 days!
WHERE
  (DATE(@s+INTERVAL (H+T+U) DAY)) <= DATE((@e))
ORDER BY d;

UNIONS define the length and range of available dates to elect from. So always make sure to make it log enough.

SELECT 1 * (3 + 1) * (9 + 1) * (9 + 1); -- 400 days starting from from @s

Based on this article
Tested and working as expected

回复 原文
怀中猫帐中妖 2024-07-19 09:21:04

改进@brad答案，我使用了带有光标的CTE：

DECLARE cursorPeriod CURSOR FOR SELECT * FROM (
   WITH RECURSIVE period as
        (
             SELECT '2019-01-01' as dt
             UNION
             SELECT DATE_ADD(period.dt, INTERVAL 1 DAY) FROM period WHERE DATE_ADD(period.dt, INTERVAL 1 DAY) <= '2019-04-30'
        )
   SELECT DATE(dt) FROM period ORDER BY dt ) AS derived_table;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET exit_loop = TRUE;

Improving on @brad answer, I used a CTE with a cursor:

DECLARE cursorPeriod CURSOR FOR SELECT * FROM (
   WITH RECURSIVE period as
        (
             SELECT '2019-01-01' as dt
             UNION
             SELECT DATE_ADD(period.dt, INTERVAL 1 DAY) FROM period WHERE DATE_ADD(period.dt, INTERVAL 1 DAY) <= '2019-04-30'
        )
   SELECT DATE(dt) FROM period ORDER BY dt ) AS derived_table;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET exit_loop = TRUE;
回复 原文
土豪 2024-07-19 09:21:04

便携式解决方案

-- Generate 
WITH 
tdates AS (
  WITH   t19 AS (
          SELECT  0 i 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 )    
   SELECT  adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) datei 
   FROM  t19 AS t0, t19 AS t1, t19 AS t2, t19 AS t3,  t19 AS t4 
)

select * from tdates
where datei between '2012-02-10' and '2012-02-15'

Portable solution

-- Generate 
WITH 
tdates AS (
  WITH   t19 AS (
          SELECT  0 i 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 )    
   SELECT  adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) datei 
   FROM  t19 AS t0, t19 AS t1, t19 AS t2, t19 AS t3,  t19 AS t4 
)

select * from tdates
where datei between '2012-02-10' and '2012-02-15'
回复 原文
烟花肆意 2024-07-19 09:21:04

通过递归我确实喜欢这样。

with recursive cte as (select CURDATE() as calendar_date
                        union all
                        select date_add(calendar_date, interval 1 day
                        ) as calendar_date from cte     
    where calendar_date < date_add(CURDATE(),interval 30 day)
)
select
calendar_date
from cte

With recursive i did like this.

with recursive cte as (select CURDATE() as calendar_date
                        union all
                        select date_add(calendar_date, interval 1 day
                        ) as calendar_date from cte     
    where calendar_date < date_add(CURDATE(),interval 30 day)
)
select
calendar_date
from cte
回复 原文
他是夢罘是命 2024-07-19 09:21:04

创建一个带有两个参数 a_begin 和 a_end 的存储过程。
在其中创建一个名为 t 的临时表，声明变量 d，将 a_begin 分配给 d，然后运行 ​​WHILE 循环将 d INSERT 插入 t 并调用 ADDDATE< /code> 函数增加值 d。 最后SELECT * FROM t。

Create a stored procedure which takes two parameters a_begin and a_end.
Create a temporary table within it called t, declare a variable d, assign a_begin to d, and run a WHILE loop INSERTing d into t and calling ADDDATE function to increment the value d. Finally SELECT * FROM t.

回复 原文
无人接听 2024-07-19 09:21:04

我正在使用服务器版本：5.7.11-log MySQL Community Server (GPL)

现在我们将通过简单的方式解决这个问题。

我现在创建了一个名为“datetable”的表

mysql> describe datetable;
+---------+---------+------+-----+---------+-------+
| Field   | Type    | Null | Key | Default | Extra |
+---------+---------+------+-----+---------+-------+
| colid   | int(11) | NO   | PRI | NULL    |       |
| coldate | date    | YES  |     | NULL    |       |
+---------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

，我们将看到其中插入的记录。

mysql> select * from datetable;
+-------+------------+
| colid | coldate    |
+-------+------------+
|   101 | 2015-01-01 |
|   102 | 2015-05-01 |
|   103 | 2016-01-01 |
+-------+------------+
3 rows in set (0.00 sec)

这里我们的查询是获取两个日期内的记录而不是这些日期。

mysql> select * from datetable where coldate > '2015-01-01' and coldate < '2016-01-01';
+-------+------------+
| colid | coldate    |
+-------+------------+
|   102 | 2015-05-01 |
+-------+------------+
1 row in set (0.00 sec)

希望这对很多人有帮助。

I am using Server version: 5.7.11-log MySQL Community Server (GPL)

Now we will solve this in a simple way.

I have created a table named "datetable"

mysql> describe datetable;
+---------+---------+------+-----+---------+-------+
| Field   | Type    | Null | Key | Default | Extra |
+---------+---------+------+-----+---------+-------+
| colid   | int(11) | NO   | PRI | NULL    |       |
| coldate | date    | YES  |     | NULL    |       |
+---------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

now, wee will see the inserted records within.

mysql> select * from datetable;
+-------+------------+
| colid | coldate    |
+-------+------------+
|   101 | 2015-01-01 |
|   102 | 2015-05-01 |
|   103 | 2016-01-01 |
+-------+------------+
3 rows in set (0.00 sec)

and here our query to fetch records within two dates rather than those dates.

mysql> select * from datetable where coldate > '2015-01-01' and coldate < '2016-01-01';
+-------+------------+
| colid | coldate    |
+-------+------------+
|   102 | 2015-05-01 |
+-------+------------+
1 row in set (0.00 sec)

hope this would help many ones.

回复 原文
滥情稳全场 2024-07-19 09:21:03

我们在 BIRT 报告中也遇到了类似的问题，因为我们想要报告那些没有数据的日子。 由于没有这些日期的条目，对我们来说最简单的解决方案是创建一个存储所有日期的简单表，并使用它来获取范围或连接以获取该日期的零值。

我们有一项每月运行一次的工作，以确保该表在未来 5 年内被填充。 表是这样创建的：

create table all_dates (
    dt date primary key
);

毫无疑问，有一些神奇而棘手的方法可以使用不同的 DBMS 来做到这一点，但我们总是选择最简单的解决方案。 该表的存储要求很小，并且使查询变得更加简单和便携。 从性能的角度来看，这种解决方案几乎总是更好，因为它不需要对数据进行每行计算。

另一个选项（我们之前使用过这个）是确保表中每个日期都有一个条目。 我们定期扫描表格并为不存在的日期和/或时间添加零条目。 对于您的情况，这可能不是一个选项，这取决于存储的数据。

如果您真的认为填充all_dates表很麻烦，那么可以使用存储过程来返回包含这些日期的数据集。 这几乎肯定会更慢，因为您必须在每次调用时计算范围，而不仅仅是从表中提取预先计算的数据。

但是，说实话，您可以将表填充 1000 年而不会出现任何严重的数据存储问题 - 365,000 个 16 字节（例如）日期加上复制日期的索引加上 20% 的安全开销，我粗略估计为大约 14M [365,000 * 16 * 2 * 1.2 = 14,016,000 字节]），在事物方案中是一个很小的表。

We had a similar problem with BIRT reports in that we wanted to report on those days that had no data. Since there were no entries for those dates, the easiest solution for us was to create a simple table that stored all dates and use that to get ranges or join to get zero values for that date.

We have a job that runs every month to ensure that the table is populated 5 years out into the future. The table is created thus:

create table all_dates (
    dt date primary key
);

No doubt there are magical tricky ways to do this with different DBMS' but we always opt for the simplest solution. The storage requirements for the table are minimal and it makes the queries so much simpler and portable. This sort of solution is almost always better from a performance point-of-view since it doesn't require per-row calculations on the data.

The other option (and we've used this before) is to ensure there's an entry in the table for every date. We swept the table periodically and added zero entries for dates and/or times that didn't exist. This may not be an option in your case, it depends on the data stored.

If you really think it's a hassle to keep the all_dates table populated, a stored procedure is the way to go which will return a dataset containing those dates. This will almost certainly be slower since you have to calculate the range every time it's called rather than just pulling pre-calculated data from a table.

But, to be honest, you could populate the table out for 1000 years without any serious data storage problems - 365,000 16-byte (for example) dates plus an index duplicating the date plus 20% overhead for safety, I'd roughly estimate at about 14M [365,000 * 16 * 2 * 1.2 = 14,016,000 bytes]), a minuscule table in the scheme of things.

回复 原文
酒浓于脸红 2024-07-19 09:21:03

在 MariaDB >= 10.3 和 MySQL >= 8.0 中使用新的递归（通用表表达式）功能的优雅解决方案。

WITH RECURSIVE t as (
    SELECT '2019-01-01' as dt
  UNION
    SELECT DATE_ADD(t.dt, INTERVAL 1 DAY) FROM t WHERE DATE_ADD(t.dt, INTERVAL 1 DAY) <= '2019-04-30'
)
SELECT * FROM t;

上面的代码返回 2019-01-01 和 2019-04-30 之间的日期表。

Elegant solution using new recursive (Common Table Expressions) functionality in MariaDB >= 10.3 and MySQL >= 8.0.

WITH RECURSIVE t as (
    SELECT '2019-01-01' as dt
  UNION
    SELECT DATE_ADD(t.dt, INTERVAL 1 DAY) FROM t WHERE DATE_ADD(t.dt, INTERVAL 1 DAY) <= '2019-04-30'
)
SELECT * FROM t;

The above returns a table of dates between 2019-01-01 and 2019-04-30.

回复 原文
一萌ing 2024-07-19 09:21:03

借用 这个答案，您可以设置一个包含 0 到 9 的表格，并使用它来生成日期列表。

CREATE TABLE num (i int);
INSERT INTO num (i) VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);

select adddate('2009-01-01', numlist.id) as `date` from
(SELECT n1.i + n10.i*10 + n100.i*100 AS id
   FROM num n1 cross join num as n10 cross join num as n100) as numlist
where adddate('2009-01-01', numlist.id) <= '2009-01-13';

这将允许您生成最多包含 1000 个日期的列表。 如果需要更大的范围，可以向内部查询添加另一个交叉联接。

Borrowing an idea from this answer, you can set up a table with 0 through 9 and use that to generate your list of dates.

CREATE TABLE num (i int);
INSERT INTO num (i) VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);

select adddate('2009-01-01', numlist.id) as `date` from
(SELECT n1.i + n10.i*10 + n100.i*100 AS id
   FROM num n1 cross join num as n10 cross join num as n100) as numlist
where adddate('2009-01-01', numlist.id) <= '2009-01-13';

This will allow you to generate a list of up to 1000 dates. If you need to go larger, you can add another cross join to the inner query.

回复 原文
清醇 2024-07-19 09:21:02

我将使用此存储过程在名为 time_intervals 的临时表中生成所需的时间间隔，然后将数据表与临时 time_intervals 表连接并聚合。

该过程可以生成您在其中指定的所有不同类型的间隔：

call make_intervals('2009-01-01 00:00:00','2009-01-10 00:00:00',1,'DAY')
.
select * from time_intervals  
.
interval_start      interval_end        
------------------- ------------------- 
2009-01-01 00:00:00 2009-01-01 23:59:59 
2009-01-02 00:00:00 2009-01-02 23:59:59 
2009-01-03 00:00:00 2009-01-03 23:59:59 
2009-01-04 00:00:00 2009-01-04 23:59:59 
2009-01-05 00:00:00 2009-01-05 23:59:59 
2009-01-06 00:00:00 2009-01-06 23:59:59 
2009-01-07 00:00:00 2009-01-07 23:59:59 
2009-01-08 00:00:00 2009-01-08 23:59:59 
2009-01-09 00:00:00 2009-01-09 23:59:59 
.
call make_intervals('2009-01-01 00:00:00','2009-01-01 02:00:00',10,'MINUTE')
. 
select * from time_intervals
.  
interval_start      interval_end        
------------------- ------------------- 
2009-01-01 00:00:00 2009-01-01 00:09:59 
2009-01-01 00:10:00 2009-01-01 00:19:59 
2009-01-01 00:20:00 2009-01-01 00:29:59 
2009-01-01 00:30:00 2009-01-01 00:39:59 
2009-01-01 00:40:00 2009-01-01 00:49:59 
2009-01-01 00:50:00 2009-01-01 00:59:59 
2009-01-01 01:00:00 2009-01-01 01:09:59 
2009-01-01 01:10:00 2009-01-01 01:19:59 
2009-01-01 01:20:00 2009-01-01 01:29:59 
2009-01-01 01:30:00 2009-01-01 01:39:59 
2009-01-01 01:40:00 2009-01-01 01:49:59 
2009-01-01 01:50:00 2009-01-01 01:59:59 
.
I specified an interval_start and interval_end so you can aggregate the 
data timestamps with a "between interval_start and interval_end" type of JOIN.
.
Code for the proc:
.
-- drop procedure make_intervals
.
CREATE PROCEDURE make_intervals(startdate timestamp, enddate timestamp, intval integer, unitval varchar(10))
BEGIN
-- *************************************************************************
-- Procedure: make_intervals()
--    Author: Ron Savage
--      Date: 02/03/2009
--
-- Description:
-- This procedure creates a temporary table named time_intervals with the
-- interval_start and interval_end fields specifed from the startdate and
-- enddate arguments, at intervals of intval (unitval) size.
-- *************************************************************************
   declare thisDate timestamp;
   declare nextDate timestamp;
   set thisDate = startdate;

   -- *************************************************************************
   -- Drop / create the temp table
   -- *************************************************************************
   drop temporary table if exists time_intervals;
   create temporary table if not exists time_intervals
      (
      interval_start timestamp,
      interval_end timestamp
      );

   -- *************************************************************************
   -- Loop through the startdate adding each intval interval until enddate
   -- *************************************************************************
   repeat
      select
         case unitval
            when 'MICROSECOND' then timestampadd(MICROSECOND, intval, thisDate)
            when 'SECOND'      then timestampadd(SECOND, intval, thisDate)
            when 'MINUTE'      then timestampadd(MINUTE, intval, thisDate)
            when 'HOUR'        then timestampadd(HOUR, intval, thisDate)
            when 'DAY'         then timestampadd(DAY, intval, thisDate)
            when 'WEEK'        then timestampadd(WEEK, intval, thisDate)
            when 'MONTH'       then timestampadd(MONTH, intval, thisDate)
            when 'QUARTER'     then timestampadd(QUARTER, intval, thisDate)
            when 'YEAR'        then timestampadd(YEAR, intval, thisDate)
         end into nextDate;

      insert into time_intervals select thisDate, timestampadd(MICROSECOND, -1, nextDate);
      set thisDate = nextDate;
   until thisDate >= enddate
   end repeat;

 END;

这篇文章，我为 SQL Server 构建了一个类似的函数。

I would use this stored procedure to generate the intervals you need into the temp table named time_intervals, then JOIN and aggregate your data table with the temp time_intervals table.

The procedure can generate intervals of all the different types you see specified in it:

call make_intervals('2009-01-01 00:00:00','2009-01-10 00:00:00',1,'DAY')
.
select * from time_intervals  
.
interval_start      interval_end        
------------------- ------------------- 
2009-01-01 00:00:00 2009-01-01 23:59:59 
2009-01-02 00:00:00 2009-01-02 23:59:59 
2009-01-03 00:00:00 2009-01-03 23:59:59 
2009-01-04 00:00:00 2009-01-04 23:59:59 
2009-01-05 00:00:00 2009-01-05 23:59:59 
2009-01-06 00:00:00 2009-01-06 23:59:59 
2009-01-07 00:00:00 2009-01-07 23:59:59 
2009-01-08 00:00:00 2009-01-08 23:59:59 
2009-01-09 00:00:00 2009-01-09 23:59:59 
.
call make_intervals('2009-01-01 00:00:00','2009-01-01 02:00:00',10,'MINUTE')
. 
select * from time_intervals
.  
interval_start      interval_end        
------------------- ------------------- 
2009-01-01 00:00:00 2009-01-01 00:09:59 
2009-01-01 00:10:00 2009-01-01 00:19:59 
2009-01-01 00:20:00 2009-01-01 00:29:59 
2009-01-01 00:30:00 2009-01-01 00:39:59 
2009-01-01 00:40:00 2009-01-01 00:49:59 
2009-01-01 00:50:00 2009-01-01 00:59:59 
2009-01-01 01:00:00 2009-01-01 01:09:59 
2009-01-01 01:10:00 2009-01-01 01:19:59 
2009-01-01 01:20:00 2009-01-01 01:29:59 
2009-01-01 01:30:00 2009-01-01 01:39:59 
2009-01-01 01:40:00 2009-01-01 01:49:59 
2009-01-01 01:50:00 2009-01-01 01:59:59 
.
I specified an interval_start and interval_end so you can aggregate the 
data timestamps with a "between interval_start and interval_end" type of JOIN.
.
Code for the proc:
.
-- drop procedure make_intervals
.
CREATE PROCEDURE make_intervals(startdate timestamp, enddate timestamp, intval integer, unitval varchar(10))
BEGIN
-- *************************************************************************
-- Procedure: make_intervals()
--    Author: Ron Savage
--      Date: 02/03/2009
--
-- Description:
-- This procedure creates a temporary table named time_intervals with the
-- interval_start and interval_end fields specifed from the startdate and
-- enddate arguments, at intervals of intval (unitval) size.
-- *************************************************************************
   declare thisDate timestamp;
   declare nextDate timestamp;
   set thisDate = startdate;

   -- *************************************************************************
   -- Drop / create the temp table
   -- *************************************************************************
   drop temporary table if exists time_intervals;
   create temporary table if not exists time_intervals
      (
      interval_start timestamp,
      interval_end timestamp
      );

   -- *************************************************************************
   -- Loop through the startdate adding each intval interval until enddate
   -- *************************************************************************
   repeat
      select
         case unitval
            when 'MICROSECOND' then timestampadd(MICROSECOND, intval, thisDate)
            when 'SECOND'      then timestampadd(SECOND, intval, thisDate)
            when 'MINUTE'      then timestampadd(MINUTE, intval, thisDate)
            when 'HOUR'        then timestampadd(HOUR, intval, thisDate)
            when 'DAY'         then timestampadd(DAY, intval, thisDate)
            when 'WEEK'        then timestampadd(WEEK, intval, thisDate)
            when 'MONTH'       then timestampadd(MONTH, intval, thisDate)
            when 'QUARTER'     then timestampadd(QUARTER, intval, thisDate)
            when 'YEAR'        then timestampadd(YEAR, intval, thisDate)
         end into nextDate;

      insert into time_intervals select thisDate, timestampadd(MICROSECOND, -1, nextDate);
      set thisDate = nextDate;
   until thisDate >= enddate
   end repeat;

 END;

Similar example data scenario at the bottom of this post, where I built a similar function for SQL Server.

回复 原文
怕倦 2024-07-19 09:21:02

对于 MSSQL，您可以使用它。 这是非常快的。

您可以将其包装在表值函数或存储过程中，并将开始日期和结束日期解析为变量。

DECLARE @startDate DATETIME
DECLARE @endDate DATETIME

SET @startDate = '2011-01-01'
SET @endDate = '2011-01-31';

WITH dates(Date) AS 
(
    SELECT @startdate as Date
    UNION ALL
    SELECT DATEADD(d,1,[Date])
    FROM dates 
    WHERE DATE < @enddate
)

SELECT Date
FROM dates
OPTION (MAXRECURSION 0)
GO

编辑2021/01（V博士）：
我喜欢这个解决方案并使其适用于 mySQL V8。 这是代码，将其包装到一个过程中：

DELIMITER //

CREATE PROCEDURE dates_between (IN from_date DATETIME,
                               IN to_date DATETIME) BEGIN
    WITH RECURSIVE dates(Date) AS
    (
        SELECT from_date as Date
        UNION ALL
        SELECT DATE_ADD(Date, INTERVAL 1 day) FROM dates WHERE Date < to_date
    )
    SELECT DATE(Date) FROM dates;
END//

DELIMITER ;

For MSSQL you can use this. It is VERY quick.

You can wrap this up in a table valued function or stored proc and parse in the start and end dates as variables.

DECLARE @startDate DATETIME
DECLARE @endDate DATETIME

SET @startDate = '2011-01-01'
SET @endDate = '2011-01-31';

WITH dates(Date) AS 
(
    SELECT @startdate as Date
    UNION ALL
    SELECT DATEADD(d,1,[Date])
    FROM dates 
    WHERE DATE < @enddate
)

SELECT Date
FROM dates
OPTION (MAXRECURSION 0)
GO

Edit 2021/01 (Dr. V):
I liked this solution and made it work for mySQL V8. Here is the code, wrapping it into a procedure:

DELIMITER //

CREATE PROCEDURE dates_between (IN from_date DATETIME,
                               IN to_date DATETIME) BEGIN
    WITH RECURSIVE dates(Date) AS
    (
        SELECT from_date as Date
        UNION ALL
        SELECT DATE_ADD(Date, INTERVAL 1 day) FROM dates WHERE Date < to_date
    )
    SELECT DATE(Date) FROM dates;
END//

DELIMITER ;
回复 原文
沙沙粒小 2024-07-19 09:21:02

您可以像这样使用 MySQL 的用户变量：

SET @num = -1;
SELECT DATE_ADD( '2009-01-01', interval @num := @num+1 day) AS date_sequence, 
your_table.* FROM your_table
WHERE your_table.other_column IS NOT NULL
HAVING DATE_ADD('2009-01-01', interval @num day) <= '2009-01-13'

@num是-1，因为你第一次使用它时添加了它。 另外，您不能使用“HAVING date_sequence”，因为这会使用户变量每行增加两次。

You can use MySQL's user variables like this:

SET @num = -1;
SELECT DATE_ADD( '2009-01-01', interval @num := @num+1 day) AS date_sequence, 
your_table.* FROM your_table
WHERE your_table.other_column IS NOT NULL
HAVING DATE_ADD('2009-01-01', interval @num day) <= '2009-01-13'

@num is -1 because you add to it the first time you use it. Also, you can't use "HAVING date_sequence" because that makes the user variable increment twice for each row.

回复 原文
~没有更多了~

关于作者

樱花细雨

暂无简介

文章
评论
25 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

雪花的坚持

文章 0 评论 0

温柔一刀

文章 0 评论 0

扛起拖把扫天下

文章 0 评论 0

北方的韩爷

文章 0 评论 0

绝對不後悔。

文章 0 评论 0

青衫负雪

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文