Operators

Subtopics

Date and time types support the addition or subtraction of various durations. Note that this operation produces a result of the same type as the operand before the operator:

Input

Run

select <datetime>'2020-03-15 12:34:56+00' +
       <cal::relative_duration>'2h 15m';

select <datetime>'2020-03-15 12:34:56+00' +
       <cal::relative_duration>'2h 15m';

Output Objects

Press the 'Run' button to evaluate the input

Input

Run

select <cal::local_datetime>'2020-03-15 12:34:56' +
       <duration>'2h 15m';

select <cal::local_datetime>'2020-03-15 12:34:56' +
       <duration>'2h 15m';

Output Objects

Press the 'Run' button to evaluate the input

Input

Run

select <cal::local_time>'12:34:56' + <duration>'2h 15m';

select <cal::local_time>'12:34:56' + <duration>'2h 15m';

Output Objects

Press the 'Run' button to evaluate the input

Input

Run

select <cal::local_date>'2020-03-15' - <cal::date_duration>'2 days';

select <cal::local_date>'2020-03-15' - <cal::date_duration>'2 days';

Output Objects

Press the 'Run' button to evaluate the input

Of course, durations can be added and subtracted from each other:

Input

Run

select <duration>'24h' - <duration>'3h 20m';

select <duration>'24h' - <duration>'3h 20m';

Output Objects

Press the 'Run' button to evaluate the input

Input

Run

select <cal::date_duration>'2 weeks' -
       <cal::date_duration>'3 days';

select <cal::date_duration>'2 weeks' -
       <cal::date_duration>'3 days';

Output Objects

Press the 'Run' button to evaluate the input

When different kinds of durations are mixed the result is a relative_duration:

Input

Run

select <duration>'24h' - <cal::relative_duration>'3h 20m';

select <duration>'24h' - <cal::relative_duration>'3h 20m';

Output Objects

Press the 'Run' button to evaluate the input

Input

Run

select <cal::date_duration>'2 weeks' -
       <cal::relative_duration>'3 days';

select <cal::date_duration>'2 weeks' -
       <cal::relative_duration>'3 days';

Output Objects

Press the 'Run' button to evaluate the input

Finally, it's possible to subtract one datetime value from another to get a duration:

Input

Run

select <datetime>'2020-03-15 12:34:56+00' -
       <datetime>'2020-03-15 11:45:16+00';

select <datetime>'2020-03-15 12:34:56+00' -
       <datetime>'2020-03-15 11:45:16+00';

Output Objects

Press the 'Run' button to evaluate the input

When calculating the difference between two local_datetime or local_time values the result is a relative_duration:

Input

Run

select <cal::local_datetime>'2020-03-15 12:34:56' -
       <cal::local_datetime>'2020-03-15 11:45:16';

select <cal::local_datetime>'2020-03-15 12:34:56' -
       <cal::local_datetime>'2020-03-15 11:45:16';

Output Objects

Press the 'Run' button to evaluate the input

When subtracting one local_date from another the result is a date_duration:

Input

Run

select <cal::local_date>'2022-10-25' -
       <cal::local_date>'2020-03-15';

select <cal::local_date>'2022-10-25' -
       <cal::local_date>'2020-03-15';

Output Objects

Press the 'Run' button to evaluate the input

Prev Next