Help Center/
GaussDB/
Developer Guide(Distributed_V2.0-8.x)/
SQL Reference/
Functions and Operators/
Date and Time Processing Functions and Operators/
TIMESTAMPDIFF
Updated on 2025-05-29 GMT+08:00
TIMESTAMPDIFF
TIMESTAMPDIFF(unit, timestamp_expr1, timestamp_expr2)
Description: Returns the result of timestamp_expr2 – timestamp_expr1 in the specified unit. This function is equivalent to timestamp_diff(text, timestamp, timestamp).
Parameters:
- timestamp_expr1 and timestamp_expr2 are of the time expression, text, datetime, date, or time type.
- unit indicates the unit of the difference between two parameters. The result is returned in the specified format.
Return type: bigint

- This function takes effect only in MySQL-compatible databases.
- When sql_compatibility is set to 'MYSQL', b_format_version is set to 5.7, and b_format_dev_version is set to s1, the called function is registered as b_timestampdiff. If the GUC parameter is not enabled in a MySQL-compatible database, the called function is registered as timestamp_diff. You can run the \df b_timestampdiff command to query the detailed input parameter and return value of the function.
The value range of unit is as follows:
- year
1 2 3 4 5
gaussdb=# SELECT TIMESTAMPDIFF(YEAR, '2018-01-01', '2020-01-01'); timestamp_diff ---------------- 2 (1 row)
- quarter
1 2 3 4 5
gaussdb=# SELECT TIMESTAMPDIFF(QUARTER, '2018-01-01', '2020-01-01'); timestamp_diff ---------------- 8 (1 row)
- month
1 2 3 4 5
gaussdb=# SELECT TIMESTAMPDIFF(MONTH, '2018-01-01', '2020-01-01'); timestamp_diff ---------------- 24 (1 row)
- week
1 2 3 4 5
gaussdb=# SELECT TIMESTAMPDIFF(WEEK, '2018-01-01', '2020-01-01'); timestamp_diff ---------------- 104 (1 row)
- day
- hour
1 2 3 4 5
gaussdb=# SELECT TIMESTAMPDIFF(HOUR, '2020-01-01 10:10:10', '2020-01-01 11:11:11'); timestamp_diff ---------------- 1 (1 row)
- minute
1 2 3 4 5
gaussdb=# SELECT TIMESTAMPDIFF(MINUTE, '2020-01-01 10:10:10', '2020-01-01 11:11:11'); timestamp_diff ---------------- 61 (1 row)
- second
1 2 3 4 5
gaussdb=# SELECT TIMESTAMPDIFF(SECOND, '2020-01-01 10:10:10', '2020-01-01 11:11:11'); timestamp_diff ---------------- 3661 (1 row)
Parent topic: Date and Time Processing Functions and Operators
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
The system is busy. Please try again later.
For any further questions, feel free to contact us through the chatbot.
Chatbot