更新时间:2025-05-29 GMT+08:00
TIMESTAMPDIFF
TIMESTAMPDIFF(unit, timestamp_expr1, timestamp_expr2)
描述:timestampdiff函数计算两个时间之间(timestamp_expr2-timestamp_expr1)的差值,并以unit形式返回结果。等效于timestamp_diff(text, timestamp, timestamp)。
参数:
- timestamp_expr1以及timestamp_expr2的类型为时间类型表达式、text、datetime、date或time等类型。
- unit表示的是两个参数差的单位,按照指定形式对结果进行返回。
返回值类型:bigint

- 该函数仅在MYSQL模式数据库中有效。
- timestampdiff在sql_compatibility = 'MYSQL',且参数b_format_version值为5.7、b_format_dev_version值为s1时,调用的函数实际上注册为b_timestampdiff;在MYSQL模式数据库中且未开启GUC参数时,调用的函数注册为timestamp_diff,可以用“\df b_timestampdiff”等指令查询函数的详细入参与返回值信息。
参数unit的取值范围如下所示。
- 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)
父主题: 时间和日期处理函数和操作符