b¬äd†ødd

Dedicated to design and performance of databases and audio systems.

Date vs.Timestamp

A question I receive a lot is the how a TIMESTAMP internally differs from a DATE data type beyond the obvious difference of TIME. There are a number of performance considerations.

It is a common assumption that the TIMESTAMP is simply a DATE with the TIME added. From a storage standpoint, not true.

A DATE is translated into an internal INTEGER based on

((Year - 1900) * 10,000) + (Month * 100) + Day

Therefore, 3/14/2012 is stored as 1120314.

DATEs as INTEGERs allow for integer math. For example, 2012-03-14 + 1 = 2012-03-15. The optimizer will do the internal conversion for other inter-month values (e.g. 2012-03-31 + 1 = 2012-04-01) where doing a simple integer equivalent would not work correctly (e.g. 1120331 + 1 = 1120332, which is not a valid date).

TIMESTAMP, however, is not a DATE concatenated with TIME. Instead, it is internally stored as six separate fields glued together:

  • Year -> SMALLINT,
  • Month -> BYTEINT,
  • Day -> BYTEINT,
  • Hour -> BYTEINT,
  • Minute -> BYTEINT, and
  • Second -> DECIMAL(8,6), assuming TIMESTAMP(6)

The performance considerations between the two are obvious. Therefore, if a DATE grain attribute is required, it should not be defined as a TIMESTAMP.

Considerations:

  1. Performance
  2. space consumed (DATE: 4 bytes; TIMESTAMP(6): 10 bytes)
  3. functionality: DATEs can be used in Value-Ordered NUSIs, and
  4. in Teradata 13.10+ TIMESTAMP/TIME conversions need to be carefully considered when temporal is enabled due to the internal storage of the values at UTC/GMT/Zulu.

I have seen a lot of applications that store dates as TIMESTAMP data types when it is completely unnecessary. It is truly inefficient from both performance and storage considerations if truly a DATE data type would have sufficed.

I hope this helps with your time-based Teradata analysis.

Cheers
-Brad