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) ?

Trying to write standard SQL is usually a mistake. Take a look at the databases available, see which features you need, then be brave and just choose a database that meets your needs and code against that. Use the features that database has effectively. "Generic SQL" typically means slow, unmaintainable SQL that only works on the two databases you tested it on. — Mark Byers
– Mark Byers, Commented Jul 11, 2010 at 17:22
To add to Mark's comment, if you need to support different database vendors - either look to an ORM (trading coding simplicity for performance) or use SQL customized to each database vendor (better performance, more work). — OMG Ponies
– OMG Ponies, Commented Jul 11, 2010 at 17:35

Frank Heikens · Accepted Answer · 2010-07-12 05:47:33Z

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';

answered Jul 12, 2010 at 5:47

Frank Heikens

129k26 gold badges157 silver badges153 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

daryn · Accepted Answer · 2010-07-11 17:15:46Z

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.

answered Jul 11, 2010 at 17:15

daryn

9265 silver badges11 bronze badges

1 Comment

user330315 Over a year ago

The operations timestamp + interval, timestamp - interval, timestamp - timestamp and date - date are specified in the SQL standard

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related