Teradata INTERVAL Function

In Teradata, we can use INTERVAL function to perform several DATE – TIME operations. Let’s see below few examples using Teradata Interval Function.

Teradata INTERVAL Function
Teradata INTERVAL Function

CREATE MULTISET 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; — 2014-01-07 04:21:17

SELECT TIMESTAMP1 - INTERVAL '02' YEAR FROM VT_DATE_TIME; — 2012-01-07 04:21:17

SELECT TIMESTAMP1 - INTERVAL '02' MONTH FROM VT_DATE_TIME; — 2013-11-07 04:21:17

SELECT TIMESTAMP1 - INTERVAL '02-02' YEAR TO MONTH FROM VT_DATE_TIME; –2011-11-07 04:21:17

SELECT TIMESTAMP1 - INTERVAL '02' DAY FROM VT_DATE_TIME; — 2014-01-05 04:21:17

SELECT TIMESTAMP1 - INTERVAL '02' HOUR FROM VT_DATE_TIME; — 2014-01-07 02:21:17

SELECT TIMESTAMP1 - INTERVAL '02' MINUTE FROM VT_DATE_TIME; — 2014-01-07 04:19:17

SELECT TIMESTAMP1 - INTERVAL '02' SECOND FROM VT_DATE_TIME; — 2014-01-07 04:21:15

SELECT TIMESTAMP1 - INTERVAL '02 02' DAY TO HOUR FROM VT_DATE_TIME; — 2014-01-05 02:21:17

SELECT TIMESTAMP1 - INTERVAL '02 02:02' DAY TO MINUTE FROM VT_DATE_TIME; — 2014-01-05 02:19:17

SELECT TIMESTAMP1 - INTERVAL '02 02:02:02' DAY TO SECOND FROM VT_DATE_TIME; — 2014-01-05 02:19:15

SELECT TIMESTAMP1 - INTERVAL '02:02' HOUR TO MINUTE FROM VT_DATE_TIME; — 2014-01-07 02:19:17

SELECT TIMESTAMP1 - INTERVAL '02:02:02' HOUR TO SECOND FROM VT_DATE_TIME; — 2014-01-07 02:19:15

SELECT TIMESTAMP1 - INTERVAL '02:02' MINUTE TO SECOND FROM VT_DATE_TIME; — 2014-01-07 04:19:15

Subtract two timestamp columns:
SELECT (CURRENT_TIMESTAMP(0) - TIMESTAMP1) HOUR TO SECOND(0) FROM VT_DATE_TIME; — 0:12:52

Leave a Reply

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