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.
INTERVAL clause follows the following syntax.
INTERVAL <number> <unit>
<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:
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.
👋 Hi there! I'm Amit. I write articles about all things web development. If you like what I write and want me to continue doing the same, I would like you buy me some coffees. I'd highly appreciate that. Cheers!