云数据库 GAUSSDB-时间间隔表达式

时间:2024-11-13 14:45:30

时间间隔表达式

语法:INTERVAL EXPR UNIT

说明:EXPR表示数值,UNIT说明符用于解释数值的单位,如HOUR、DAY、WEEK等。关键字INTERVAL和说明符不区分大小写。

时间间隔表达式中UNIT的取值范围如下表1所示,允许任何标点符号分隔EXPR格式。表1中显示的是建议的分隔符。

INTERVAL表达式仅在sql_compatibility = 'B',且参数b_format_version值为5.7、b_format_dev_version值为s1时,才支持上述功能。

表1 时间间隔表达式UNIT取值范围

UNIT 取值范围

预期 EXPR 格式

MICROSECOND

MICROSECONDS

SECOND

SECONDS

MINUTE

MINUTES

HOUR

HOURS

DAY

DAYS

WEEK

WEEKS

MONTH

MONTHS

QUARTER

QUARTERS

YEAR

YEARS

SECOND_MICROSECOND

'SECOND_MICROSECOND'

MINUTE_MICROSECOND

'MINUTES:SECONDS.MICROSECONDS'

MINUTE_SECOND

'MINUTES:SECONDS'

HOUR_MICROSECOND

'HOURS:MINUTES:SECONDS.MICROSECONDS'

HOUR_SECOND

'HOURS:MINUTES:SECONDS'

HOUR_MINUTE

'HOURS:MINUTES'

DAY_MICROSECOND

'DAYS HOURS:MINUTES:SECONDS.MICROSECONDS'

DAY_SECOND

'DAYS HOURS:MINUTES:SECONDS'

DAY_MINUTE

'DAYS HOURS:MINUTES'

DAY_HOUR

'DAYS HOURS'

YEAR_MONTH

'YEAR_MONTH'

示例:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
gaussdb=# SELECT DATE_ADD('2018-05-01', INTERVAL 1 DAY);
  date_add  
------------
 2018-05-02
(1 row)

gaussdb=# SELECT DATE_SUB('2018-05-01', INTERVAL 1 YEAR);
  date_sub  
------------
 2017-05-01
(1 row)

gaussdb=# SELECT DATE'2023-01-10' - INTERVAL 1 DAY;
      ?column?       
---------------------
 2023-01-09 00:00:00
(1 row)

gaussdb=# SELECT DATE'2023-01-10' + INTERVAL 1 MONTH;
      ?column?       
---------------------
 2023-02-10 00:00:00
(1 row)
support.huaweicloud.com/centralized-devg-v8-gaussdb/gaussdb-42-0424.html