Implement INTERVAL in basic date/time operations and functions

Final Release Note

Description

This issue tracks the implementation of following operations with INTERVAL operands

  • +
  • -
  • DATE_ADD
  • DATE_SUB
  • EXTRACT

The interval operand will be represented by literals like the ones shown below

interval'1 day 1 hour'
interval'1 1:1:1' DAY_SECOND

DATE_ADD() (#838), e.g.:

SELECT DATE_ADD(DATE'2018-05-01',INTERVAL '1' DAY);  -- MySQL syntax

EXTRACT(), e.g.:

SELECT EXTRACT(hour from interval'80 minutes');  -- PostgreSQL syntax

A real world example of interval usage looks like:

OCTO> select * from orders;
order_id|order_date|order_amount|customer_id
1|1776-07-04|$234.56|1
2|1760-03-14|$78.50|3
3|1784-05-23|$124.00|2
4|1790-09-03|$65.50|3
5|1795-07-21|$25.50|10
6|1787-11-27|$14.40|9
(6 rows)

OCTO> select order_date from orders where order_date between date '1700-01-01' - interval '9 day' and date '1770-01-04';
order_date
1760-03-14
(1 row)

Implementing DATE_ADD() and resolving #838 will require support for INTERVAL features explained above, while resolving #45 (closed) and supporting EXTRACT() (discussed there) will as well. There may also be implications for #382 (closed).

Accordingly, INTERVAL should be implemented as a stepping stone to robust support for date/time functionality in Octo.

Note that this issue can be resolved independently of #45 (closed) and #382 (closed), and hence has been defined separately.

Draft Release Note

Octo now supports SQL INTERVAL syntax, which can be used for modifying dates and times.

Edited by Ganesh Mahesh