27 Jun 22 ·  Cpaul in Guides ·        Bookmark ·  Report ·  More actions.. Lock comments ·  Pin thread

INTERVAL datatype & constants - Query conversion guide

INTERVAL datatype:

The interval datatype stores a "time interval" in a table column

CREATE TABLE candidates (
candidate_id NUMBER,
first_name VARCHAR2(50) NOT NULL,
last_name VARCHAR2(50) NOT NULL,
job_title VARCHAR2(255) NOT NULL,
year_of_experience INTERVAL YEAR TO MONTH
);

You can then insert data:

INSERT INTO candidates (first_name, last_name, job_title, year_of_experience)
VALUES ('Camila', 'Kramer', 'SCM Manager', INTERVAL '10-2' YEAR TO MONTH);
INSERT INTO candidates (first_name, last_name, job_title, year_of_experience)
VALUES ('Keila', 'Doyle'', 'SCM Staff', INTERVAL '9' MONTH);

Teradata, for instance, supports the "INTERVAL" datatype in below flavors:

+---------------------------------+-------------------------+------------------------+
| Datatype | Minimum value | Maximum value |
+---------------------------------+-------------------------+------------------------+
| INTERVAL YEAR(1) | -'9' | '9' |
| INTERVAL YEAR(2) | -'99' | '99' |
| INTERVAL YEAR(3) | -'999' | '999' |
| INTERVAL YEAR(4) | -'9999' | '9999' |
| INTERVAL YEAR(1) TO MONTH | -'9-11' | '9-11' |
| INTERVAL YEAR(2) TO MONTH | -'99-11' | '99-11' |
| INTERVAL YEAR(3) TO MONTH | -'999-11' | '999-11' |
| INTERVAL YEAR(4) TO MONTH | -'9999-11' | '9999-11' |
| INTERVAL MONTH(1) | -'9' | '9' |
| INTERVAL MONTH(2) | -'99' | '99' |
| INTERVAL MONTH(3) | -'999' | '999' |
| INTERVAL MONTH(4) | -'9999' | '9999' |
| INTERVAL DAY(1) | -'9' | '9' |
| INTERVAL DAY(2) | -'99' | '99' |
| INTERVAL DAY(3) | -'999' | '999' |
| INTERVAL DAY(4) | -'9999' | '9999' |
| INTERVAL DAY(1) TO HOUR | -'9 23' | '9 23' |
| INTERVAL DAY(2) TO HOUR | -'99 23' | '99 23' |
| INTERVAL DAY(3) TO HOUR | -'999 23' | '999 23' |
| INTERVAL DAY(4) TO HOUR | -'9999 23' | '9999 23' |
| INTERVAL DAY(1) TO MINUTE | -'9 23:59' | '9 23:59' |
| INTERVAL DAY(2) TO MINUTE | -'99 23:59' | '99 23:59' |
| INTERVAL DAY(3) TO MINUTE | -'999 23:59' | '999 23:59' |
| INTERVAL DAY(4) TO MINUTE | -'9999 23:59' | '9999 23:59' |
| INTERVAL DAY(1) TO SECOND(0) | -'9 23:59:59' | '9 23:59:59' |
| INTERVAL DAY(1) TO SECOND(1) | -'9 23:59:59.9' | '9 23:59:59.9' |
| INTERVAL DAY(1) TO SECOND(2) | -'9 23:59:59.99' | '9 23:59:59.99' |
| INTERVAL DAY(1) TO SECOND(3) | -'9 23:59:59.999' | '9 23:59:59.999' |
| INTERVAL DAY(1) TO SECOND(4) | -'9 23:59:59.9999' | '9 23:59:59.9999' |
| INTERVAL DAY(1) TO SECOND(5) | -'9 23:59:59.99999' | '9 23:59:59.99999' |
| INTERVAL DAY(1) TO SECOND(6) | -'9 23:59:59.999999' | '9 23:59:59.999999' |
| INTERVAL DAY(2) TO SECOND(0) | -'99 23:59:59' | '99 23:59:59' |
| INTERVAL DAY(2) TO SECOND(1) | -'99 23:59:59.9' | '99 23:59:59.9' |
| INTERVAL DAY(2) TO SECOND(2) | -'99 23:59:59.99' | '99 23:59:59.99' |
| INTERVAL DAY(2) TO SECOND(3) | -'99 23:59:59.999' | '99 23:59:59.999' |
| INTERVAL DAY(2) TO SECOND(4) | -'99 23:59:59.9999' | '99 23:59:59.9999' |
| INTERVAL DAY(2) TO SECOND(5) | -'99 23:59:59.99999' | '99 23:59:59.99999' |
| INTERVAL DAY(2) TO SECOND(6) | -'99 23:59:59.999999' | '99 23:59:59.999999' |
| INTERVAL DAY(3) TO SECOND(0) | -'999 23:59:59' | '999 23:59:59' |
| INTERVAL DAY(3) TO SECOND(1) | -'999 23:59:59.9' | '999 23:59:59.9' |
| INTERVAL DAY(3) TO SECOND(2) | -'999 23:59:59.99' | '999 23:59:59.99' |
| INTERVAL DAY(3) TO SECOND(3) | -'999 23:59:59.999' | '999 23:59:59.999' |
| INTERVAL DAY(3) TO SECOND(4) | -'999 23:59:59.9999' | '999 23:59:59.9999' |
| INTERVAL DAY(3) TO SECOND(5) | -'999 23:59:59.99999' | '999 23:59:59.99999' |
| INTERVAL DAY(3) TO SECOND(6) | -'999 23:59:59.999999' | '999 23:59:59.999999' |
| INTERVAL DAY(4) TO SECOND(0) | -'9999 23:59:59' | '9999 23:59:59' |
| INTERVAL DAY(4) TO SECOND(1) | -'9999 23:59:59.9' | '9999 23:59:59.9' |
| INTERVAL DAY(4) TO SECOND(2) | -'9999 23:59:59.99' | '9999 23:59:59.99' |
| INTERVAL DAY(4) TO SECOND(3) | -'9999 23:59:59.999' | '9999 23:59:59.999' |
| INTERVAL DAY(4) TO SECOND(4) | -'9999 23:59:59.9999' | '9999 23:59:59.9999' |
| INTERVAL DAY(4) TO SECOND(5) | -'9999 23:59:59.99999' | '9999 23:59:59.99999' |
| INTERVAL DAY(4) TO SECOND(6) | -'9999 23:59:59.999999' | '9999 23:59:59.999999' |
| INTERVAL HOUR(1) | -'9' | '9' |
| INTERVAL HOUR(2) | -'99' | '99' |
| INTERVAL HOUR(3) | -'999' | '999' |
| INTERVAL HOUR(4) | -'9999' | '9999' |
| INTERVAL HOUR(1) TO MINUTE | -'9:59' | '9:59' |
| INTERVAL HOUR(2) TO MINUTE | -'99:59' | '99:59' |
| INTERVAL HOUR(3) TO MINUTE | -'999:59' | '999:59' |
| INTERVAL HOUR(4) TO MINUTE | -'9999:59' | '9999:59' |
| INTERVAL HOUR(1) TO SECOND(0) | -'9:59:59' | '9:59:59' |
| INTERVAL HOUR(1) TO SECOND(1) | -'9:59:59.9' | '9:59:59.9' |
| INTERVAL HOUR(1) TO SECOND(2) | -'9:59:59.99' | '9:59:59.99' |
| INTERVAL HOUR(1) TO SECOND(3) | -'9:59:59.999' | '9:59:59.999' |
| INTERVAL HOUR(1) TO SECOND(4) | -'9:59:59.9999' | '9:59:59.9999' |
| INTERVAL HOUR(1) TO SECOND(5) | -'9:59:59.99999' | '9:59:59.99999' |
| INTERVAL HOUR(1) TO SECOND(6) | -'9:59:59.999999' | '9:59:59.999999' |
| INTERVAL HOUR(2) TO SECOND(0) | -'99:59:59' | '99:59:59' |
| INTERVAL HOUR(2) TO SECOND(1) | -'99:59:59.9' | '99:59:59.9' |
| INTERVAL HOUR(2) TO SECOND(2) | -'99:59:59.99' | '99:59:59.99' |
| INTERVAL HOUR(2) TO SECOND(3) | -'99:59:59.999' | '99:59:59.999' |
| INTERVAL HOUR(2) TO SECOND(4) | -'99:59:59.9999' | '99:59:59.9999' |
| INTERVAL HOUR(2) TO SECOND(5) | -'99:59:59.99999' | '99:59:59.99999' |
| INTERVAL HOUR(2) TO SECOND(6) | -'99:59:59.999999' | '99:59:59.999999' |
| INTERVAL HOUR(3) TO SECOND(0) | -'999:59:59' | '999:59:59' |
| INTERVAL HOUR(3) TO SECOND(1) | -'999:59:59.9' | '999:59:59.9' |
| INTERVAL HOUR(3) TO SECOND(2) | -'999:59:59.99' | '999:59:59.99' |
| INTERVAL HOUR(3) TO SECOND(3) | -'999:59:59.999' | '999:59:59.999' |
| INTERVAL HOUR(3) TO SECOND(4) | -'999:59:59.9999' | '999:59:59.9999' |
| INTERVAL HOUR(3) TO SECOND(5) | -'999:59:59.99999' | '999:59:59.99999' |
| INTERVAL HOUR(3) TO SECOND(6) | -'999:59:59.999999' | '999:59:59.999999' |
| INTERVAL HOUR(4) TO SECOND(0) | -'9999:59:59' | '9999:59:59' |
| INTERVAL HOUR(4) TO SECOND(1) | -'9999:59:59.9' | '9999:59:59.9' |
| INTERVAL HOUR(4) TO SECOND(2) | -'9999:59:59.99' | '9999:59:59.99' |
| INTERVAL HOUR(4) TO SECOND(3) | -'9999:59:59.999' | '9999:59:59.999' |
| INTERVAL HOUR(4) TO SECOND(4) | -'9999:59:59.9999' | '9999:59:59.9999' |
| INTERVAL HOUR(4) TO SECOND(5) | -'9999:59:59.99999' | '9999:59:59.99999' |
| INTERVAL HOUR(4) TO SECOND(6) | -'9999:59:59.999999' | '9999:59:59.999999' |
| INTERVAL MINUTE(1) | -'9' | '9' |
| INTERVAL MINUTE(2) | -'99' | '99' |
| INTERVAL MINUTE(3) | -'999' | '999' |
| INTERVAL MINUTE(4) | -'9999' | '9999' |
| INTERVAL MINUTE(1) TO SECOND(0) | -'9:59' | '9:59' |
| INTERVAL MINUTE(1) TO SECOND(1) | -'9:59.9' | '9:59.9' |
| INTERVAL MINUTE(1) TO SECOND(2) | -'9:59.99' | '9:59.99' |
| INTERVAL MINUTE(1) TO SECOND(3) | -'9:59.999' | '9:59.999' |
| INTERVAL MINUTE(1) TO SECOND(4) | -'9:59.9999' | '9:59.9999' |
| INTERVAL MINUTE(1) TO SECOND(5) | -'9:59.99999' | '9:59.99999' |
| INTERVAL MINUTE(1) TO SECOND(6) | -'9:59.999999' | '9:59.999999' |
| INTERVAL MINUTE(2) TO SECOND(0) | -'99:59' | '99:59' |
| INTERVAL MINUTE(2) TO SECOND(1) | -'99:59.9' | '99:59.9' |
| INTERVAL MINUTE(2) TO SECOND(2) | -'99:59.99' | '99:59.99' |
| INTERVAL MINUTE(2) TO SECOND(3) | -'99:59.999' | '99:59.999' |
| INTERVAL MINUTE(2) TO SECOND(4) | -'99:59.9999' | '99:59.9999' |
| INTERVAL MINUTE(2) TO SECOND(5) | -'99:59.99999' | '99:59.99999' |
| INTERVAL MINUTE(2) TO SECOND(6) | -'99:59.999999' | '99:59.999999' |
| INTERVAL MINUTE(3) TO SECOND(0) | -'999:59' | '999:59' |
| INTERVAL MINUTE(3) TO SECOND(1) | -'999:59.9' | '999:59.9' |
| INTERVAL MINUTE(3) TO SECOND(2) | -'999:59.99' | '999:59.99' |
| INTERVAL MINUTE(3) TO SECOND(3) | -'999:59.999' | '999:59.999' |
| INTERVAL MINUTE(3) TO SECOND(4) | -'999:59.9999' | '999:59.9999' |
| INTERVAL MINUTE(3) TO SECOND(5) | -'999:59.99999' | '999:59.99999' |
| INTERVAL MINUTE(3) TO SECOND(6) | -'999:59.999999' | '999:59.999999' |
| INTERVAL MINUTE(4) TO SECOND(0) | -'9999:59' | '9999:59' |
| INTERVAL MINUTE(4) TO SECOND(1) | -'9999:59.9' | '9999:59.9' |
| INTERVAL MINUTE(4) TO SECOND(2) | -'9999:59.99' | '9999:59.99' |
| INTERVAL MINUTE(4) TO SECOND(3) | -'9999:59.999' | '9999:59.999' |
| INTERVAL MINUTE(4) TO SECOND(4) | -'9999:59.9999' | '9999:59.9999' |
| INTERVAL MINUTE(4) TO SECOND(5) | -'9999:59.99999' | '9999:59.99999' |
| INTERVAL MINUTE(4) TO SECOND(6) | -'9999:59.999999' | '9999:59.999999' |
| INTERVAL SECOND(1,0) | -'9' | '9' |
| INTERVAL SECOND(1,1) | -'9.9' | '9.9' |
| INTERVAL SECOND(1,2) | -'9.99' | '9.99' |
| INTERVAL SECOND(1,3) | -'9.999' | '9.999' |
| INTERVAL SECOND(1,4) | -'9.9999' | '9.9999' |
| INTERVAL SECOND(1,5) | -'9.99999' | '9.99999' |
| INTERVAL SECOND(1,6) | -'9.999999' | '9.999999' |
| INTERVAL SECOND(2,0) | -'99' | '99' |
| INTERVAL SECOND(2,1) | -'99.9' | '99.9' |
| INTERVAL SECOND(2,2) | -'99.99' | '99.99' |
| INTERVAL SECOND(2,3) | -'99.999' | '99.999' |
| INTERVAL SECOND(2,4) | -'99.9999' | '99.9999' |
| INTERVAL SECOND(2,5) | -'99.99999' | '99.99999' |
| INTERVAL SECOND(2,6) | -'99.999999' | '99.999999' |
| INTERVAL SECOND(3,0) | -'999' | '999' |
| INTERVAL SECOND(3,1) | -'999.9' | '999.9' |
| INTERVAL SECOND(3,2) | -'999.99' | '999.99' |
| INTERVAL SECOND(3,3) | -'999.999' | '999.999' |
| INTERVAL SECOND(3,4) | -'999.9999' | '999.9999' |
| INTERVAL SECOND(3,5) | -'999.99999' | '999.99999' |
| INTERVAL SECOND(3,6) | -'999.999999' | '999.999999' |
| INTERVAL SECOND(4,0) | -'9999' | '9999' |
| INTERVAL SECOND(4,1) | -'9999.9' | '9999.9' |
| INTERVAL SECOND(4,2) | -'9999.99' | '9999.99' |
| INTERVAL SECOND(4,3) | -'9999.999' | '9999.999' |
| INTERVAL SECOND(4,4) | -'9999.9999' | '9999.9999' |
| INTERVAL SECOND(4,5) | -'9999.99999' | '9999.99999' |
| INTERVAL SECOND(4,6) | -'9999.999999' | '9999.999999' |
+---------------------------------+-------------------------+------------------------+

Oracle supports the above datatypes too. But lot of other commercial databases including Snowflake, BigQuery doesn't support the "INTERVAL" datatype. You might want to convert it to integer  (and split it into multiple columns if required)

INTERVAL constant:

You can use interval constants to add or subtract a period of time to/from a date, time, or timestamp

There are two different conventions being used:

  • INTERVAL  '<period_expression>' interval_qualifier

Teradata, Oracle, BigQuery uses the above format. Examples:

INTERVAL '1' YEAR
INTERVAL '1' MONTH
INTERVAL '1' DAY
INTERVAL '1' HOUR
INTERVAL '1' MINUTE
INTERVAL '1' SECOND
INTERVAL '1-1' YEAR TO MONTH
INTERVAL '1-1 1' YEAR TO DAY
INTERVAL '1-1 1 01' YEAR TO HOUR
INTERVAL '100-0 1 01:10' YEAR TO MINUTE
INTERVAL '01:10' MINUTE TO SECOND
...

The second convention,

  • INTERVAL '1 year 2 months 3 days';

The expression could be "space" delimited (Postgres) or "comma" delimited (snowflake)

INTERVAL '1 year,2 months,3 days';

NOTE: Roboquery converts simple interval types (only one unit) eg. INTERVAL '2h'. Whereas composite interval type conversion is not supported. eg. INTERVAL '2h 2s'

Cpaul

posted on 27 Jun 22

Enjoy great content like this and a lot more !

Signup for a free account to write a post / comment / upvote posts. Its simple and takes less than 5 seconds