Archives of the TeradataForum

Message Posted: Fri, 01 Dec 2006 @ 10:11:00 GMT


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

Subj:		Re: Length of timestamp

From:		Anomy Anom

<-- Anonymously Posted: Thursday, November 30, 2006 19:04 -->

I think what I would do is create 4 tables with one column each: a timestamp(0), timestamp(6) and then two more of the same type with time zone. Then I would insert a single row. At that point I would use BTEQ to .export data file=xxxx(n).dat.

Then, I would subtract the two byte header and the one byte carriage return and I would have the answer.

I performed the test you recommended but I still have questions. I created two, single column tables. One with TIMESTAMP(0) and the other with TIMESTAMP(6) data type. I created SQL files in Unix that just do a select * from each table. I defined the .export file and ran the SQL files for each in BTEQ as shown below...

     BTEQ -- Enter your DBC/SQL request or BTEQ command:
     export data file=./TS6.dat
     *** To reset export, type .EXPORT RESET

     BTEQ -- Enter your DBC/SQL request or BTEQ command:
     run file=./TS6length.sql

     BTEQ -- Enter your DBC/SQL request or BTEQ command:
     select * from db02.datatype_TS6;

     *** Success, Stmt# 1 ActivityCount = 1
     *** Query completed. One row found. One column returned.
     *** Total elapsed time was 1 second.

The SQL files run in BTEQ populated each .dat file for Timestamp(0) and Timestamp(6). As you can see below, TS0 has 22 bytes in the file and TS6 has 29 bytes.

Unix files:

     Bytes   Date     Time    Filename
     ---------------------------------
     22      Nov 30   18:34   TS0.dat
     29      Nov 30   18:37   TS6.dat

After subtracting the 3 bytes for header and carriage return I have 19 bytes for TS0 and 26 bytes for TS6. This is exactly what the DBC.COLUMNS table was telling me, which is where Teradata Administrator is getting its column length info.

DBC.COLUMNS:

     Database   TableName      ColumnName      ColumnFormat             ColumnLength
     -------------------------------------------------------------------------------
     DB02       DATATYPE_TS6   TIMESTAMPsix    YYYY-MM-DDBHH:MI:SS.S(6)           26
     DB02       DATATYPE_TS0   TIMESTAMPzero   YYYY-MM-DDBHH:MI:SS                19

However, in the Teradata documentation...

     Database Design
     Chapter 15: Database-Level Capacity Planning Considerations

On page 15-85 it states the following regarding date & time datatypes and the disk space they occupy.

     DATE: 4 bytes
     TIME: 6 bytes
     TIME WITH TIME ZONE: 8 bytes
     TIMESTAMP WITH TIME ZONE: 12 bytes

Assuming the Time Zone takes 2 bytes (by comparing TIME to TIME WITH TIME ZONE above), this would tell me that TIMESTAMP would consume 10 bytes of disk storage, which is in line with Dieter's earlier comments.

I'm not sure if I did something incorrectly in the BTEQ above or am misreading the doc but can anyone help me confirm this?

Thanks for your help.


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

Attachments

Library

Quick Reference