Dedicated to design and performance of databases and audio systems.
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:
SMALLINT,BYTEINT,BYTEINT,BYTEINT,BYTEINT, andDECIMAL(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:
DATE: 4 bytes; TIMESTAMP(6): 10 bytes)DATEs can be used in Value-Ordered NUSIs, andTIMESTAMP/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