Mysql uses SQL to calculate the difference between two dates and times.

Mysql uses SQL to calculate the difference between two dates and times.

Requirement Description:

1. Calculate the duration of a link in the project and the number of times it is performed.

2. The duration must be displayed in minutes and counted.

Solution:

You can determine the number of seconds between the start time and end time of a link.

Code Section:

Note: The database uses Mysql and the persistent layer framework uses Mybatis.

The Code is as follows:

FLOOR (SUM (UNIX_TIMESTAMP (Start Time)-UNIX_TIMESTAMP (End Time)/COUNT (execution times)/60) AS averagetime

Code Description:

1. UNIX_TIMESTAMP ()

Return Value: the number of seconds from '2017-01-01 00:00:00 'to the current time. If the number of milliseconds is calculated, the unit conversion is required.

2. (SUM (UNIX_TIMESTAMP (Start Time)-UNIX_TIMESTAMP (End Time)/COUNT (execution times)/60)

Return Value: the average execution time (minutes) of a link ).

3. FLOOR ()

Return Value: The function returns only the integer part, and the fractional part is discarded.

Conclusion: The code above only calculates the difference between two dates, and does not describe the overall sorting and grouping of data queries.