Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Tue, 21 Nov 2006 @ 22:13:31 GMT


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


Subj:   Re: Length of timestamp
 
From:   Dieter Noeth

Simard Rudel wrote:

  We tried to use the TIMESTAMP(6) for data type in a table, in the documentation it's say than the length of the internal stored form is 10 bytes for a timestamp data type. Surprise, the length we see in DBC.COLUMNS views for our table is 26 bytes. If I looked for all DBC tables how their timestamp as been defined, they are fine with 10 bytes.  


For a Timestamp ColumnLength indicates the *logical* (string) length, not the physical. It's a value between 19 and 26, depending on fractional seconds.

DBC tables are special tables created by sysinit and maybe ColumnLength was 10 when it was first implemented in an older version?

To get the physical size use a modified dbc.columns:

     CASE ColumnType
       WHEN 'AT' THEN  6  -- time
       WHEN 'BO' THEN 37  -- BLOB OID: 37 bytes, according to Filer stored within a VARBYTE(50)
       WHEN 'CO' THEN 37  -- CLOB OID: 37 bytes, according to Filer stored within a VARBYTE(50)
       WHEN 'DH' THEN  4  -- interval day to hour
       WHEN 'DM' THEN  8  -- interval day to minute
       WHEN 'DS' THEN 10  -- interval day to second
       WHEN 'DY' THEN  2  -- interval day
       WHEN 'HM' THEN  4  -- interval hour to minute
       WHEN 'HS' THEN  8  -- interval hour to second
       WHEN 'HR' THEN  2  -- interval hour
       WHEN 'MI' THEN  2  -- interval minute
       WHEN 'MO' THEN  2  -- interval month
       WHEN 'MS' THEN  6  -- interval minute to second
       WHEN 'SC' THEN  2  -- interval second
       WHEN 'SZ' THEN 12  -- timestamp with timezone
       WHEN 'TS' THEN 10  -- timestamp
       WHEN 'TZ' THEN  8  -- time with timezone
       WHEN 'YM' THEN  4  -- interval year to month
       WHEN 'YR' THEN  2  -- interval year
       ELSE ColumnLength
     END AS ColumnByteLength

Dieter



     
  <Prev Next>   <<First <Prev Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023