Work out the number of days in each month of 2012
Question
For each month of the year in 2012, output the number of days in that month. Format the output as an integer column containing the month of the year, and a second column containing an interval data type.
Expected Results
| month | length |
|---|---|
| 1 | 31 days |
| 2 | 29 days |
| 3 | 31 days |
| 4 | 30 days |
| 5 | 31 days |
| 6 | 30 days |
| 7 | 31 days |
| 8 | 31 days |
| 9 | 30 days |
| 10 | 31 days |
| 11 | 30 days |
| 12 | 31 days |
Your Answer
Your Results
Loading database...
Answers and Discussion
select extract(month from cal.month) as month,
(cal.month + interval '1 month') - cal.month as length
from
(
select generate_series(timestamp '2012-01-01', timestamp '2012-12-01', interval '1 month') as month
) cal
order by month;This answer shows several of the concepts we've learned. We use the GENERATE_SERIES function to produce a year's worth of timestamps, incrementing a month at a time. We then use the EXTRACT function to get the month number. Finally, we subtract each timestamp + 1 month from itself.
It's worth noting that subtracting two timestamps will always produce an interval in terms of days (or portions of a day). You won't just get an answer in terms of months or years, because the length of those time periods is variable.