Question

How to generate a date series in MySQL

  • 14 February 2015
  • 1 reply
  • 9 views

Date series can be useful for zero-filling dates. This article shows an example of a generated date series in MySQL as a derived table in Looker.


This will generate a series of dates starting from the date given in the WHERE clause until today, with up to 10,000 dates total in the series.


- view: date_series_table
derived_table:
sql: |
SELECT date
FROM (
SELECT curdate() - interval (a.a + (10 * b.a) + (100 * c.a) + (1000 * d.a) + (10000 * e.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
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 d
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 e
) dates
WHERE date >= '2012-01-01'
ORDER BY date

fields:

- dimension: date
sql: ${TABLE}.date

- measure: count
type: count

Similar solutions can be found here.


Reply