openGauss TIMESTAMPDIFF

TIMESTAMPDIFF

• TIMESTAMPDIFF(unit , timestamp_expr1, timestamp_expr2)

timestampdiff函数是计算两个日期时间之间(timestamp_expr2-timestamp_expr1)的差值，并以unit形式返回结果。timestamp_expr1，timestamp_expr2必须是一个timestamp、timestamptz、date类型的值表达式。unit表示的是两个日期差的单位。

 说明：
该函数仅在openGauss兼容MY类型时（即dbcompatibility = 'B'）有效，其他类型不支持该函数。 该函数仅在GUC参数dolphin.b_compatibility_mode为true/on时，才可正确兼容MySQL。

• year

  年份。

  openGauss=#  SELECT TIMESTAMPDIFF(YEAR, '2018-01-01', '2020-01-01');
   timestamp_diff
  ----------------
                2
  (1 row)
  

• quarter

  季度。

  openGauss=#  SELECT TIMESTAMPDIFF(QUARTER, '2018-01-01', '2020-01-01');
   timestamp_diff
  ----------------
                8
  (1 row)
  

• month

  月份。

  openGauss=#  SELECT TIMESTAMPDIFF(MONTH, '2018-01-01', '2020-01-01');
   timestamp_diff
  ----------------
               24
  (1 row)
  

• week

  星期。

  openGauss=#  SELECT TIMESTAMPDIFF(WEEK, '2018-01-01', '2020-01-01');
   timestamp_diff
  ----------------
              104
  (1 row)
  

• day

  天。

  openGauss=#  SELECT TIMESTAMPDIFF(DAY, '2018-01-01', '2020-01-01');
   timestamp_diff
  ----------------
              730
  (1 row)
  

• hour

  小时。

  openGauss=#  SELECT TIMESTAMPDIFF(HOUR, '2020-01-01 10:10:10', '2020-01-01 11:11:11');
   timestamp_diff
  ----------------
                1
  (1 row)
  

• minute

  分钟。

  openGauss=#  SELECT TIMESTAMPDIFF(MINUTE, '2020-01-01 10:10:10', '2020-01-01 11:11:11');
   timestamp_diff
  ----------------
               61
  (1 row)
  

• second

  秒。

  openGauss=#  SELECT TIMESTAMPDIFF(SECOND, '2020-01-01 10:10:10', '2020-01-01 11:11:11');
   timestamp_diff
  ----------------
             3661
  (1 row)
  

• microseconds

  秒域（包括小数部分）乘以1,000,000。

  openGauss=#  SELECT TIMESTAMPDIFF(MICROSECOND, '2020-01-01 10:10:10.000000', '2020-01-01 10:10:10.111111');
   timestamp_diff
  ----------------
           111111
  (1 row)
  

• timestamp_expr含有时区

  openGauss=# SELECT TIMESTAMPDIFF(HOUR,'2020-05-01 10:10:10-01','2020-05-01 10:10:10-03');
   timestamp_diff
  ----------------
                2
  (1 row)