Help Center/ GaussDB/ Developer Guide(Centralized_V2.0-8.x)/ SQL Reference/ Functions and Operators/ Date and Time Processing Functions and Operators/ TIMESTAMPDIFF
Updated on 2025-08-19 GMT+08:00
View PDF

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 specifies the unit of the difference between two parameters. It returns the result in the specified format.

Return type: bigint

  • This function takes effect only in databases in B-compatible mode.
  • When sql_compatibility is set to B, 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 database in B-compatible mode, 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

    Year.

    1
2
3
4
5
    		 
    gaussdb=#  SELECT TIMESTAMPDIFF(YEAR, '2018-01-01', '2020-01-01');
 timestamp_diff
----------------
              2
(1 row)
  • quarter

    Quarter.

    1
2
3
4
5
    		 
    gaussdb=#  SELECT TIMESTAMPDIFF(QUARTER, '2018-01-01', '2020-01-01');
 timestamp_diff
----------------
              8
(1 row)
  • month

    Month.

    1
2
3
4
5
    		 
    gaussdb=#  SELECT TIMESTAMPDIFF(MONTH, '2018-01-01', '2020-01-01');
 timestamp_diff
----------------
             24
(1 row)
  • week

    Week.

    1
2
3
4
5
    		 
    gaussdb=#  SELECT TIMESTAMPDIFF(WEEK, '2018-01-01', '2020-01-01');
 timestamp_diff
----------------
            104
(1 row)
  • day
    Day. 
    1
2
3
4
5
    		 
    gaussdb=#  SELECT TIMESTAMPDIFF(DAY, '2018-01-01', '2020-01-01');
 timestamp_diff
----------------
            730
(1 row)
  • hour

    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

    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

    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)
  • microseconds

    The second field, including fractional parts, is multiplied by 1,000,000.

    1
2
3
4
5
    		 
    gaussdb=#  SELECT TIMESTAMPDIFF(MICROSECOND, '2020-01-01 10:10:10.000000', '2020-01-01 10:10:10.111111');
 timestamp_diff
----------------
         111111
(1 row)