skip to content

SQL: Postgres vs. MySQL

 Tweet1 Share0 Tweets

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

Send a message to The Art of Web:


used only for us to reply, and to display your gravatar.

CAPTCHA

<- copy the digits from the image into this box

press <Esc> or click outside this box to close

Post your comment or question
top