How to get the number of days in a month?

Question

I am trying to get the following in Postgres:

select day_in_month(2); 

Expected output:

28 

Is there any built-in way in Postgres to do that?

Answer 1

SELECT       DATE_PART('days',          DATE_TRUNC('month', NOW())          + '1 MONTH'::INTERVAL          - '1 DAY'::INTERVAL     ) 

Substitute NOW() with any other date.

Answer 2

Using the smart "trick" to extract the day part from the last date of the month, as demonstrated by Quassnoi. But it can be a bit simpler / faster:

SELECT extract(days FROM date_trunc('month', now()) + interval '1 month - 1 day'); 

Rationale

extract is standard SQL, so maybe preferable, but it resolves to the same function internally as date_part(). The manual:

The date_part function is modeled on the traditional Ingres equivalent to the SQL-standard function extract:

But we only need to add a single interval. Postgres allows multiple time units at once. The manual:

interval values can be written using the following verbose syntax:

[@] quantity unit[quantity unit...] [direction]

where quantity is a number (possibly signed); unit is microsecond, millisecond, second, minute, hour, day, week, month, year, decade, century, millennium, or abbreviations or plurals of these units;

ISO 8601 or standard SQL format are also accepted. Either way, the manual again:

Internally interval values are stored as months, days, and seconds. This is done because the number of days in a month varies, and a day can have 23 or 25 hours if a daylight savings time adjustment is involved. The months and days fields are integers while the seconds field can store fractions.

(Output / display depends on the setting of IntervalStyle.)

The above example uses default Postgres format: interval '1 month - 1 day'. These are also valid (while less readable):

interval '1 mon - 1 d' -- unambiguous abbreviations of time units are allowed 

IS0 8601 format:

interval '0-1 -1 0:0' 

Standard SQL format:

interval 'P1M-1D'; 

All the same.