MySQL calculates the number of days, months, and years between two dates

The MySQL built-in date function TIMESTAMPDIFF calculates the number of seconds, minutes, hours, days, weeks, quarters, months, years between two dates, and increases or decreases the current date by one day, a week, etc.

SELECT TIMESTAMPDIFF(type, start time, end time)

Difference in seconds:

SELECT TIMESTAMPDIFF(SECOND,'1993-03-23 00:00:00',DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%S'))

Difference in minutes:

SELECT TIMESTAMPDIFF(MINUTE,'1993-03-23 00:00:00',DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%S'))

Difference in hours:

SELECT TIMESTAMPDIFF(HOUR,'1993-03-23 00:00:00 00:00:00',DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%S'))

Difference in days:

SELECT TIMESTAMPDIFF(DAY,'1993-03-23 00:00:00',DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%S'))

Weeks difference:

SELECT TIMESTAMPDIFF(WEEK,'1993-03-23 00:00:00',DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%S'))

Number of quarters difference:

SELECT TIMESTAMPDIFF(QUARTER,'1993-03-23 00:00:00',DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%S'))

Difference in months:

SELECT TIMESTAMPDIFF(MONTH,'1993-03-23 00:00:00',DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%S'))

Difference in years:

SELECT TIMESTAMPDIFF(YEAR,'1993-03-23 00:00:00',DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%S'))

Get the current date:

SELECT NOW()
SELECT CURDATE()

Add one day to the current date:

SELECT DATE_SUB(CURDATE(),INTERVAL -1 DAY)

Subtract one day from the current date:

SELECT DATE_SUB(CURDATE(),INTERVAL 1 DAY)

Add one week to the current date:

SELECT DATE_SUB(CURDATE(),INTERVAL -1 WEEK)
SELECT DATE_SUB(NOW(),INTERVAL -1 MONTH)

Add one month to the current date:

SELECT DATE_SUB(CURDATE(),INTERVAL -1 MONTH)
FRAC_SECOND milliseconds SECOND seconds MINUTE minutes HOUR hours DAY days WEEK weeks MONTH months QUARTER quarters YEAR years

Summarize

The above is the introduction of MySQL to calculate the number of days, months, and years between two dates. I hope it will be helpful to everyone. If you have any questions, please leave me a message and I will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!
If you find this article helpful, please feel free to reprint it and please indicate the source. Thank you!

You may also be interested in:

How to query the maximum number of consecutive login days between two dates in MySQL
SQL query for users who have logged in for at least n consecutive days
mysql generates continuous dates and variable assignments
How to calculate the number of consecutive login days in MySQL

<<: JS implements WeChat's "shit bombing" function

>>: How to install JDK and set environment variables in Linux (this article is enough)