How To Calculate Difference Between Dates In MYSQL – 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-1`, `DATE-2`) FROM `TABLE`
  • Alternatively, for the difference between dates in minutes, hours, days, weeks, months, or years – SELECT TIMESTAMPDIFF(UNIT, `DATE-1`, `DATE-2`) 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-FIELD`, NOW()) FROM `TABLE`

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

ⓘ I have included a zip file with all the source code at the start of this tutorial, so you don’t have to copy-paste everything… Or if you just want to dive straight in.

 

 

QUICK SLIDES

 

TABLE OF CONTENTS

Download & Notes Date Difference Useful Bits & Links
The End

 

DOWNLOAD & NOTES

Firstly, here is the download link to the example code as promised.

 

EXAMPLE CODE DOWNLOAD

Click here to download all the example source code, I have released it under the MIT license, so feel free to build on top of it or use it in your own project.

 

QUICK NOTES

If you spot a bug, please feel free to comment below. I try to answer questions too, but it is one person versus the entire world… If you need answers urgently, please check out my list of websites to get help with programming.

 

 

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

0-dummy.sql
CREATE TABLE `stock` (
  `item_id` int(11) 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=latin1;

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');
 
ALTER TABLE `stock`
  ADD PRIMARY KEY (`item_id`);
 
ALTER TABLE `stock`
  MODIFY `item_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

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.

 

USEFUL BITS & LINKS

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, 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

 

INFOGRAPHIC CHEAT SHEET

Calculate Date Time Difference In MYSQL (click to enlarge)

 

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!

Leave a Comment

Your email address will not be published. Required fields are marked *