Archives of the TeradataForum

Message Posted: Wed, 20 Oct 2010 @ 14:09:49 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Even if the database supports partitioning on timestamps, I would not recommend it. The reason is that timestamps are actually structures defined thus:

     typedef struct TimeStamp
     {
         DECIMAL4 seconds;   /* represented as DECIMAL(8,6) */
         SMALLINT year;
         BYTEINT  month;
         BYTEINT  day;
         BYTEINT  hour;
         BYTEINT  minute;
     } TimeStamp;

Therefore, converting a timestamp to a partitioning value (or comparing two timestamps) may require operations on up to six fields -- much more CPU-intensive than, say, an integer. This is true even without the confusion wrought by time zones.

Partitioning on timestamps could be avoided with a little ingenuity. For instance, if you needed a partition for every half hour of the day, you could convert the time portion to a smallint, 0-47, and train the users (or the BI tool) to ask for those instead of literal times.

	<Prev	Next>		<<First	<Prev	Next>	Last>>