Get "PHP 8 in a Nuthshell" (Soon includes PHP 8.4)
Amit Merchant

Amit Merchant

A blog on PHP, JavaScript, and more

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:

  • MICROSECOND
  • SECOND
  • MINUTE
  • HOUR
  • DAY
  • WEEK
  • MONTH
  • QUARTER
  • YEAR

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.

Comments?