Date / time operators - Amazon Timestream

Date / time operators

Note

Timestream does not support negative time values. Any operation resulting in negative time results in error.

Timestream supports the following operations on timestamps, dates, and intervals.

Operator Description

+

Addition

-

Subtraction

Operations

The result type of an operation is based on the operands. Interval literals such as 1day and 3s can be used.

SELECT date '2022-05-21' + interval '2' day
SELECT date '2022-05-21' + 2d
SELECT date '2022-05-21' + 2day

Example result for each: 2022-05-23

Interval units include second, minute, hour, day, week, month, and year. But in some cases not all are applicable. For example seconds, minutes, and hours can not be added to or subtracted from a date.

SELECT interval '4' year + interval '2' month

Example result: 4-2

SELECT typeof(interval '4' year + interval '2' month)

Example result: interval year to month

Result type of interval operations may be 'interval year to month' or 'interval day to second' depending on the operands. Intervals can be added to or subtracted from dates and timestamps. But a date or timestamp cannot be added to or subtracted from a date or timestamp. To find intervals or durations related to dates or timestamps, see date_diff and related functions in Interval and duration.

Addition

SELECT date '2022-05-21' + interval '2' day

Example result: 2022-05-23

SELECT typeof(date '2022-05-21' + interval '2' day)

Example result: date

SELECT interval '2' year + interval '4' month

Example result: 2-4

SELECT typeof(interval '2' year + interval '4' month)

Example result: interval year to month

Subtraction

SELECT timestamp '2022-06-17 01:00' - interval '7' hour

Example result: 2022-06-16 18:00:00.000000000

SELECT typeof(timestamp '2022-06-17 01:00' - interval '7' hour)

Example result: timestamp

SELECT interval '6' day - interval '4' hour

Example result: 5 20:00:00.000000000

SELECT typeof(interval '6' day - interval '4' hour)

Example result: interval day to second