SQL: Postgres vs. MySQL
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
Send a message to The Art of Web:
press <Esc> or click outside this box to close