Gerando uma série de datas

Qual é a melhor maneira no mysql para gerar uma série de datas em um determinado intervalo?

O aplicativo que tenho em mente é escrever uma consulta de relatório que retorna uma linha para cada data, independentemente de haver algum dado a ser relatado. Na sua forma mais simples:

select dates.date, sum(sales.amount) from  dates left join sales on date(sales.created) = dates.date group by 1 

Eu tentei criar uma tabela com muitos datas, mas isso parece uma solução ruim.

Eu acho que ter uma mesa de calendar é uma boa ideia; Você pode obter muitas funcionalidades de relatórios e consultas, especialmente ao preencher intervalos de dados esparsos.

Eu encontrei este artigo com o que parece ser um bom exemplo.

Você pode usar uma variável gerar séries de datas:

 Set @i:=0; SELECT DATE(DATE_ADD(X, INTERVAL @i:=@i+1 DAY) ) AS datesSeries FROM yourtable, (SELECT @i:=0) r where @i < DATEDIFF(now(), date Y) ; 

Não tenho certeza se isso é o que você tentou 🙂 embora.

Próximo uso acima gerou consulta como uma tabela para a left join :

 set @i:=0; select d.dates, sum(s.amount) as TotalAmount from( SELECT DATE(DATE_ADD(X, INTERVAL @i:=@i+1 DAY) ) AS dateSeries FROM Sales, (SELECT @i:=0) r where @i < DATEDIFF(now(), date Y) ) dates d left join Sales s on Date(s.Created) = Date(d.dateSeries) group by 1 ; 

Se você estiver em uma situação como eu, onde a criação de tabelas temporárias é proibida e a configuração de variables ​​também não é permitida , mas você deseja gerar uma lista de datas em um período específico , digamos o ano atual para fazer alguma agregação, use

 select * from (select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) gen_date from (select 0 t0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0, (select 0 t1 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1, (select 0 t2 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2, (select 0 t3 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3, (select 0 t4 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v where gen_date between '2017-01-01' and '2017-12-31' 

Você também pode usar a Tabela Temporária para gerar séries de datas. Confira abaixo a consulta:

 CREATE TEMPORARY TABLE daterange (dte DATE); SET @counter := -1; WHILE (@counter < DATEDIFF(DATE(_todate), DATE(_fromdate))) DO INSERT daterange VALUES (DATE_ADD(_fromdate, INTERVAL @counter:=@counter + 1 DAY)); END WHILE; SELECT dates.dte, SUM(sales.amount) FROM daterange dates LEFT JOIN sales ON DATE(sales.created) = dates.date GROUP BY dates.dte;