Archives of the TeradataForum

Message Posted: Thu, 12 Jun 2008 @ 20:17:50 GMT

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

Victor:

I don't have many occasions to really use timestamp. It is obvious that you can control the number of decimal places returned, but what is returned in each location is something which I never paid close attention regarding timestamp data returned beyond the two decimal places in seconds as long as it was greater than .000000. I re-read the manual for TD 12 and it specifies the minimum value as .000000 and the maximum as .999999. There is even an example that uses the value of: '1999-01-01 23:59:59.999999' for exported data on page 126. On page 130 there is another example referencing time zone with this example: '11:37:58.123456+08:00'. This makes the manual a bit misleading where the max value according to your reading .990000.

I don't remember what a V2R6 demo version returns, but I just did some more testing on a TD 12 demo version on XP and 5 times in a row everything after the decimal point was .000000 for timestamp(6). So either TD 12 is less precise, or the OS (Windows vs UNIX) makes a difference too. My guess would be the OS instead of changes within Teradata.

I thought that I read somewhere that it takes more storage for a timestamp(6) column than timestamp(0), but do not remember the specifics. The TD 12 manual indicates the seconds are stored as decimal(8,6). This would seem to imply both timestamp(0) and timestamp(6) are the same length. This leads me to believe that currently between two bytes and three bytes of physical disk is wasted for each time and timestamp column in a table as long as you store the seconds.

Here is my question: Since Teradata is not currently using the decimal places after two, do you know if the storage allocated for timestamp(0) and timestamp(6) are actually the same? Or is disk space wasted when you specify timestamp(6)?

Thanks for sharing your detailed insight on this topic.

Michael Larkins
Certified Teradata Master
Certified Teradata SQL Instructor

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