Help Center/ DataArts Fabric/ Developer Guide/ SQL Syntax Reference/ Functions and Operators/ Time and Date Processing Functions and Operators/ Time and Date Operators
Updated on 2025-09-18 GMT+08:00
View PDF
Share

Time and Date Operators

When using time and date operators, you should explicitly prefix the corresponding operands with type modifiers to ensure that the database parses the operands as intended, avoiding unexpected results.

For example, failing to specify the data type in the following query will cause an exception.

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

Operator

Example

+

Add a date type parameter and an integer parameter to get the time after 7 days.

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

Add a date type parameter and an interval parameter to get 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 type parameter and an interval parameter to get the time span of 1 month.

The date function adjusts the date range beyond the month to align with the last day of the respective month. It leaves the date range unchanged if it does not exceed 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 type parameter and a time type parameter to get a specific date and time result.

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 interval parameters to get the sum of two time spans.

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 type parameter and an interval parameter to get 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 type parameter and an interval parameter to get the time after 3 hours.

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

-

Subtract date type parameters to get the time difference between two dates.

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

Subtract an integer parameter from a date type parameter and return a timestamp type to get the time difference between them.

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

Subtract an interval parameter from a date type parameter to get 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 time type parameters to get the time difference between them.

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

Subtract an interval parameter from a time type parameter to get the time difference between them.

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 get the date and time difference between them.

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 interval parameters to get the time difference between them.

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

Subtract timestamp type parameters to get the date and time difference between them.

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)

Get the previous day from the current date.

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

*

Multiply a time span 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 a time span by a quantity to get a segment within a period of time.

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