select statement that gives a range of dates without any table?

Question

If I have a MySQL database with ZERO tables in it. Is there an SQL statement that can return results like:

+------------+
| date       |
+------------+
| 2017-06-01 |
| 2017-06-02 |
| 2017-06-03 |
| 2017-06-04 |
etc.... to any end date I want
+------------+

The reason I want this is because I want to be able to generate a table like this on the fly to help me with some queries in a different database.

There are ways to create it on the fly, but IMHO they all are "ugly". I'd just create a calendar table with all dates you need. — Paul Spiegel, Nov 13 '17 at 00:34
What's the big deal with the one-time action of creating calendar table? It only takes a couple of minutes, but will save you hours of SQL coding. — Bohemian, Nov 13 '17 at 01:00

score 1 · Answer 1 · answered Nov 13 '17 at 00:45

In MariaDB, you can use a built-in seq table to do this. This query, for example, returns the 100 days starting at 1-Nov-2017

SELECT '2017-11-01' + INTERVAL seq.seq DAY AS sequential_day FROM seq_0_to_99 seq

In MySQL, you need to engage in some monkey business to get a sequence of numbers with no tables. This ugly little query generates the numbers from zero to 15,625.

SELECT A.N + 5*(B.N + 5*(C.N + 5*(D.N + 5*(E.N + 5*(F.N))))) AS seq
  FROM (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS A
  JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS B
  JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS C
  JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS D
  JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS E
  JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS F

You can use it as a subquery to generate a sequence of dates.

select '2017-11-01' + INTERVAL seq.seq DAY AS sequential_day
  from (
    SELECT A.N + 5*(B.N + 5*(C.N + 5*(D.N + 5*(E.N + 5*(F.N))))) AS seq
      FROM (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS A
      JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS B
      JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS C
      JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS D
      JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS E
      JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS F
       ) AS seq
 where seq.seq <= 99

It's not very elegant. It fact, it's ugly. But it works fine.

Or you can make yourself a date table and use it.

YouneL · Answer 2 · 2017-11-13T01:11:26.397

0

You can use subquery to declare the first date and then increment it, like following:

SELECT  @date := DATE_ADD(@date, INTERVAL 1 DAY) AS dates 
FROM mysql.help_relation , (
    SELECT @date:= DATE_SUB('2017-06-01', INTERVAL 1 DAY)) d 
    WHERE @date BETWEEN @date AND DATE_SUB('2017-06-04', INTERVAL 1 DAY
);

edited Nov 13 '17 at 01:11

answered Nov 13 '17 at 00:34

YouneL

8,152
2
28
50

Paul Maxwell · Answer 3 · 2017-11-13T01:40:41.730

One method is to create a loop and insert. Note I'm not keen on using "date" as a column name as it gets way too confusing in queries. Also suggest the date is used as primary key.

delimiter \\
## Create the calendar table.
CREATE TABLE calendar (
    cal_date date primary key
);
\\ 

## accepts a date range
CREATE PROCEDURE create_calendar(IN startdate date, IN enddate date)
BEGIN
    SET @x = 0;
    WHILE (startdate + INTERVAL @x DAY) < enddate DO
        ## Insert another row
        INSERT INTO calendar (cal_date) VALUES (startdate + INTERVAL @x DAY);
        SET @x = @x + 1;
    END WHILE;
END
\\

## populate the calendar table with wanted range 
## nb the enddate is NOT included in the table

CALL create_calendar('2017-01-01','2017-02-01');
\\
select * from calendar;
\\

When you need more dates in the table, re-run the procedure with the wanted range (but those dates must not already exist in the calendar table).

Derived from How to Create a Tally Table in MySQL

score 0 · Answer 4 · answered Nov 13 '17 at 01:04

I think you can do it via Stored Procedures and Inline Tables

drop procedure if exists timer;
CREATE PROCEDURE timer()   
BEGIN
    DECLARE i INT DEFAULT 1;
    declare d date default now();
    drop table B;
    create table B (id date); 
    WHILE (d<='2017-11-23') DO
        insert into B values(d);
        set d = CURRENT_DATE()+i;
        set i = i+1;
    END WHILE;
    select * from B;
END;

And call this SP when you need such that CALL timer();

NOTE I am not expert in MYSQL and I depend on those answers to compose this answers (: so you can also benefit from, compare dates in mysql, Inline tables in mysql, MySQL functions, and also While Loops

Also Note you can use IN parameter for a target date in the stored procedure

select statement that gives a range of dates without any table?

4 Answers4