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(field
FROMsource
)
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