PROVE IT !!If you know it then PROVE IT !! Skill Proficiency Test

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.

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

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

  • SELECT TIMESTAMP1 FROM COLUMN_TIME_FIELD; — 2014-01-07 04:21:17
  • SELECT TIMESTAMP1 – INTERVAL '02' YEAR FROM COLUMN_TIME_FIELD; — 2012-01-07 04:21:17
  • SELECT TIMESTAMP1 – INTERVAL '02' MONTH FROM COLUMN_TIME_FIELD; — 2013-11-07 04:21:17
  • SELECT TIMESTAMP1 – INTERVAL '02-02' YEAR TO MONTH FROM COLUMN_TIME_FIELD; –2011-11-07 04:21:17
  • SELECT TIMESTAMP1 – INTERVAL '02' DAY FROM COLUMN_TIME_FIELD; — 2014-01-05 04:21:17
  • SELECT TIMESTAMP1 – INTERVAL '02' HOUR FROM COLUMN_TIME_FIELD; — 2014-01-07 02:21:17
  • SELECT TIMESTAMP1 – INTERVAL '02' MINUTE FROM COLUMN_TIME_FIELD; — 2014-01-07 04:19:17
  • SELECT TIMESTAMP1 – INTERVAL '02' SECOND FROM COLUMN_TIME_FIELD; — 2014-01-07 04:21:15
  • SELECT TIMESTAMP1 – INTERVAL '02 02' DAY TO HOUR FROM COLUMN_TIME_FIELD; — 2014-01-05 02:21:17
  • SELECT TIMESTAMP1 – INTERVAL '02 02:02' DAY TO MINUTE FROM COLUMN_TIME_FIELD; — 2014-01-05 02:19:17
  • SELECT TIMESTAMP1 – INTERVAL '02 02:02:02' DAY TO SECOND FROM COLUMN_TIME_FIELD; — 2014-01-05 02:19:15
  • SELECT TIMESTAMP1 – INTERVAL '02:02' HOUR TO MINUTE FROM COLUMN_TIME_FIELD; — 2014-01-07 02:19:17
  • SELECT TIMESTAMP1 – INTERVAL '02:02:02' HOUR TO SECOND FROM COLUMN_TIME_FIELD; — 2014-01-07 02:19:15
  • SELECT TIMESTAMP1 – INTERVAL '02:02' MINUTE TO SECOND FROM COLUMN_TIME_FIELD; — 2014-01-07 04:19:1

 

  • Subtract two timestamp columns:
    SELECT (CURRENT_TIMESTAMP(0) – TIMESTAMP1) HOUR TO SECOND(0) FROM COLUMN_TIME_FIELD; — 0:12:52

Add a Comment

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