MySQL calculates a two field date difference

One, Timestampdiff

Grammar:

Timestampdiff (INTERVAL,DATETIME_EXPR1,DATETIME_EXPR2).

Description

Returns an integer difference between a date or datetime expression Datetime_expr1 and datetime_expr2the. The unit of the result is given by the interval parameter. The parameter must be one of the following values:

1. Frac_second. Indicates that the interval is milliseconds
2. SECOND. Seconds
3. MINUTE. Minutes
4. HOUR. Hours
5. Day. Days
6. WEEK. Week
7. MONTH. Month
8. QUARTER. Quarter
9. Year. Years

Second, str_to_date

MySQL built-in function that uses str_to_date () to convert a string to a date inside MySQL.

Example: The delimiter is the same, month and day

Select Str_to_date (' 2008-4-2 15:3:28 ', '%y-%m-%d%h:%i:%s ');

Select Str_to_date (' 2008-08-09 08:9:30 ', '%y-%m-%d%h:%i:%s ');

Example: The following calculates the time difference of two fields (year) Note: The second time field does not have a time format

SELECT user_id as name,
(case is sex=0 then ' women ' ELSE ' END) As Sex,
Mobileno as Telephone,
StartDate as entry date,
EndDate as date of commencement,
(Case is enddate>0 then
Timestampdiff (Year,str_to_date (StartDate, '%y-%m-%d%Y '), EndDate)
ELSE
Timestampdiff (Year,str_to_date (StartDate, '%y-%m-%d%Y '), LocalTime ()) END) as seniority,
Curstar as Star,
Curshopname as Store,
Strength as advantage,
Weakness as disadvantage,
Improve as Progress point,
Memo as remark,
(case is enddate>0 then ' quit ' ELSE ' in Service ' END) As Status

From Ft_sales

MySQL calculates a two field date difference