2

How can I create a temporary table and fill it with ceratin dates. I know the startdate and the "limit"

  • startdate = 2014-11-11
  • limit = 3

table should look like

2014-11-11
2014-11-12
2014-11-13

I like to use this create table to join it with another

Xaver
  • 11,144
  • 13
  • 56
  • 91

1 Answers1

5

You can generate the dynamic dates and then insert them int to the table as below. I have used a table instead of temporary table you can change it to temporary table.

CREATE TEMPORARY TABLE IF NOT EXISTS dates_test
(dates datetime);


insert into dates_test (dates)
select 
t1.date
from
(
  select
  a.Date as date
  from (
    select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as Date
    from (select 0 as a 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) as a
    cross join (select 0 as a 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) as b
    cross join (select 0 as a 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) as c
  ) a
  where a.Date BETWEEN '2014-11-11' 
  and
  DATE_ADD('2014-11-11' ,INTERVAL 3 DAY)
)t1

Here is a demo

Xaver
  • 11,144
  • 13
  • 56
  • 91
Abhik Chakraborty
  • 44,654
  • 6
  • 52
  • 63
  • looks good. How can I join this table with another in a single query? – Xaver Nov 15 '14 at 10:41
  • You can join but without seeing the tables I can't say, I have few similar answers you may check those http://stackoverflow.com/questions/24301238/mysql-show-count-of-0-for-dates-with-no-records/24302040#24302040 – Abhik Chakraborty Nov 15 '14 at 10:48
  • Here is another http://stackoverflow.com/questions/23300303/mysql-single-table-select-last-7-days-and-include-empty-rows/23301236#23301236 – Abhik Chakraborty Nov 15 '14 at 10:50