Generate Series in Redshift and MySQL_MySQL
A lot of the charts and tables made inPeriscopeare 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:
Postgres has a great function for generating a list of dates (seeUse generate_series to get continuous results), and making a list of the last 60 days withgenerate_series
is easy:
<code>select now()::date - generate_series(0, 59)</code>
Accomplishing the same thing in Redshift and MySQL requires a little more work.
Date Series from a Numbers Table
The simplest alternative togenerate_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 sequentialid
column and never delete rows from it, you can just select theid
column from that table instead of creating a new numbers table).
<code>select n from numbers;</code>
Returns this list of rows: 0, 1, 2, 3...
Now that you have a numbers table, convert each number into a date:
Redshift:
<code>select (getdate()::date - n)::date from numbers</code>
MySQL:
<code>select date_sub(date(now()), interval n day) from numbers</code>
A numbers table is more convenient than a dates table since it never needs to be refreshed with new dates.
Redshift: Date Series using Window Functions
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:
<code>select row_number() over (order by true) as nfrom users limit 60</code>
And now creating the list of dates directly:
<code>select (getdate()::date - row_number() over (order by true))::date as nfrom users limit 60</code>
MySQL: Date Series using Variables
With variables in MySQL, we can generate a numbers table by treating a select statement as a for loop:
<code>set @n:=-1;select (select @n:= @n+1) nfrom users limit 60</code>
And now creating the list of dates directly:
<code>set @n:=date(now() + interval 1 day);select (select @n:= @n - interval 1 day) nfrom users limit 60</code>
Now that we've made a list of dates, aggregating and joining data from other tables for time series charts is a breeze!

Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics

This article explores optimizing MySQL memory usage in Docker. It discusses monitoring techniques (Docker stats, Performance Schema, external tools) and configuration strategies. These include Docker memory limits, swapping, and cgroups, alongside

This article addresses MySQL's "unable to open shared library" error. The issue stems from MySQL's inability to locate necessary shared libraries (.so/.dll files). Solutions involve verifying library installation via the system's package m

The article discusses using MySQL's ALTER TABLE statement to modify tables, including adding/dropping columns, renaming tables/columns, and changing column data types.

This article compares installing MySQL on Linux directly versus using Podman containers, with/without phpMyAdmin. It details installation steps for each method, emphasizing Podman's advantages in isolation, portability, and reproducibility, but also

This article provides a comprehensive overview of SQLite, a self-contained, serverless relational database. It details SQLite's advantages (simplicity, portability, ease of use) and disadvantages (concurrency limitations, scalability challenges). C

This guide demonstrates installing and managing multiple MySQL versions on macOS using Homebrew. It emphasizes using Homebrew to isolate installations, preventing conflicts. The article details installation, starting/stopping services, and best pra

Article discusses configuring SSL/TLS encryption for MySQL, including certificate generation and verification. Main issue is using self-signed certificates' security implications.[Character count: 159]

Article discusses popular MySQL GUI tools like MySQL Workbench and phpMyAdmin, comparing their features and suitability for beginners and advanced users.[159 characters]
