Sunday, March 11, 2012

PostgreSQL: Auto generating a sample dataset (II)

Some time ago I presented a simple solution to generate date dimension data in PostgreSQL using standard SQL. The below approach is another take on this and probably a little bit easier to read then the previous approach.
This time I made use of the GENERATE_SERIES function. As a simple example, we first use a Common Table Expression to generate a dataset which holds 10 successive dates and then in the main query we extract some date information:


WITH date_series AS (
SELECT
DATE(GENERATE_SERIES(DATE '2012-01-01', DATE '2012-01-10','1 day')) AS date
)
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_series
;



2 comments:

  1. Wonderful post. Quality content posted certainly.

    Sample

    ReplyDelete
    Replies
    1. Thanks a lot for your feedback! Much appreciated!

      Delete