How to generate a date series in MySQL

8 years ago
14 February 2015
1 reply
114 views

Userlevel 1

anika
Looker Staff
10 replies

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