Mysql To Postgresql: How To Modify This Sql Query?

I have this MySQL query that makes use of MONTH() and YEAR(): SELECT MONTH(created_at) AS month, YEAR(created_at) AS year FROM users GROUP BY MONTH(created_at), YEAR(create

Solution 1:

SELECT 
  extract(MONTH from created_at) AS month, 
  extract(YEAR from created_at) AS year 
FROM users 
GROUP BY extract(MONTH from created_at), extract(YEAR from created_at) 
ORDER BY extract(MONTH from created_at), extract(YEAR from created_at) 

Here is the up-to-date manual http://www.postgresql.org/docs/current/static/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT

Btw: This is standard (ANSI) SQL that works on MySQL as well.

Solution 2:

http://www.postgresql.org/docs/9.0/static/functions-datetime.html

SELECTEXTRACT(MONTHFROM created_at) ASmonth, 
  EXTRACT(YEARFROM created_at) ASyearFROM users 
GROUPBYEXTRACT(MONTHFROM created_at), EXTRACT(YEARFROM created_at)
ORDERBYEXTRACT(YEARFROM created_at), EXTRACT(MONTHFROM created_at)

This syntax should work on PostgreSQL, Oracle and Teradata