TIMESTAMPDIFF is a function used to calculate the difference between two datetimes in MySQL and MariaDB databases. Its basic syntax is as follows:
TIMESTAMPDIFF(unit, start_datetime, end_datetime)
Where, unit represents the time unit to be calculated, which can be one of the following values:
MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
start_datetime and end_datetime are the two datetime values to be calculated respectively. These values can be column names, constants, or expressions.
Here are some example usages:
1. Calculate the difference in days between two date times:
SELECT TIMESTAMPDIFF(DAY, '2021-01-01', '2021-01-10');
This will return 9, indicating that the second date is different from the first There are 9 days between dates.
2. Calculate the hour difference between two date times:
SELECT TIMESTAMPDIFF(HOUR, '2021-01-01 12:00:00', '2021-01-02 12:00:00');
This will return 24, indicating that the second date time is 24 hours apart from the first date time.
3. Use column names to calculate:
Assume that there is a table containing two columns: start_datetime and end_datetime. We can use the TIMESTAMPDIFF function to calculate the difference between them:
SELECT TIMESTAMPDIFF(MONTH, start_datetime, end_datetime) AS month_diff FROM your_table;
This query will return the month difference between the start_datetime and end_datetime columns, and name it month_diff.
In general, the usage of TIMESTAMPDIFF function is very simple and intuitive. You just need to specify the time unit to be calculated and then provide the start and end datetime values. This function is very useful for scenarios where you need to perform datetime difference calculations in SQL queries.
The above is the detailed content of timestampdiff function usage. For more information, please follow other related articles on the PHP Chinese website!