16.20 - Example: Compressing the TIME Data Type - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Operators and User-Defined Functions

Product
Advanced SQL Engine
Teradata Database
Release Number
16.20
Release Date
April 2020
Content Type
Administration
Programming Reference
Publication ID
B035-1210-162K
Language
English (United States)

Uncompressed data requires 6 bytes for the TIME data type, broken down into bytes, possible range and minimum possible bits as shown in the following table.

Hour Byte Possible Range Minimum Possible Bits
Hour 1 00 23 5
Minute 1 00 59 6
Second 4 00 000000 61.999999 6 to 26 depending on the seconds precision

If you insert the following value for TIME:

INSERT into t1(1, TIME'03:38:06');

as specified in the following SQL statement:

CREATE TABLE table t1(pk int, col1 time(0) compress using ts_compress decompress using ts_decompress);
the required bits are as follows:
  • 3 hours = 5
  • 38 minutes = 6
  • 6 seconds = 6

The total number of required bits needed to represent TIME is 17. The minimum number of bytes is 3. Since TIME (without TIMEZONE) takes 6 bytes, TS_COMPRESS compresses 6 bytes to 3. The function saves 3 bytes.