Cloud

Time Operators

Time operators in Redpanda SQL perform various operations on dates, times, and intervals.

DATE + INTEGER

Add a specific number of days to a date.

Example:

select date '2022-03-15' + 14 as "result";

The result is the date 14 days after ‘2022-03-15’:

   result
------------
 2022-03-29

INTEGER + DATE

Adding and multiplying time operators can also be done in reverse order. For example, you can add a number of days to a date in the format of Integer + Date.

select 14 + date '2022-03-15' AS "result";

This produces the same result: 14 days after ‘2022-03-15’ is ‘2022-03-29’:

   result
------------
 2022-03-29

DATE + INTERVAL

Add a specified interval to a date.

Example:

select date '2022-03-15' + interval '3 months' as "result";

The result is the date three months after ‘2022-03-15’:

           result
----------------------------
 2022-06-15 00:00:00.000000

DATE - INTEGER

Subtract a certain number of days from a date.

Example:

select date '2022-03-15' - 7 as "result";

The result is the date 7 days before ‘2022-03-15’:

   result
------------
 2022-03-08

DATE - INTERVAL

Subtract a specified interval from a date.

Example:

select date '2022-03-15' - interval '2 hour' as "result";

The result is the timestamp two hours before ‘2022-03-15’:

           result
----------------------------
 2022-03-14 22:00:00.000000

DATE - DATE

Subtract dates.

Example:

select date '2023-03-15' - date '2023-01-10' as "result";

The number of days elapsed between ‘2023-03-15’ and ‘2023-01-10’ is 64 days.

 result
--------
     64

DATE + TIME

Add a time-of-day to a date.

Example:

select date '2010-05-20' + time '02:00' as "result";

The result is a timestamp combining the date and time:

           result
----------------------------
 2010-05-20 02:00:00.000000

TIME + INTERVAL

Add a certain interval to a given time.

Example:

select time '12:30' + interval '1 hour' as "result";

The result is the time one hour after ‘12:30’:

     result
-----------------
 13:30:00.000000

TIME - INTERVAL

Subtract a specified interval from a given time.

Example:

select time '18:45' - interval '45 minutes' as "result";

The result is the time 18:00:

     result
-----------------
 18:00:00.000000

TIME - TIME

Get a time difference by subtracting one time from another.

Example:

select time '10:00' - TIME '08:20' as "result";

In this example, the time difference between the two provided times is 1 hour and 40 minutes.

     result
-----------------
 01:40:00.000000

TIMESTAMP + INTERVAL

Add a timestamp and an interval.

Example:

select timestamp '2021-01-05 12:00:00' + interval '5 days' as "result";

The result is a new timestamp, 5 days after ‘2021-01-05 12:00:00’:

           result
----------------------------
 2021-01-10 12:00:00.000000

TIMESTAMP - INTERVAL

Subtract an interval from a timestamp.

Example:

select timestamp '2022-01-04 12:00:00' - interval '3 days' as "result";

In this example, it subtracts 3 days from ‘2022-01-04 12:00:00’.

           result
----------------------------
 2022-01-01 12:00:00.000000

TIMESTAMP - TIMESTAMP

Get an interval by subtracting one timestamp from another.

Example:

select timestamp '2022-01-05 18:30:00' - timestamp '2022-01-01 12:00:00' as "result";

It gives the interval between the two timestamps, 102 hours and 30 minutes.

      result
------------------
 102:30:00.000000

INTERVAL + INTERVAL

Add intervals.

Example:

select interval '2 months 2 days' + interval '6 days' as "result";

It adds 6 days to 2 days, resulting in a total of 2 months and 8 days.

    result
---------------
 2 mons 8 days

INTERVAL - INTERVAL

Subtract intervals.

Example:

select interval '2 months' - interval '20 days' as "result";

It subtracts 20 days from 2 months.

     result
-----------------
 2 mons -20 days

INTERVAL * INTEGER

Multiply an interval by an integer.

Example:

select interval '2 hours' * 3 as "result";

It multiplies ‘2 hours’ by 3, the result is 6 hours.

     result
-----------------
 06:00:00.000000

INTERVAL * DOUBLE PRECISION

Multiply an interval by a scalar.

Example:

select interval '2 hours' * 1.5 as "result";

It multiplies ‘2 hours’ by 1.5, and returns 3 hours.

    result
-----------------
 03:00:00.000000

INTERVAL / NUMBER

Divide an interval by an integer or scalar.

Divide by an integer

select interval '1 hour' / 2 as "result";

It divides ‘1 hour’ by 2, and returns 30 minutes.

    result
-----------------
 00:30:00.000000

Divide by a scalar

select interval '2 hours' / 1.5 as "result";

It divides ‘2 hours’ by 1.5, and returns 1 hour 20 minutes.

     result
-----------------
 01:20:00.000000