Discussion on: Extract month and year from Postgres DB

A rebuttal using PostgreSQL 13

This article has a number of errors. I will make an attempt to correct them.

First:

These SQL snippets appear to be valid in MySQL, but not valid in PostgreSQL.

Second:

The snippets are a bit off. For snippets like these, you don't need the from table_name clause.

For example:

SELECT MONTH(CURRENT_DATE) as Month, YEAR(CURRENT_DATE) as Year
FROM table_name

is unnecessary and will cause PostgreSQL to complain about table_name if you copy+paste into psql (or other query editor).

This is a better form of the example syntax:

SELECT MONTH(CURRENT_DATE) as Month, YEAR(CURRENT_DATE) as Year;

Now on to specifics

SELECT MONTH(CURRENT_DATE) as Month, YEAR(CURRENT_DATE) as Year

To get the month from a date or timestamp/timestamptz data type in PostgreSQL, use the following functions:

select extract(month from now());
 date_part 
-----------
         7
(1 row)

select date_part('month', now());
 date_part 
-----------
         7
(1 row)

Will give you the month as an integer from 1 to 12.

select date_trunc('month', now());
       date_trunc       
------------------------
 2022-07-01 00:00:00+00
(1 row)

Will always return a timestamptz in UTC zone of the date or timestamp with day and all time information truncated to zeroes.

To get the year, simply change month or 'month' to year or 'year'.

SELECT Month from date

This is not valid for PostgreSQL.

SELECT DATE_FORMAT(CURRENT_DATE,'%Y') as Month, DATE_FORMAT(CURRENT_DATE,'%m') as Year

First and foremost in this example is that the operation does not agree with the label.

The equivalent in PostgreSQL is to_char:

SELECT to_char(CURRENT_DATE,'mm') as "Month", to_char(CURRENT_DATE,'YYYY') as "Year"
 Month | Year 
-------+------
 07    | 2022
(1 row)

This will return a zero-padded 2-character string for month and a 4-digit string for year.

SELECT NOW() as Month, NOW() as Year

Maybe this works in MySQL, but in PostgreSQL, you will get two timestamps with weird labels.

Check here for PostgreSQL date and time functions and operators.