Teradata INTERVAL Function

In Teradata, we can use INTERVAL function to perform several DATE – TIME operations. Consider INTERVAL as the time-period or time-quanta between two timestamps like DAY to SECOND or YEAR to HOUR etc.

Let's see below few examples using Teradata Interval Function.

CREATE VOLATILE TABLE VT_DATE_TIME
(
TIMESTAMP1 TIMESTAMP(0) FORMAT 'Y4-MM-DDBHH:MI:SS'
)
PRIMARY INDEX(TIMESTAMP1)
ON COMMIT PRESERVE ROWS;

INSERT INTO VT_DATE_TIME
SELECT CURRENT_TIMESTAMP(0) (FORMAT 'Y4-MM-DDBHH:MI:SS');

SELECT TIMESTAMP1 FROM VT_DATE_TIME; --2020-10-16 23:41:03

SELECT TIMESTAMP1 - INTERVAL '02' YEAR FROM VT_DATE_TIME; -- 2018-10-16 23:41:03

SELECT TIMESTAMP1 - INTERVAL '02' MONTH FROM VT_DATE_TIME; -- 2020-08-16 23:41:03

SELECT TIMESTAMP1 - INTERVAL '02-02' YEAR TO MONTH FROM VT_DATE_TIME; -- 2018-08-16 23:41:03

SELECT TIMESTAMP1 - INTERVAL '02' DAY FROM VT_DATE_TIME; -- 2020-10-14 23:41:03

SELECT TIMESTAMP1 - INTERVAL '02' HOUR FROM VT_DATE_TIME; -- 2020-10-16 21:41:03

SELECT TIMESTAMP1 - INTERVAL '02' MINUTE FROM VT_DATE_TIME; -- 2020-10-16 23:39:03

SELECT TIMESTAMP1 - INTERVAL '02' SECOND FROM VT_DATE_TIME; -- 2020-10-16 23:41:01

SELECT TIMESTAMP1 - INTERVAL '02 02' DAY TO HOUR FROM VT_DATE_TIME; -- 2020-10-14 21:41:03

SELECT TIMESTAMP1 - INTERVAL '02 02:02' DAY TO MINUTE FROM VT_DATE_TIME; -- 2020-10-14 21:39:03

SELECT TIMESTAMP1 - INTERVAL '02 02:02:02' DAY TO SECOND FROM VT_DATE_TIME; -- 2020-10-14 21:39:01

SELECT TIMESTAMP1 - INTERVAL '02:02' HOUR TO MINUTE FROM VT_DATE_TIME; -- 2020-10-16 21:39:03

SELECT TIMESTAMP1 - INTERVAL '02:02:02' HOUR TO SECOND FROM VT_DATE_TIME; -- 2020-10-16 21:39:01

SELECT TIMESTAMP1 - INTERVAL '02:02' MINUTE TO SECOND FROM VT_DATE_TIME; -- 2020-10-16 23:39:01

SELECT (CURRENT_TIMESTAMP(0) - TIMESTAMP1) HOUR TO SECOND(0) FROM VT_DATE_TIME; --   0:04:57

In above examples, we saw different ways in which we can subtract various interval types from timestamp. Also when we subtract 2 timestamp columns, the output is INTERVAL.

Leave a Comment

Your email address will not be published. Required fields are marked *