How to write the MySQL DATE_ADD() in "generic" SQL

Question

I am writing my queries in 'standard' / 'generic' SQL so that it runs on any database (MySQL, Postgre etc).

I'd like to use DATE_ADD() of MySQL. In Postgre, it would be done with something like '2001-01-01'::timestamp + '1 year'::interval;.

Is there a more 'general' way of writing it so that it runs on both MySQL and Postgre (or others) ?

Answer 1

No, there are not SQL standard functions for date addition.

Depending on how you're building your app (using a database abstraction layer and/or interface layer), you could probably handle it in a generic way there, and still have the database doing the math, not your code.

Answer 2

In MySQL you don't need DATE_ADD, it has almost the same syntax as PostgreSQL:

MySQL: SELECT CAST('2001-01-01' AS date) + INTERVAL 1 year;

PostgreSQL: SELECT CAST('2001-01-01' AS date) + INTERVAL '1 year';