Temporal intervals are handy in MySQL
Say you want to find out the number of orders placed in the last 6 months. You can do this in MySQL using the DATE_SUB() function as follows.
SELECT COUNT(*) AS total_orders
FROM orders
WHERE order_date >= DATE_SUB(NOW(), INTERVAL 6 MONTH);
The above query will return the total number of orders placed in the last 6 months.
Now, one thing to note here is the INTERVAL clause. It’s called a temporal interval expression and is used to specify the number of units of time to subtract from the current date. In the above query, we are subtracting 6 months from the current date.
The INTERVAL clause follows the following syntax.
INTERVAL <number> <unit>
Here, <number> is the number of units of time to subtract from the current date, and <unit> is the unit of time. The <unit> can be one of the following:
MICROSECONDSECONDMINUTEHOURDAYWEEKMONTHQUARTERYEAR
And a lot of other units that you can use as well.
So, the following query would give us the date 6 months ago from the current date.
SELECT DATE_SUB(NOW(), INTERVAL 6 MONTH);
/* 2022-10-10 11:54:09 */
Or you can directly do arithmetic on the date as well.
SELECT CURRENT_DATE + INTERVAL 6 MONTH;
/* 2023-10-10 */
It surely is a handy feature only recently I came to know about.
Like this article?
Buy me a coffee
👋 Hi there! I'm Amit. I write articles about all things web development. You can become a sponsor on my blog to help me continue my writing journey and get your brand in front of thousands of eyes.
