Blog Archive

Thursday 11 April 2013

Teradata Timestamp Part 2- Timestamp subtraction


Time Stamp subtraction:

Timestamp subtraction can yield any interval type. (both YEAR-MONTH and DAY-TIME)

As explained with Time subtraction the result should always be cast to a specific datatype.

SELECT TIMESTAMP '2013-12-12 12:12:31.121' - TIMESTAMP '2013-11-12 12:12:12'

Above query though looks legitimate will fail as we have not specified the interval type.

The following query works

SELECT (TIMESTAMP '2013-12-12 12:12:31.121' - TIMESTAMP '2013-11-12 12:12:12') YEAR

This gives 0.

Note that no rounding off occurs, if the output interval type does not support.


Example 1:

SELECT (TIMESTAMP '2013-12-12 12:12:31.121' - TIMESTAMP '2013-11-12 12:12:12') YEAR TO MONTH

0-01


Example 2:

SELECT (TIMESTAMP '2013-12-12 12:12:31.121' - TIMESTAMP '2013-11-12 12:12:12') DAY TO SECOND;

(2013-12-12 12:12:31.121 - 2013-11-12 12:12:12) DAY TO SECON
 30 00:00:19.121000

As explained with TIME, Adding Two TIME is illogical, similarly adding Timestamps is also illogical.

No comments:

Post a Comment