Blog Archive

Thursday 11 April 2013

Teradata Timestamp Part 1


  • The TIMESTAMP Data type integrates date and Time in a single data type.

  • Default timestamp is TIMESTAMP(6)

  • As TIMESTAMP consists of TIME and DATE, the amount of storage required for TIMESTAMP is 10 bytes ( 4 bytes for Date and 6 bytes for Time). This space requirement is same irrespective of the precision.

  • CURRENT_TIMESTAMP is the reserved word and provides the current timestamp

  • Timestamp may be represented as literals using the following formats:

TIMESTAMP 'YYYY-MM-DD HH:MM:SS.NNNNNN'

  • The character string representation might take from 19 bytes to 26 bytes

TIMESTAMP(0) --> '2013-12-12 11:12:13' --> CHAR(19)
TIMESTAMP(6) --> '2013-12-12 11:13:12.331421' --> CHAR(26)

  • Earlier we studied that Date data type can be combined YEAR-MONTH interval types. But Timestamp can be combined with all the interval types.

Examples:

SELECT TIMESTAMP '2013-12-12 12:12:31.121' + INTERVAL '3' DAY

12/15/2013 12:12:31.121

SELECT TIMESTAMP '2013-12-12 12:12:31.121' + INTERVAL '3 12:12' DAY TO MINUTE

12/16/2013 00:24:31.121

SELECT TIMESTAMP '2013-12-12 12:12:31.121' + INTERVAL '3' SECOND

12/12/2013 12:12:34.121

SELECT TIMESTAMP '2013-12-12 12:12:31.121' + INTERVAL '3' YEAR

12/12/2016 12:12:31.121

As with Time operations, the resulting timestamp will have precision of the starting time.
As shown in the above resulting output is TIMESTAMP(3) , irrespective of the interval precision.

However we can always use CAST to convert it to a different timestamp precision.

No comments:

Post a Comment