(MySQL)如何得到Date Range之間的所有日子

1 min readMar 24, 2022

以下方法是使用了 Recursive CTE (Common Table Expressions). 而MySQL version 是 8.0, 如果 version < 8.0 是不能使用 WITH clause的.

What is CTE?

由於每條 statement 都會產生一個暫時的result set, 如果想賦予它一個名字給另外的 statement 使用, 就會用 CTE. 它是以WITH為開頭.

WITH
cte_name [(col1, col2, ...)]
AS ( subquery )
Select col1, col2, .. from cte_name;

What is Recursive CTE?

是不斷 Loop 自己直到 terminating condition fulfilled. 它是以WITH RECURSIVE為開頭, 中間要使用 UNION ALL.

WITH RECURSIVE
cte_name [(col1, col2, ...)]
AS 
( 
  Selet a, b, ... n from table_name        
  UNION ALL
  Select col1, col2, ...coln from cte_name          
    WHERE terminating condition 
)
Select col1, col2, .. from cte_name;

How do get all dates from a date range?

利用 RECURSIVE CTE 產生一個table, 由start_date開始不斷加一日產生一個Date record 直到到達end_date (terminating condition).

set @start_date := '2022-01-01';
SET @end_date := '2022-01-05';
    
WITH RECURSIVE 
DateRange(DateData) AS
(
  SELECT @start_date
  UNION ALL
  SELECT DATE_ADD(DateData, INTERVAL 1 DAY) FROM DateRange WHERE DateData < @end_date
)
SELECT DateData FROM DateRange;

當中 DATE_ADD() function 是在 DateData 加一個 interval, 之後 returns the date.

Reference:

https://dev.mysql.com/doc/refman/8.0/en/with.html

(MySQL)如何得到Date Range之間的所有日子

Written by fish~~