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.
DATE
s as INTEGER
s 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)DATE
s 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