SELECT CAST(MONTH AS DECIMAL(6, 2)) + '-' + CAST(YEAR as DECIMAL(5, 2)) FROM posts
read also : PhpMyAdmin vs MySQL
Extract Month and Year from a Postgres DB:
One of the most common queries that needs to be executed on a Postgres database is extracting the month and year from a particular record. It can be done using various SQL commands but below are some sql snippets that will show you how to do it.
SELECT MONTH(CURRENT_DATE) as Month, YEAR(CURRENT_DATE) as Year
FROM table_name;
Select Month from date
This will give you the month as a number between 1 and 12 and Year as a four digit number.
SELECT MONTH(CURRENT_DATE) as Month, YEAR(CURRENT_DATE) as Year
FROM table_name
SELECT DATE_FORMAT(CURRENT_DATE,'%Y') as Month, DATE_FORMAT(CURRENT_DATE,'%m') as Year
FROM table_name;
SELECT NOW() as Month, NOW() as Year
FROM table_name;
The Results of Extracting Month and Year from a Postgres DB:
Lets take a look at some of the results that will be generated by the preceding SQL commands.
SELECT MONTH(CURRENT_DATE) as Month, YEAR(CURRENT_DATE) as Year
FROM table_name;
SELECT Month from date
This will give you the month as a number between 1 and 12 and Year as a four digit number.
SELECT MONTH(CURRENT_DATE) as Month, YEAR(CURRENT_DATE) as Year
FROM table_name
SELECT DATE_FORMAT(CURRENT_DATE,'%Y') as Month, DATE_FORMAT(CURRENT_DATE,'%m') as Year
FROM table_name
SELECT NOW() as Month, NOW() as Year
FROM table_name;
The SELECT NOW() as Month, NOW() as Year from table_name will give you the current date and the corresponding month and year.
Extracting the month and year from a Postgres DB is a simple task that can be done using various SQL commands. The results of the commands will vary depending on the table that is being queried.
Top comments (1)
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_nameclause.For example:
is unnecessary and will cause PostgreSQL to complain about
table_nameif you copy+paste intopsql(or other query editor).This is a better form of the example syntax:
Now on to specifics
To get the month from a date or timestamp/timestamptz data type in PostgreSQL, use the following functions:
Will give you the month as an integer from 1 to 12.
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
monthor'month'toyearor'year'.This is not valid for PostgreSQL.
First and foremost in this example is that the operation does not agree with the label.
The equivalent in PostgreSQL is
to_char:This will return a zero-padded 2-character string for month and a 4-digit string for 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.