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 timestamp, time, 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(fieldFROMsource)
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'); // 013. 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
