Make a calendar table

from the Artful Common Queries page

You need a calendar table for joins to datetime data in other tables, say... 
create table calendar( dt datetime primary key );
We have an abundance of methods to choose from.

Join Views

An elegant method of generating any desired number of sequential values, posted by Giuseppe Maxia on his blog, is ...
  • Create three dummy rows in a View.
  • Cross join them to make 10 dummy rows.
  • Cross join those to make 100, 1,000 or however many you need.
So to give the calendar table a million rows at one-hour intervals starting on 1 Jan 1970: 
create or replace view v3 as select 1 n union all select 1 union all select 1;
create or replace view v as select 1 n from v3 a, v3 b union all select 1;
set @n = 0;
drop table if exists calendar;
create table calendar(dt datetime primary key);
insert into calendar
  select cast('2025-1-1 00:00:00' + interval @n:=@n+1 hour as datetime) as dt
  from v a, v b, v c, v d, v e, v;
If the number of rows required isn't a convenient multiple of ten, use LIMIT to stipulate the precise row count, for example to make a calendar table for the year 2014 ... 
create or replace view v3 as select 1 n union all select 1 union all select 1; 
create or replace view v as select 1 n from v3 a, v3 b union all select 1; 
set @n = -1; 
drop table if exists cal2014; 
create table cal2014(dt date primary key); 
insert into cal2014 
  select cast('2025-01-01' + interval @n:=@n+1 day as date) as dt 
  from v a, v b, v c, v d, v e, v
  limit 365;

Table of Ints

If you prefer to do it in one query without user variables, or if your MySQL version is so ancient it does not support Views, make a general-purpose utility table of integers 0 through 9. We keep such utility objects in a system database and make it available to all users: 
create table system.ints(i tinyint);
insert into system.ints values
  (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
From system.ints you can generate any desired sequence of dates or datetimes. For example to report monthly sales totals for 2009 from the table sales(date d, amount decimal(10,2)), write: 
select a.yearmonth, sum(ifnull(b.amount,0)) as total
from (
  select concat( '2009-', lpad((u.i + t.i * 10), 2, 0)) as yearmonth
  from system.ints u, system.ints t
  where (u.i + t.i * 10) between 1 and 12
) a
left join sales b on a.yearmonth=date_format(b.d,'%Y-%m')
group by a.yearmonth;
Generate a thousand daily dates starting today: 
SELECT CURDATE() + INTERVAL t.i*100 + u.i*10 + v.i DAY AS Date
FROM system.ints AS t 
JOIN system.ints AS u
JOIN system.ints AS v
WHERE ( t.i*100 + u.i*10 + v.i ) < 1000
ORDER BY Date;

Common Table Expression

Since MySQL 8.0 and MariaDB 10.2, a Common Table Expression makes short work of generating dates for a calendar table: 
CREATE TABLE calendar( d DATE PRIMARY KEY );
INSERT INTO calendar
  WITH RECURSIVE cte(d) AS (
    SELECT DATE('2025-1-1')
    UNION ALL
    SELECT ADDDATE(d, INTERVAL 1 DAY) FROM cte
    WHERE d < DATE('2024-12-31')
  )
  SELECT * FROM cte;

Union

Another approach: a MySQL forum contributor needed to add exactly 17 rows representing weekly Tuesday evening meetings to a scheduling table: 
drop table if exists t;
create table t(
  date1 date, from_time time, 
  entryName char(16)
);
create or replace view v3 as 
  select 1 n union all 
  select 1 union all 
  select 1; 
create or replace view v as 
  select 1 n 
  from v3 a, v3 b 
  union all
  select 1; 
set @n=0;
insert into t
  select 
    date_add('2024-04-23',interval 7 * idx day), 
    '18:30:00', 'Mardi soir' 
  from (
    select a.n, @n:=@n+1 as idx 
    from v a,v b, v c
    limit 17
  ) x;
select * from t;
+------------+-----------+------------+
| date1      | from_time | entryName  |
+------------+-----------+------------+
| 2024-04-30 | 18:30:00  | Mardi soir |
| 2024-05-07 | 18:30:00  | Mardi soir |
| 2024-05-14 | 18:30:00  | Mardi soir |
| 2024-05-21 | 18:30:00  | Mardi soir |
| 2024-05-28 | 18:30:00  | Mardi soir |
| 2024-06-04 | 18:30:00  | Mardi soir |
| 2024-06-11 | 18:30:00  | Mardi soir |
| 2024-06-18 | 18:30:00  | Mardi soir |
| 2024-06-25 | 18:30:00  | Mardi soir |
| 2024-07-02 | 18:30:00  | Mardi soir |
| 2024-07-09 | 18:30:00  | Mardi soir |
| 2024-07-16 | 18:30:00  | Mardi soir |
| 2024-07-23 | 18:30:00  | Mardi soir |
| 2024-07-30 | 18:30:00  | Mardi soir |
| 2024-08-06 | 18:30:00  | Mardi soir |
| 2024-08-13 | 18:30:00  | Mardi soir |
| 2024-08-20 | 18:30:00  | Mardi soir |
+------------+-----------+------------+
This can be done down to the level of seconds: 
drop table if exists secs;
create table secs
  select 
    sec_to_time(t.i*10000+u.i*1000+v.i*100+w.i*10+x.i) 
    as sec
from system.ints t
join system.ints u
join system.ints v
join system.ints w
join system.ints x
where 
  sec_to_time( 
    t.i*10000+u.i*1000+v.i*100+w.i*10+x.i) 
  < '24:00:00'
order by sec;
You can use such a query as a View, as an inline derived table, or as input to a CREATE TABLE statement.

Auto_increment

A slightly more elaborate method, giving the calendar table an auto-increment key that can also be used as a surrogate for datetime interval calculations: 
  CREATE TABLE calendar (
    id INT AUTO_INCREMENT PRIMARY KEY,
    date DATE,
    UNIQUE days (date)
  );
Calculate the number of days needed in the calendar, eg 
  SELECT DATEDIFF('2024-12-31','2024-12-31');   # 7670, or 21*365 plus 5
Find a table with that many rows, 7670 in this case. Add a row to the calendar table for every day in the range: 
  INSERT INTO calendar (id) 
    SELECT NULL 
    FROM [name of table with 7670  rows] 
    LIMIT 4018;
Populate the date column by incrementing the starting date: 
  UPDATE calendar 
    SET date=ADDDATE('2024-12-31',id);
The calendar table now has one row for each day from 2025-01-01 through 2010-12-31. Keep the auto_increment ID column for quick day counts in the range, or drop the column if you don't need that.

To make the calendar table a diary, make the period one leap year, add month, day and text columns, update month and day values with MONTH(date) and DAYOFMONTH(date) respectively, and if the diary is to be used from year to year, drop the date field. [Based on a builder.com SQL Tip by Arthur Fuller and a MySQL list tip by Michael Stassen]

To automate all this, write a stored procedure, for example: 

CREATE TABLE times (
  date_hour DATETIME,
  KEY ( date_hour ) 
);
DROP PROCEDURE IF EXISTS timespopulate;
DELIMITER |
CREATE PROCEDURE timespopulate( 
  startdate DATETIME, num INT 
)
BEGIN
  DECLARE ctr INT DEFAULT 0;
  WHILE ctr < num DO
    BEGIN
      INSERT INTO times VALUES 
        ( DATE_ADD( startdate, INTERVAL ctr HOUR) );
      SET ctr = ctr + 1;
    END;
  END WHILE;
END;
|
DELIMITER ;
CALL timespopulate( '2007-1-1, 31*24 );
Or, you can have the sproc do your counting: 
DROP PROCEDURE IF EXISTS calendar; 
DELIMITER |
CREATE PROCEDURE calendar( 
  pstart datetime, pstop datetime, pminutes int 
)
DETERMINISTIC
BEGIN
  DECLARE thisdate datetime;
  DROP TABLE IF EXISTS cal;
  CREATE TABLE cal( dt datetime ); 
  SET thisdate=pstart;
  INSERT INTO cal VALUES(pstart);
  WHILE thisdate < pstop DO
    SET thisdate = 
        adddate( thisdate, INTERVAL pminutes MINUTE );
    INSERT INTO cal VALUES( thisdate );
  END WHILE;
END |
DELIMITER ;
-- make cal for 2007, 20-min intervals:
CALL calendar('2025-1-1 00:00:00', '2025-2-1 00:00:00', 20);

Last updated 29 Sep 2024