MYSQL

CALCULATE DATE TIME DIFFERENCE

(quick guide & examples)

DATEDIFF WILL ROUND UP, LESS THAN 24 HRS COUNT AS 1 DAY.  SELECT DATEDIFF(`DATE-A`, `DATE-B`) FROM `TABLE` 

DIFFERENCE IN DAYS

01

TIMESTAMPDIFF WILL NOT ROUND UP, LESS THAN 24 HRS COUNT AS 0. SELECT TIMESTAMPDIFF(DAY, `DATE-A`, `DATE-B`) FROM `TABLE`

SIMPLY SET THE UNIT FOR TIMESTAMPDIFF SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, YEAR  SELECT TIMESTAMPDIFF(UNIT, `DATE-A`, `DATE-B`) FROM `TABLE`

DIFFERENCE IN OTHER UNITS

02

MANUAL CALCULATE HOURS TIMESTAMPDIFF(MINUTE, `DATE-A`, `DATE-B`) / 60

CALCULATION & ROUNDING OFF

03

TO ROUND UP, DOWN, OFF CEIL(TIMESTAMPDIFF(MINUTE, `DATE-A`, `DATE-B`) / 60) FLOOR(TIMESTAMPDIFF(MINUTE, `DATE-A`, `DATE-B`) / 60) ROUND(TIMESTAMPDIFF(MINUTE, `DATE-A`, `DATE-B`) / 60)

AGE IN DAYS DATEDIFF(NOW(), `DATE-FIELD`)

CALCULATE AGE OF ITEMS

04

AGE IN OTHER UNITS TIMESTAMPDIFF(UNIT, NOW(), `DATE-FIELD`)

ADD TO DATE DATE_ADD(`DATE-FIELD`, INTERVAL 7 DAY)

DATE ADD OR SUBTRACT

05

MINUS FROM DATE DATE_SUB(`DATE-FIELD`, INTERVAL 3 DAY)