mysql query days

MySQL Query Days

In database development, it is often necessary to query the time and date difference, such as calculating the number of days, hours, minutes, etc. between two dates. MySQL provides some functions to implement this function, which can easily calculate the difference between time and date.

1. DATEDIFF function

The DATEDIFF function returns the difference in days between two dates. The syntax is as follows:

DATEDIFF(date1,date2)

where date1 and date2 are two dates or datetime values, and the function returns the difference in days between the two dates. If date1 is later than date2, the return value is positive, otherwise the return value is negative.

For example:

SELECT DATEDIFF('2019-01-03','2018-12-31'); // 输出3

Query the difference in days of user registration in two tables:

SELECT DATEDIFF(reg_time,now()) AS days FROM user;

2. TIMESTAMPDIFF function

TIMESTAMPDIFF function returns two dates the time difference between. The syntax is as follows:

TIMESTAMPDIFF(unit,start,stop)

where unit represents the unit for calculating the time difference, which can be seconds (SECOND), minutes (MINUTE), hours (HOUR), days (DAY), weeks (WEEK), months (MONTH) ) or year (YEAR).

start and stop represent the start date and end date, which can be date or datetime values. The function returns the time difference between two dates in units. If start is later than stop, the return value is positive, otherwise the return value is negative.

For example:

SELECT TIMESTAMPDIFF(MONTH,'2018-02-28','2018-03-31'); // 输出1

Query the user’s registration duration:

SELECT TIMESTAMPDIFF(DAY,reg_time,now()) AS days FROM user;

3. TIMEDIFF function

TIMEDIFF function returns the time difference between two times , the format is hh:mm:ss. The syntax is as follows:

TIMEDIFF(time1, time2)

where time1 and time2 are time or time date values, and the function returns the time difference between time1 and time2.

For example:

SELECT TIMEDIFF('18:30:00', '17:00:00'); // 输出01:30:00

Query the delivery time of an order:

SELECT TIMEDIFF(end_time,start_time) AS duration FROM order WHERE order_id=123;

4. TIME_TO_SEC function and SEC_TO_TIME function

TIME_TO_SEC function converts the time value into Seconds, the syntax is as follows:

TIME_TO_SEC(time)

where time is the time or time date value, and the function returns the total seconds of time.

The SEC_TO_TIME function converts seconds into a time value. The syntax is as follows:

SEC_TO_TIME(seconds)

where seconds represents the number of seconds and the function returns the corresponding time value.

For example:

SELECT TIME_TO_SEC('01:30:00'); // 输出5400
SELECT SEC_TO_TIME(5400); // 输出01:30:00

5. Summary

MySQL provides multiple functions to calculate the time and date difference, which can be flexibly selected and used according to needs. In actual development, appropriate calculation methods should be selected based on specific business needs in order to better implement functions.

The above is the detailed content of mysql query days. For more information, please follow other related articles on the PHP Chinese website!