Help Center/ Data Warehouse Service / SQL Syntax Reference (9.1.0.x)/ Functions and Operators/ Date and Time Processing Functions and Operators/ Date and Time Operators
Updated on 2025-09-18 GMT+08:00
View PDF

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

Result:

+

date + integer → timestamp

Add days to a date.

1
 
SELECT date '2001-09-28' + integer '7' AS RESULT;

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

1
 
 2001-10-05 00:00:00

+

date + interval → timestamp

Add an interval to a date.

1
 
SELECT date '2001-09-28' + interval '1 hour' AS RESULT;

1
 
SELECT date '2021-02-28' + interval '1 month' AS RESULT;

1
 
SELECT date '2021-01-31' + interval '1 month' AS RESULT;

Sum of the date parameter and the interval parameter

Obtain the time after one hour.

1
 
 2001-09-28 01:00:00

Obtain the time after one month.

1
 
 2021-02-28 00:00:00

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
 
 2021-03-28 00:00:00

+

date + time → timestamp

Add the time of a day to a date.

1
 
SELECT date '2001-09-28' + time '03:00' AS RESULT;

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

1
 
 2001-09-28 03:00:00

+

interval + interval → interval

Add an interval.

1
 
SELECT interval '1 day' + interval '1 hour' AS RESULT;

Add two intervals to obtain the sum.

1
 
 1 day 01:00:00

+

timestamp + interval → timestamp

Add an interval to a timestamp.

1
 
SELECT timestamp '2001-09-28 01:00' + interval '23 hours' AS RESULT;

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

1
 
 2001-09-29 00:00:00

+

time + interval → time

Add an interval to a time.

1
 
SELECT time '01:00' + interval '3 hours' AS RESULT;

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

1
 
 04:00:00

-

date - date → integer

Subtract a date from another to obtain the time difference.

1
 
SELECT date '2001-10-01' - date '2001-09-28' AS RESULT;

Subtract a date from another to obtain the time difference.

1
 
 3 days

-

date - integer → timestamp

Subtract days from a date.

1
 
SELECT date '2001-10-01' - integer '7' AS RESULT;

Subtract an integer from a date to obtain the time difference between them.

1
 
 2001-09-24 00:00:00

-

date - interval → timestamp

Subtract an interval from a date.

1
 
SELECT date '2001-09-28' - interval '1 hour' AS RESULT;

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

1
 
 2001-09-27 23:00:00

-

time - time → interval

Subtract a time.

1
 
SELECT time '05:00' - time '03:00' AS RESULT;

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

1
 
 02:00:00

-

time - interval → time

Subtract an interval from a time.

1
 
SELECT time '05:00' - interval '2 hours' AS RESULT;

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

1
 
 03:00:00

-

timestamp - interval → timestamp

Subtract an interval from a timestamp.

1
 
SELECT timestamp '2001-09-28 23:00' - interval '23 hours' AS RESULT;

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

1
 
 2001-09-28 00:00:00

-

interval - interval → interval

Subtract an interval.

1
 
SELECT interval '1 day' - interval '1 hour' AS RESULT;

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

1
 
 23:00:00

-

timestamp - timestamp → interval

Subtract a timestamp.

1
 
SELECT timestamp '2001-09-29 03:00' - timestamp '2001-09-27 12:00' AS RESULT;

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

1
 
 1 day 15:00:00

-

select now() - interval → timestamp with time zone

Obtain the time on the previous day.

1
 
select now() - interval '1 day' AS RESULT;

Obtain the time on the previous day.

1
 
 2025-07-29 10:54:29.116501+08

*

interval * double precision → interval

Multiply an interval by a quantity.

1
 
SELECT 900 * interval '1 second' AS RESULT;

1
 
SELECT 21 * interval '1 day' AS RESULT;

1
 
SELECT double precision '3.5' * interval '1 hour' AS RESULT;

Multiply the interval of 1 second by 900.

1
 
 00:15:00
Multiply the interval of one day by 21. 
1
 
 21 days

Multiply the interval of one hour by 3.5.

1
 
 03:30:00

/

interval / double precision → interval

1
 
SELECT interval '1 hour' / double precision '1.5' AS RESULT;

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

1
 
 00:40:00
Parent Topic: Date and Time Processing Functions and Operators