 |
 |
Archives of the TeradataForum
Message Posted: Tue, 21 Nov 2006 @ 22:13:31 GMT
| 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
| |
