Any alternative to MONTH() and YEAR() functions which are common to both MySQL and PostgreSQL

Question

Writing queries (a sql file) that can run both in MySQL and PostgreSQL, everything is OK so far except the following:

SELECT MONTH(Date), YEAR(Date) FROM Ticket;

for extracting the month number (9,10,11 ..) and year (2011, 2012..) from the Date column (yes thats true, I cannot change the name of this column) in the Ticket table. It works fine in MySQL but when I run the query in PostgreSQL, it gives the following error message for both MONTH() and YEAR().

ERROR:  function month(date) does not exist
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

After researching a little bit I came along some other functions that will do the job in Postgres, but I am afraid those might not work in MySQL. Strongly looking forward for any possible solution.

Answer 1

Well if you use an alias on the front of your field and use EXTRACT instead, the same query will work for both PostgreSQL and MySQL:

SELECT EXTRACT(MONTH FROM t.Date) AS MonthOfDate, 
       EXTRACT(YEAR FROM t.Date) AS YearOfDate
FROM Ticket t;

PostgreSQL SQL Fiddle

MySQL SQL Fiddle