# 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

Post Comment