calendar

Simulates a date-range generation function.

There is one row in this view for each date in time. This view allows you to perform a SQL CROSS JOIN to it and use aggregate functions to produce day-by-day drill-down detail reporting for something that changes over time. The following example reports the number of payments made on any given day throughout financial year 2015-16.

SELECT calendar.date, count(*) FROM smile02.calendar CROSS JOIN smile02.payment WHERE calendar.date BETWEEN '2015-07-01' AND '2016-06-30' AND payment.creation_time::DATE = calendar.date GROUP BY calendar.date;