MYSQL Calculate Difference Between 2 Dates (Simple Examples)

Welcome to a quick tutorial and examples on how to calculate the difference between dates in MYSQL – In minutes, days, months, and years. Yes, these are probably the most common questions. How to calculate the difference between 2 dates? How to add or minus days? How many days have passed till today? Let us walk through them quickly:

To calculate the difference between dates in days – SELECT DATEDIFF(`DATE-A`, `DATE-B`) FROM `TABLE`
Alternatively, for the difference between dates in minutes, hours, days, weeks, months, or years – SELECT TIMESTAMPDIFF(UNIT, `DATE-A`, `DATE-B`) FROM `TABLE`. UNIT can be SECOND MINUTE HOUR DAY WEEK MONTH YEAR.
To calculate the age of a person or item – SELECT TIMESTAMPDIFF(YEAR, `DATE-A`, NOW()) FROM `TABLE`

That should cover the basics, but read on for detailed examples!

Download & Notes

MYSQL Date Difference

Extras

The End

DOWNLOAD & NOTES

Here is the download link to the example code, so you don’t have to copy-paste everything.

EXAMPLE CODE DOWNLOAD

Source code on GitHub Gist

Just click on “download zip” or do a git clone. I have released it under the MIT license, so feel free to build on top of it or use it in your own project.

SORRY FOR THE ADS...

But someone has to pay the bills, and sponsors are paying for it. I insist on not turning Code Boxx into a "paid scripts" business, and I don't "block people with Adblock". Every little bit of support helps.

Buy Me A Coffee Code Boxx eBooks

MYSQL DATE TIME DIFFERENCE

All right, let us now get into the examples of calculating the differences between date and time in MYSQL.

DUMMY STOCK TABLE

x-dummy.sql

CREATE TABLE `stock` (
  `item_id` bigint(20) NOT NULL,
  `item_name` varchar(255) NOT NULL,
  `item_in` datetime NOT NULL,
  `item_out` datetime DEFAULT NULL,
  `item_expire` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
ALTER TABLE `stock`
  ADD PRIMARY KEY (`item_id`);
 
ALTER TABLE `stock`
  MODIFY `item_id` int(11) NOT NULL AUTO_INCREMENT;
 
INSERT INTO `stock` (`item_id`, `item_name`, `item_in`, `item_out`, `item_expire`) VALUES
(1, 'Cereal', '2021-05-03 21:35:36', '2021-05-12 12:34:17', '2023-05-03 00:00:00'),
(2, 'Dried Fish', '2021-06-16 21:23:11', '2021-06-17 08:12:34', '2021-09-16 00:00:00'),
(3, 'Coffee', '2021-03-17 13:35:24', '2021-04-14 11:45:26', '2024-08-01 00:00:00');

For this example, we will use a simple “stock in the warehouse” table.

Field	Description
`item_id`	Item ID, primary key.
`item_name`	Name of the item.
`item_in item_out`	Timestamp, when the stock is received and retrieved.
`item_expire`	Timestamp, item expiry date.

EXAMPLE 1) DIFFERENCE BETWEEN 2 DATES IN DAYS

1-diff-days.sql

-- (A) DIFFERENCE IN DAYS
SELECT `item_name`, DATEDIFF(`item_out`, `item_in`) AS `days_in_warehouse` FROM `stock`
 
-- (B) ALTERNATIVE DIFFERENCE IN DAYS
SELECT `item_name`, TIMESTAMPDIFF(DAY, `item_in`, `item_out`) AS `days_in_warehouse` FROM `stock`

To calculate the difference between dates, there are 2 SQL functions we can use – DATEDIFF() and TIMESTAMPDIFF(). While they look similar at first, there is a critical difference between the both of them.

DATEDIFF will actually round the days up, while TIMESTAMPDIFF will not.
In the above example, dried fish has been in the warehouse for less than 24 hours. DATEDIFF will return 1 day, while TIMESTAMPDIFF returns 0.

EXAMPLE 2) DIFFERENCE BETWEEN 2 DATES IN MINUTES, HOURS, WEEKS, MONTHS, YEARS

2-diff-others.sql

SELECT `item_name`, TIMESTAMPDIFF(SECOND, `item_in`, `item_out`) AS `diff` FROM `stock`
SELECT `item_name`, TIMESTAMPDIFF(MINUTE, `item_in`, `item_out`) AS `diff` FROM `stock`
SELECT `item_name`, TIMESTAMPDIFF(HOUR, `item_in`, `item_out`) AS `diff` FROM `stock`
SELECT `item_name`, TIMESTAMPDIFF(DAY, `item_in`, `item_out`) AS `diff` FROM `stock`
SELECT `item_name`, TIMESTAMPDIFF(WEEK, `item_in`, `item_out`) AS `diff` FROM `stock`
SELECT `item_name`, TIMESTAMPDIFF(MONTH, `item_in`, `item_out`) AS `diff` FROM `stock`
SELECT `item_name`, TIMESTAMPDIFF(YEAR, `item_in`, `item_out`) AS `diff` FROM `stock`

As in the introduction snippet, we can set TIMESTAMPDIFF() to calculate the difference in other units.

EXAMPLE 3) MANUAL CALCULATION & ROUNDING OFF

3-manual-round.sql

-- (A) DIFFERENCE IN HOURS
SELECT `item_name`, (TIMESTAMPDIFF(MINUTE, `item_in`, `item_out`) / 60) AS `hours` FROM `stock`

-- (B) DIFFERENCE IN HOURS (CEILING)
SELECT `item_name`, CEIL(TIMESTAMPDIFF(MINUTE, `item_in`, `item_out`) / 60) AS `hours` FROM `stock`

-- (C) DIFFERENCE IN HOURS (FLOOR)
SELECT `item_name`, FLOOR(TIMESTAMPDIFF(MINUTE, `item_in`, `item_out`) / 60) AS `hours` FROM `stock`

-- (D) DIFFERENCE IN HOURS (ROUND OFF)
SELECT `item_name`, ROUND(TIMESTAMPDIFF(MINUTE, `item_in`, `item_out`) / 60) AS `hours` FROM `stock`

As you already know, TIMESTAMPDIFF() will not round the time difference up. So, what if we need to calculate the time difference, rounded-up? Sadly, the only way around is to do it Mathematically. In this example:

TIMESTAMPDIFF(MINUTE, `item_in`, `item_out`) will give the difference in minutes.
TIMESTAMPDIFF(MINUTE, `item_in`, `item_out`) / 60 will give the difference in hours, but with decimal points.
Lastly, CEIL() will always round-up, FLOOR() will always round down, and ROUND() should be Captain Obvious.

EXAMPLE 4) CALCULATE AGE OF ITEMS

4-age.sql

-- (A) GET CURRENT DATE TIME
SELECT NOW()
 
-- (B) TO CALCULATE EXPIRY DAYS
SELECT `item_name`, DATEDIFF(NOW(), `item_expire`) AS `expire_days` FROM `stock`

-- (C) HOW LONG HAS ITEM LEFT WAREHOUSE?
SELECT `item_name`, DATEDIFF(NOW(), `item_out`) AS `left_days` FROM `stock` WHERE `item_out` IS NOT NULL
 
-- (D) ALTERNATIVELY (REMINDER ON ROUNDING)
SELECT `item_name`, TIMESTAMPDIFF(DAY, `item_out`, NOW()) AS `left_years` FROM `stock` WHERE `item_out` IS NOT NULL

Use NOW() to get the current date and time.
To calculate the age of items, very simply – DATEDIFF(NOW(), `DATE-FIELD`) or TIMESTAMPDIFF(UNIT, `DATE-FIELD`, NOW()).

EXAMPLE 5) ADD OR MINUS DAYS

5-add-minus.sql

-- (A) TO ADD DAYS
SELECT `item_name`, DATE_ADD(`item_expire`, INTERVAL 7 DAY) AS `after_expire` FROM `stock`;
 
-- (B) TO SUBTRACT DAYS
SELECT `item_name`, DATE_SUB(`item_expire`, INTERVAL 7 DAY) AS `before_expire` FROM `stock`;

To add to a date – DATE_ADD(DATE-FIELD, INTERVAL)
To subtract from a date – DATE_SUB(DATE-FIELD, INTERVAL)

In this example, it is very useful for serving reminders – Which items are going to expire, or which items are best to be discarded after expiry.

EXTRAS

That’s all for the tutorial, and here is a small section on some extras and links that may be useful to you.

LINKS & REFERENCES

Function	Description	Link
`DATEDIFF(DATE-A, DATE-B)`	Calculate the difference between 2 dates in days.	Click Here
`TIMESTAMPDIFF(UNIT, DATE-A, DATE-B)`	Calculate the difference between 2 dates in seconds, minutes, hours, days, weeks, months, and years.	Click Here
`NOW()`	Get the date and time now.	Click Here
`DATE_ADD(DATE, INTERVAL)`	Add to the specified date.	Click Here
`DATE_SUB(DATE, INTERVAL)`	Minus from the specified date.	Click Here

THE END

Thank you for reading, and we have come to the end. I hope that it has helped you to better understand, and if you want to share anything with this guide, please feel free to comment below. Good luck and happy coding!