Updated on 2025-05-29 GMT+08:00

TIMESTAMPDIFF

TIMESTAMPDIFF(unit, timestamp_expr1, timestamp_expr2)

Description: Returns the result of timestamp_expr2timestamp_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

    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)