How to extract year and month from date in PostgreSQL

There are various way to get year and month from date in PostgreSQL.

1. Extract

The extract function retrieves subfields such as year or hour from date/time values. source must be a value expression of type timestamptime, or interval. (Expressions of type date are cast to timestamp and can therefore be used as well.) field is an identifier or string that selects what field to extract from the source value. 

Syntax: EXTRACT(field FROM source)

Example:

SELECT EXTRACT(YEAR FROM Date '2006-01-01'); // 2006
SELECT EXTRACT(DAY FROM Date '2006-01-05'); // 05
SELECT EXTRACT(MONTH FROM Date '2006-01-11'); // 01
SELECT EXTRACT(MONTH FROM TIMESTAMP '2001-02-16 20:38:40'); // 02

2. DATE_PART

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

Syntax: date_part('field', source)

Here the field parameter needs to be a string value, not a name. The valid field names for date_part are the same as for extract.

Example:

SELECT DATE_PART('day', TIMESTAMP '2001-02-16 20:38:40'); // 02
SELECT DATE_PART('year', Date '2006-01-01'); // 2006
SELECT DATE_PART('day', Date '2006-01-05'); // 05
SELECT DATE_PART('month', Date '2006-01-11'); // 01

3. to_char

We can data formatting method ‘to_char‘ to extract desire value from date string.

SELECT to_char(DATE '2006-01-05', 'DD'); //05
SELECT to_char(DATE '2006-01-05', 'MM'); //01
SELECT to_char(DATE '2006-01-05', 'YYYY'); //2006
SELECT to_char(DATE '2006-01-05', 'YY'); //06
SELECT to_char(DATE '2006-01-05', 'MM'); //01
SELECT to_char(TIMESTAMP '2001-02-16 20:38:40', 'DD'); //16
SELECT to_char(DATE'2001-02-16 20:38:40', 'DD'); //16
SELECT to_char(TIMESTAMP '2001-02-16 20:38:40', 'HH24'); //20

References: to_char and PGSQL Date-Time

Leave a Reply