PostgreSQL has quite an interesting recursive WITH statement which allows you to create a loop: You can define in example a value that is incremented with each iteration until a certain condition is met. This comes in quite handy when creating sample data for a date dimension in example:
Let's keep it very simple:
WITH RECURSIVE date_generator(date) AS (
VALUES (DATE '2012-01-01')
UNION ALL
SELECT date+1 FROM date_generator WHERE date < DATE '2012-01-15'
)
SELECT
date
, EXTRACT(DAY FROM date) AS day
, EXTRACT(MONTH FROM date) AS month
, EXTRACT(QUARTER FROM date) AS quarter
, EXTRACT(YEAR FROM date) AS year
FROM
date_generator
;
VALUES (DATE '2012-01-01')
UNION ALL
SELECT date+1 FROM date_generator WHERE date < DATE '2012-01-15'
)
SELECT
date
, EXTRACT(DAY FROM date) AS day
, EXTRACT(MONTH FROM date) AS month
, EXTRACT(QUARTER FROM date) AS quarter
, EXTRACT(YEAR FROM date) AS year
FROM
date_generator
;
In the WITH statement we provide a start date (in this case 2012-01-01) and increase it by 1 until a specific end date (in this case '2012-01-15') is reached. In the main query we make use of the auto generated dates by extracting various date periods. The output looks like this:
Looking at this elegant code am I right in assuming that the first EXTRACT line in the example above should be;
ReplyDelete", EXTRACT(DAY FROM date) AS day"
Well spotted! Thanks for pointing this out! I'll correct it immediately.
ReplyDelete