GBase 8c 数据类型——TIMESTAMPDIFF

l TIMESTAMPDIFF(unit , timestamp_expr1, timestamp_expr2)
 

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

仅在兼容MY类型时（即创建数据库时指定dbcompatibility 'B'）有效，其他类型不支持该函数。

l year ：年份

gbase=# SELECT TIMESTAMPDIFF(YEAR, '2018-01-01', '2020-01-01');

timestamp_diff

----------------

2

(1 row)

l quarter ：季度

gbase=# SELECT TIMESTAMPDIFF(QUARTER, '2018-01-01', '2020-01-01');

timestamp_diff

----------------

8

(1 row)

l month：月份

gbase=# SELECT TIMESTAMPDIFF(MONTH, '2018-01-01', '2020-01-01');

timestamp_diff

----------------

24

(1 row)

l week：星期

gbase=# SELECT TIMESTAMPDIFF(WEEK, '2018-01-01', '2020-01-01');

timestamp_diff

----------------

104

(1 row)

l day ：天

gbase=# SELECT TIMESTAMPDIFF(DAY, '2018-01-01', '2020-01-01');

timestamp_diff

----------------

730

(1 row)

l hour ：小时

gbase=# SELECT TIMESTAMPDIFF(HOUR, '2020-01-01 10:10:10', '2020-01-01 11:11:11');

timestamp_diff

----------------

1

(1 row)

l minute ：分钟

gbase=# SELECT TIMESTAMPDIFF(MINUTE, '2020-01-01 10:10:10', '2020-01-01 11:11:11');

timestamp_diff

----------------

61

(1 row)

l second ：秒

gbase=# SELECT TIMESTAMPDIFF(SECOND, '2020-01-01 10:10:10', '2020-01-01 11:11:11');

timestamp_diff

----------------

3661

(1 row)

l microseconds ：秒域（百万级别），可包含小数。

gbase=# SELECT TIMESTAMPDIFF(MICROSECOND, '2020-01-01 10:10:10.000000', '2020-01-01 10:10:10.111111');

timestamp_diff

----------------

111111

(1 row)

l timestamp_expr含有时区

gbase=# SELECT TIMESTAMPDIFF(HOUR,'2020-05-01 10:10:10-01','2020-05-01 10:10:10-03');

timestamp_diff

----------------

2

(1 row)