CODEWITHSUNDEEP
sqlmysqlpostgresqlrefactoring
Shpigford
Shpigford

Reputation: 25358

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(created_at) 
ORDER BY YEAR(created_at), MONTH(created_at)

How would I modify that query to work with PostgreSQL?

Upvotes: 2

Views: 946

Answers (2)

user330315
user330315

Reputation: 

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.

Upvotes: 4

Cade Roux
Cade Roux

Reputation: 89701

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

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(YEAR FROM created_at), EXTRACT(MONTH FROM created_at)

This syntax should work on PostgreSQL, Oracle and Teradata

Upvotes: 2

Related Questions