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.