
<p>A lot of charts and tables are time series, and the queries behind them are often easier when you can join and aggregate against a list of dates. Not having a complete list of dates causes gaps in the results, changing them in a misleading way:</p>



<figure class="wp-block-image fancybox"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/Daily-reports.png" alt="Daily reports" class="wp-image-74257"/></figure>



<p> Postgres has a great function for generating a list of dates (see <a href="https://www.sisense.com/blog/use-generate-series-to-get-continuous-results/">Use generate_series to get continuous results</a>), and making a list of the last 60 days with <a href="https://www.postgresql.org/docs/9.3/functions-srf.html" target="_blank" rel="noreferrer noopener" aria-label=" (opens in a new tab)">generate_series</a> is easy: </p>



<pre class="wp-block-code"><code>select now()::date - generate_series(0, 59)</code></pre>



<p>Accomplishing the same thing in Redshift and MySQL requires a little more work.</p>



<h2 class="wp-block-heading"><strong>Date Series from a Numbers Table</strong></h2>



<p>The simplest alternative to generate_series is to create a table containing a continuous list of numbers, starting at 0, and select from that table. (If you have a table with a sequential id column and never delete rows from it, you can just select the id column from that table instead of creating a new numbers table).</p>



<pre class="wp-block-code"><code>select n from numbers;</code></pre>



<p>Returns this list of rows: 0, 1, 2, 3…</p>



<p>Now that you have a numbers table, convert each number into a date:</p>



<h2 class="wp-block-heading"><strong>Redshift:</strong></h2>



<pre class="wp-block-code"><code>select (getdate()::date - n)::date from numbers</code></pre>



<h2 class="wp-block-heading"><strong>MySQL:</strong></h2>



<pre class="wp-block-code"><code>select date_sub(date(now()), interval n day) from numbers</code></pre>



<p>A numbers table is more convenient than a dates table since it never needs to be refreshed with new dates.</p>



<h2 class="wp-block-heading"><strong>Redshift: Date Series using Window Functions</strong></h2>



<p>If you don’t have the option to create a numbers table, you can build one on the fly using a window function. All you need is a table that has at least as many rows as the number of dates desired. Using a window function, number the rows in any table to get a list of numbers, and then convert that to a list of dates:</p>



<pre class="wp-block-code"><code>select row_number() over (order by true) as n
from users limit 60</code></pre>



<p>And now creating the list of dates directly:</p>



<pre class="wp-block-code"><code>select (
    getdate()::date - row_number() over (order by true)
  )::date as n
from users limit 60</code></pre>



<h2 class="wp-block-heading"><strong>MySQL: Date Series using Variables</strong></h2>



<p>With variables in MySQL, we can generate a numbers table by treating a select statement as a for loop:</p>



<pre class="wp-block-code"><code>set @n:=-1;
select (select @n:= @n+1) n
from users limit 60</code></pre>



<p>Now that we’ve made a list of dates, aggregating and joining data from other tables for time series charts is a breeze!</p>


Generate Series in Redshift and MySQL

LinkedIn

Twitter

GitHub

curve-image-unique-image-unique

curve

3-dark-2-image-unique-image-unique

3 DARK 2

Get the latest in analytics right in your inbox.

Article