MySQL Add and subtract time-function-time plus minus

Source: Internet
Author: User

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');


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


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


, 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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.