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

Time:2021-1-19

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

Select timestamp diff (type, start time, end time)

Seconds of phase difference:


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

Minutes of difference:


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

Hours of difference:


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

Days of difference:


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

Weeks of phase difference:


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

Quarters of difference:


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

Months of difference:


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

Years of difference:


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

Get current date:


SELECT NOW()
SELECT CURDATE()

Add one day to current date:


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

Decrease the current date by one day:


SELECT DATE_SUB(CURDATE(),INTERVAL 1 DAY)

Add one week to current date:


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

Current date increased by 1 month:

SELECT DATE_SUB(CURDATE(),INTERVAL -1 MONTH)
FRAC_ Second MS
Second second
Minute
Hour
Day day
Week week
Month
Quater quarter
Year

summary

The above is Xiaobian’s introduction to you. MySQL calculates the days, months and years of the difference between the two dates. I hope it can help you. If you have any questions, please leave me a message and Xiaobian will reply you in time. Thank you very much for your support to developer!
If you think this article is helpful to you, please reprint, please indicate the source, thank you!

Recommended Today

Practice of query operation of database table (Experiment 3)

Following the previous two experiments, this experiment is to master the use of select statements for various query operations: single table query, multi table connection and query, nested query, set query, to consolidate the database query operation.Now follow Xiaobian to practice together!Based on the data table (student, course, SC, teacher, TC) created and inserted in […]