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 22Enjoy 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