The Art of PostgreSQL/

...

/

Solution Review: Generating Series

Solution Review: Generating Series

See a detailed analysis of the solution to the "Generating Series" challenge.

We'll cover the following...

In the challenge, you were asked to perform two tasks. Here we’re going to discuss the solution to these two tasks.

Task 1

You were asked to write a query to generate a series and check which year is a leap year. Your code should output the date, year, and leap columns. The date column will take the values starting from 2000-01-01.

Solution

Click the “Run” button in the following widget to see the output.

Press + to interact
/***************************************************/
/*****           The query to check            *****/
/*****        if a year is a leap year         *****/
/***************************************************/
select date::date,
       extract('year' from date) as year,
       extract('day' from
               (date + interval '2 month - 1 day')
              ) = 29
       as leap
  from generate_series(date '2000-01-01',
                       date '2010-01-01',
                       interval '1 year')
       as t(date);
/***************************************************/

Explanation

This section gives a detailed analysis of the solution to the first task of the challenge.

  • Lines 5–10: The select clause mentions the following columns:

    • date: This column will print the date of the first day of the year.
    • year: This
...