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

SQL Operators and User-Defined Functions

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Published
January 2021
Language
English (United States)
Last Update
2023-04-27
dita:mapPath
xwv1596137968859.ditamap
dita:ditavalPath
hoy1596145193032.ditaval
dita:id
B035-1210
lifecycle
previous
Product Category
Teradata Vantage™

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.