Help Center/ GaussDB(DWS)/ SQL Syntax Reference/ SQL Syntax Reference (9.1.0.x)/ Functions and Operators/ Date and Time Processing Functions and Operators/ Date and Time Operators
Updated on 2024-09-02 GMT+08:00
View PDF
Share

Date and Time Operators

When you use date and time operators, add prefixes of explicit types to the operands to ensure that the operands can be properly parsed by the database.

For example, the operand in the following example does not have an explicit data type, and an error will occur.

1
 
SELECT date '2001-10-01' - '7' AS RESULT;
Table 1 Date and time operators

Operator

Example

+

Add a date with an integer to obtain the time after 7 days.

1
2
3
4
5
 
SELECT date '2001-09-28' + integer '7' AS RESULT;
       result        
---------------------
 2001-10-05 00:00:00
(1 row)

Add a date with an interval to obtain the time after 1 hour.

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

Add a date with an interval to obtain the time after 1 month.

If the sum or subtraction results fall beyond the date range of a month, the result will be rounded to the last day of the month.

1
2
3
4
5
 
SELECT date '2021-01-31' + interval '1 month' AS RESULT;
       result
---------------------
 2021-02-28 00:00:00
(1 row)

1
2
3
4
5
 
SELECT date '2021-02-28' + interval '1 month' AS RESULT;
       result
---------------------
 2021-03-28 00:00:00
(1 row)

Add a date and a time to obtain a specific time.

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

Add two intervals to obtain the sum.

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

Add a timestamp and an interval to obtain the time after 23 hours.

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

Add a time and an interval to obtain the time after three hours.

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

-

Subtract a date from another to obtain the time difference.

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

Subtract an integer from a date to obtain the difference.

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

Subtract an interval from a date to obtain the date and time difference.

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

Subtract a time from another time to obtain the time difference.

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

Subtract an interval from a time to obtain the time difference.

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

Subtract an interval from a timestamp to obtain the date and time difference.

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

Subtract an interval from another interval to obtain the time difference.

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

Subtract a timestamp from another timestamp to obtain the time difference.

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

Obtain the time on the previous day.

1
2
3
4
5
 
select now() - interval '1 day'AS RESULT;
           result
-------------------------------
 2022-08-08 01:46:15.555406+00
(1 row)

*

Multiply an interval by a quantity.

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

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

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

/

Divide an interval by a quantity to obtain a time segment.

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