You might have gathered from other articles that
I'm not a huge fan of MySQL, but sometimes it's necessary to port code
between different databases so here's a quick reference of some of the more useful functions.
Extracting values from date fields
| PostgreSQL |
MySQL |
| DATE_PART('year', datefield) |
YEAR(datefield) |
| DATE_PART('month', datefield) |
MONTH(datefield) |
| DATE_PART('day', datefield) |
DAY(datefield) |
Note: MySQL also has an EXTRACT function which is similar to the standard DATE_PART e.g. EXTRACT(YEAR FROM 'datefield')
Adding and Subtracting Dates
| PostgreSQL |
MySQL |
| datefield + 6 |
DATE_ADD(datefield, INTERVAL 6 DAY) |
| datefield + INTERVAL '6 months' |
DATE_ADD(datefield, INTERVAL 6 MONTH) |
| datefield - INTERVAL '6 months' |
DATE_SUB(datefield, INTERVAL 6 MONTH) |
Number of Days between Dates
| PostgreSQL |
MySQL |
| datefield - datefield |
TO_DAYS(datefield) - TO_DAYS(datefield) |
References
< SQL
