文档首页/ 云数据库 GaussDB/ 开发指南（分布式_V2.0-8.x）/ SQL参考/ 函数和操作符/ 时间和日期处理函数和操作符/ 时间日期操作符
更新时间：2025-05-29 GMT+08:00
查看PDF
分享

时间日期操作符

时间日期操作符如表1 时间和日期操作符所示。

用户在使用时间和日期操作符时，应使用明确的类型前缀修饰对应的操作数，以确保数据库在解析操作数的时候能够与用户预期一致，避免产生非预期的结果。

举例：以下示例没有明确数据类型，就会出现异常错误。

1
2
3
4
5
6
7
 
gaussdb=# SELECT date '2001-10-01' - '7' AS RESULT;
ERROR:  
GAUSS-10416: invalid input syntax for type timestamp: "7"
SQLSTATE: 22007
LINE 1: SELECT date '2001-10-01' - '7' AS RESULT;
                                   ^
CONTEXT:  referenced column: result
表1 时间和日期操作符

操作符

示例

+

 
1
2
3
4
5
 
gaussdb=# SELECT date '2001-9-28' + integer '7' AS RESULT;
   result
------------
 2001-10-05
(1 row)
说明：

在ORA兼容模式下，查询结果为2001-10-05 00:00:00。

 
1
2
3
4
5
 
gaussdb=# SELECT date '2001-09-28' + interval '1 hour' AS RESULT;
       result        
---------------------
 2001-09-28 01:00:00
(1 row)

1
2
3
4
5
 
gaussdb=# SELECT date '2001-09-28' + time '03:00' AS RESULT;
       result        
---------------------
 2001-09-28 03:00:00
(1 row)

1
2
3
4
5
 
gaussdb=# SELECT interval '1 day' + interval '1 hour' AS RESULT;
     result     
----------------
 1 day 01:00:00
(1 row)

1
2
3
4
5
 
gaussdb=# SELECT timestamp '2001-09-28 01:00' + interval '23 hours' AS RESULT;
       result        
---------------------
 2001-09-29 00:00:00
(1 row)

1
2
3
4
5
 
gaussdb=# SELECT time '01:00' + interval '3 hours' AS RESULT;
  result  
----------
 04:00:00
(1 row)

-

 
1
2
3
4
5
 
gaussdb=# SELECT date '2001-10-01' - date '2001-09-28' AS RESULT;
 result
--------
      3
(1 row)

1
2
3
4
5
 
gaussdb=# SELECT date '2001-10-01' - integer '7' AS RESULT;
       result        
---------------------
 2001-09-24 00:00:00
(1 row)

1
2
3
4
5
 
gaussdb=# SELECT date '2001-09-28' - interval '1 hour' AS RESULT;
       result        
---------------------
 2001-09-27 23:00:00
(1 row)

1
2
3
4
5
 
gaussdb=# SELECT time '05:00' - time '03:00' AS RESULT;
  result  
----------
 02:00:00
(1 row)

1
2
3
4
5
 
gaussdb=# SELECT time '05:00' - interval '2 hours' AS RESULT;
  result  
----------
 03:00:00
(1 row)

1
2
3
4
5
 
gaussdb=# SELECT timestamp '2001-09-28 23:00' - interval '23 hours' AS RESULT;
       result        
---------------------
 2001-09-28 00:00:00
(1 row)

1
2
3
4
5
 
gaussdb=# SELECT interval '1 day' - interval '1 hour' AS RESULT;
  result  
----------
 23:00:00
(1 row)

1
2
3
4
5
 
gaussdb=# SELECT timestamp '2001-09-29 03:00' - timestamp '2001-09-27 12:00' AS RESULT;
     result     
----------------
 1 day 15:00:00
(1 row)

*

 
1
2
3
4
5
 
gaussdb=# SELECT 900 * interval '1 second' AS RESULT;
  result  
----------
 00:15:00
(1 row)

1
2
3
4
5
 
gaussdb=# SELECT 21 * interval '1 day' AS RESULT;
 result  
---------
 21 days
(1 row)

1
2
3
4
5
 
gaussdb=# SELECT double precision '3.5' * interval '1 hour' AS RESULT;
  result  
----------
 03:30:00
(1 row)

/

 
1
2
3
4
5
 
gaussdb=# SELECT interval '1 hour' / double precision '1.5' AS RESULT;
  result  
----------
 00:40:00
(1 row)