MySQL Date and Time Arithmetic

One thing that sometimes bugs me in MySQL is the huge amount of errata. The database is packed with features and abilities that are side-effects from "historical decisions". Decisions that were made when it wasn't planned to be an enterprise product, or changes made to ease porting applications to MySQL from other databases. Legacy, so to speak.

The timestamp data type is a good example - the timestamp manual section is a long list of exceptions and special cases. I usually re-read the manual page before using it. Another good example is the amount of date and time functions that exist in MySQL. There are way too many of them, some overlapping in function, and some just exist as aliases to other functions for compatibility. Just go over the list.

On the other hand, this artistic freedom has its merits.
For example, take the date arithmetic. Chances are that if you came from SQL Server background like me, you were used to all the variations on the DATEDIFF and DATEADD commands. While it works as well (the DATEADD function is there and works almost the same way), MySQL supports a much readable form of date and time arithmetic.

For example:

SELECT NOW() - INTERVAL 5 MINUTE

Will return, naturally, current date+time minus 5 minutes. Or:

SELECT NOW() + INTERVAL 10 DAY

You guessed it, this will return the current date+time plus 10 days.

Clean and simple, isn't it? The syntax goes like this:

date + INTERVAL expression unit

The unit can be MINUTE, HOUR, DAY, MONTH, YEAR etc. The expression can be any legal T-SQL expression, and the date is just the date variable, which can be a result from another expression, a variable or a table column. Check out the manual for more examples.

This is an elegant solution to a common problem, which is also easy to remember and use. Why so few people use it is a mystery to me - I've almost never seen it in code samples.