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.