Make a calendar table

from the Artful Common Queries page


You need a calendar table for joins to datetime data in other tables:

create table calendar ( dt datetime primary key );

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('1970-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('2014-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; 

If you prefer to do it one query without user variables, or if your MySQL version does not support Views, make a general-purpose utility table of integers 0 through 9. We keep such utility objects in a sys database and make it available to all users:

create table sys.ints(i tinyint);
insert into sys.ints values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

From sys.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 sys.ints u, sys.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 sys.ints AS t 
JOIN sys.ints AS u
JOIN sys.ints AS v
WHERE ( t.i*100 + u.i*10 + v.i ) < 1000
ORDER BY Date;

Here's a recent example from the MySQL Newbie forum. A poster 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('2013-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  |
+------------+-----------+------------+
| 2013-04-30 | 18:30:00  | Mardi soir |
| 2013-05-07 | 18:30:00  | Mardi soir |
| 2013-05-14 | 18:30:00  | Mardi soir |
| 2013-05-21 | 18:30:00  | Mardi soir |
| 2013-05-28 | 18:30:00  | Mardi soir |
| 2013-06-04 | 18:30:00  | Mardi soir |
| 2013-06-11 | 18:30:00  | Mardi soir |
| 2013-06-18 | 18:30:00  | Mardi soir |
| 2013-06-25 | 18:30:00  | Mardi soir |
| 2013-07-02 | 18:30:00  | Mardi soir |
| 2013-07-09 | 18:30:00  | Mardi soir |
| 2013-07-16 | 18:30:00  | Mardi soir |
| 2013-07-23 | 18:30:00  | Mardi soir |
| 2013-07-30 | 18:30:00  | Mardi soir |
| 2013-08-06 | 18:30:00  | Mardi soir |
| 2013-08-13 | 18:30:00  | Mardi soir |
| 2013-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 sys.ints t
join sys.ints u
join sys.ints v
join sys.ints w
join sys.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.

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('2010-12-31','1989-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('1989-12-31',id);

The calendar table now has one row for each day from 1990-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('2007-1-1 00:00:00', '2007-2-1 00:00:00', 20);


Return to the Artful Common Queries page