Help Center/ GaussDB/ Developer Guide(Distributed_V2.0-8.x)/ SQL Reference/ Functions and Operators/ Date and Time Processing Functions and Operators/ Time and Date Operators
Updated on 2025-05-29 GMT+08:00
View PDF
Share

Time and Date Operators

Table 1 describes the time and date operators.

When using time and date operators, use explicit type prefixes to modify the corresponding operands to ensure that the operands parsed by the database are consistent with user expectations and avoid unexpected results.

Example: Not specifying a data type results in an error.

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
Table 1 Time and date operators

Operator

Example

+

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

In ORA-compatible mode, the query result is 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)