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

Date and Time Operators

When the user uses date/time operators, explicit type prefixes are modified for corresponding operands to ensure that the operands parsed by the database are consistent with what the user expects, and no unexpected results occur.

For example, abnormal mistakes will occur in the following example without an explicit data type.

1
2
 
SELECT date '2001-10-01' - '7' AS RESULT;
ERROR:  invalid input syntax for type timestamp: "7"
Table 1 Time and date operators

Operator

Example

+

Add a date with an integer to obtain the date 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 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 a date with an interval to obtain the time after one 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. For example, if the date of the month after 2021-01-31 is 2021-02-31, but February is a leap month and has only 28 days, the date function will accordingly return the last day of February, that is, 2021-02-28.

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 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 with 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 with 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 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, the return is a timestamp type.

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 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 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 at 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)