MySQL Add and subtract time-function-time plus minus

SelectTimediff ('23:40:00','18:30:00'); --subtract two time select substring (Timediff ('23:40:00','18:30:00'),1,5) ----“ to：Ten"Subtract back hours: minutesSelectDateDiff'2008-08-08','2008-08-01'); --7-----Two date subtractionSelectTo_days ('2008-09-08')-to_days ('2008-08-08')     -----Two date subtraction Select substring ('2009-06-17 10:00:00',1,Ten)   ----extract "Date" from DateTime (*********************The timestamp is the number of seconds elapsed from the start of January 1, 1970 to the target time. Can perform operations of two datetime intervals）

MySQL sets the two date format subtraction method:

　　

MySQL calculates a difference function for two datetime:

The first: The Timestampdiff function, which needs to pass in three parameters, the first is the type of comparison, can compare Frac_second, SECOND, MINUTE, HOUR, Day, WEEK, MONTH, Quarter, or year of several types, The second and third parameters are the two times to compare, the comparison is the last time minus one time, the following:

SELECT Timestampdiff (Day, ' 2012-10-01 ', ' 2013-01-13 ');

The return result is 104, here is the difference between the days of two times;

SELECT Timestampdiff (MONTH,'2012-10-01','2013-01-13');

这里比较的是两个时间的月份，返回的结果是3；

The second method: The DateDiff function, passing in the two date function, compares the day days, the first parameter minus the second parameter's days value, the following is used as follows:

SELECT DATEDIFF (' 2013-01-13 ', ' 2012-10-01 ');

The returned result is also 104.

Another date function,

The now () function returns the current time, month and day, and seconds, such as: 2008-12-29 16:25:46

The Curdate () function returns the month and day information: for example: 2008-12-29

The Curtime () function returns the time-of-day information for the current hour, such as: 16:25:46

Also, if we want to format a date with a date that contains a month or two, you can use the DATE function, such as

DATE (now ()) returns the 2008-12-29

DateDiff () Two date subtraction functions

2

2. <pre name="code" class="SQL" >select Timestampdiff (minute,now (), flowexpiredate) as Minsremaining from Flowdealorders WHERE sn=' 172150210001156 ' and orderstatus=' in use ' and Sysstatus=1
6. Timestampdiff functions can be used in MySQL
8. Format Timestampdiff (Show time format, start time, end time)
10. The first property can also be: hours, minutes, seconds, etc.

3、

, the two datetime fields in MySQL are subtracted from the assumption that the table name is Tblname, two datetime field names are Begindatetime,enddatetime, and the following are the SQL statements that subtract from the two MySQL date fields. This way two fields cross days, months, and years without problems. Get the number of seconds between two date fields Selec t (Unix_timestamp (EndDateTime)-Unix_timestamp (begindatetime)) Dif_second fromTblname get a score between two date fields Selec t (Unix_timestamp (EndDateTime)-Unix_timestamp (Begindatetime))/ -Dif_minute fromTblname Gets the number of days between two date fields Selec t (Unix_timestamp (EndDateTime)-Unix_timestamp (Begindatetime))/( -* -* -) Dif_minute fromtblname Second, MySQL in two time field subtraction if two fields are of type A, if two times on the same day, subtraction can get a difference in the number of seconds, but if cross days, months, years have problems. Selec t (time_to_sec (EndDateTime)-Time_to_sec (begindatetime)) Dif_second fromTblname

MySQL Add and subtract time-function-time plus minus