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.
DATE is translated into an internal
INTEGER based on
((Year - 1900) * 10,000) + (Month * 100) + Day
Therefore, 3/14/2012 is stored as 1120314.
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:
The performance considerations between the two are obvious. Therefore, if a
DATE grain attribute is required, it should not be defined as a
DATE: 4 bytes;
TIMESTAMP(6): 10 bytes)
DATEs can be used in Value-Ordered NUSIs, and
TIMEconversions 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.