MySql calculates the number of days, seconds, minutes, weeks, and hours between two dates and times.

Source: Internet
Author: User

MySql calculates the number of days, seconds, minutes, weeks, and hours between two dates and times.

Calculate the number of days, seconds, minutes, weeks, and hours of the difference between the two dates and times. Here we mainly share the implementation through the MySql built-in function TimeStampDiff.

The TimeStampDiff () function is provided by MySQL itself to calculate two time intervals. Syntax:

TIMESTAMPDIFF (unit, datetime_expr1, datetime_expr2)

Returns the integer difference between datetime_expr1 and datetime_expr2the. Unit units include: FRAC_SECOND (microseconds), SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, and YEAR. This parameter is interpreted as follows:

FRAC_SECOND indicates that the interval is millisecond.
SECOND seconds
MINUTE minutes
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR

For example:

# Calculate the number of weeks between two dates

select timestampdiff(week,'2011-09-30','2015-05-04');

# Calculate the number of days between two dates

select timestampdiff(day,'2011-09-30','2015-05-04');

In addition, the to_days function can be used to calculate the number of days for the difference between two dates or times. However, this function is not used for values before the occurrence of a calendar (1582), because when the calendar changes, the date of loss will not be taken into account. Therefore, for the date before January 1, 1582 (maybe another region is the next year), the result of this function is unreliable. The specific usage is as follows:

To_days (end_time)-to_days (start_time );

# Calculate the number of seconds between two dates/times:

select timestampdiff(SECOND,'2011-09-30','2015-05-04');

You can also use the MySql built-in function UNIX_TIMESTAMP, as shown below:

SELECT UNIX_TIMESTAMP (end_time)-UNIX_TIMESTAMP (start_time );

# Calculate the time score for the difference between two dates and times:

select timestampdiff(MINUTE,'2011-09-30','2015-05-04');

It can also be implemented as follows:

SELECT SEC_TO_TIME(UNIX_TIMESTAMP(end_time) - UNIX_TIMESTAMP(start_time));

The brief record is as above. The usage of related functions such as to_days UNIX_TIMESTAMP SEC_TO_TIME will be supplemented later.

Articles you may be interested in
  • Php calculates the number of days of the difference between two dates.
  • Php calculates the number of years, months, and days between two dates.
  • How many seconds ago php was implemented, how many minutes ago, and how many hours ago
  • Php obtains the start time and end time timestamp of the week of the specified date.
  • What does the statistical result mean when the average stay time is 0 s?
  • Solution to an eight-hour difference in the date time in php
  • PHP generates continuous numbers (letters) Array Function range () analysis, PHP lottery program function
  • Php summarizes the methods for obtaining the time interval. php displays the time interval of Forum posts.

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: info-contact@alibabacloud.com 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.