Member-only story
MySQL DATE Calculations using INTERVAL — with examples
Recently in my day job, while developing the back-end of a reporting dashboard with PHP and MySQL, I noticed some interesting differences in DATE math calculations. The examples used in this post are purely arbitrary but stem from lessons learned, therefore, I feel they are definitely worth sharing…
OS, Software, and DB used:
- OpenSuse Leap 15.1
- MySQL 8.0.19
Self-Promotion:
If you enjoy the content written here, by all means, share this blog and your favorite post(s) with others who may benefit from or like it as well. Since coffee is my favorite drink, you can even buy me one if you would like!
My requirement was to run an analysis on specific assets based on the current date and the day before, eventually providing those query results to the front-end dashboard with PHP and MySQL. It’s quite simple using MySQL’s built-in CURRENT_DATE() function to determine what the current date is:
MySQL localhost:33060+ ssl SQL > SELECT CURRENT_DATE();
+ — — — — — — — — +
| CURRENT_DATE() |
+ — — — — — — — — +
| 2020–02–25 |
+ — — — — — — — — +
1 row in set (0.0006 sec)And to figure out what yesterday was? Also, quite easy. Just subtract 1 (day) from the current date:
