Date Spine

Create a table that lists all possible dates. Run once.

DROP TABLE IF EXISTS public.date_spine;

CREATE TABLE IF NOT EXISTS public.date_spine (
  id serial,
  date date
);

INSERT INTO public.date_spine (date)
SELECT CAST('1970-01-01' AS date) + generate_series(0, 365*100);

CREATE INDEX ON public.date_spine(date);


Why?

A date spine table is useful for any use case that requires rows of dates where no dates exists in the original data.
For example in an employee turnover use case, you might have data that looks like this:
employee_idstart_dateend_date
12014-01-012017-03-31
22015-06-01NULL
32017-01-012017-05-31
To show how many employees were at the company on any given day you need a different data structure, one that has every possible date and everyone who was at the company on those dates.
To solve this you need a master date / calendar table.

What are the building blocks?

This is the simplest version that just lists the dates from a starting date and forward N number of days.
SELECT CAST(‘2015-01-01’ AS date) + generate_series(0, 365*100) AS dates;
 
Generate series is creating the series of numbers from 0 to N. In this case its ~100 years (365*109).
This is the result of the date spine SQL.
 

Search