timestampdiff function usage

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!