TS_COMPRESS
Purpose
Compresses TIME and TIMESTAMP with or without time zone to the minimum possible bytes.
Syntax
where:
|
Syntax element… |
Specifies… |
|
TD_SYSFNLIB |
the name of the database where the function is located. |
|
td_anytype |
in this function TIME or TIMESTAMP with or without time zone data types. |
This function takes no arguments when used as part of the COMPRESS USING or DECOMPRESS USING phrases. For more information about the COMPRESS/DECOMPRESS phrase, see SQL Data Types and Literals.
ANSI Compliance
This is a Teradata extension to the ANSI SQL:2011 standard.
Argument Type and Rules
The output of this function is the input to the TS_DECOMPRESS function.
Though the input argument type is td_anytype, the algorithm for this function only supports TIME and TIMESTAMP data types. Each value in TIME and TIMESTAMP (for example, year, month, or day) is compressed into the minimum possible bits individually and then concatenated.
Result Type
The result data type is VARBYTE(20).
Usage Notes
When creating algorithmic compression (ALC) columns, you should include the compression and decompression functions by which the column is going to be compressed and uncompressed respectively.
When you insert data into ALC columns, the data is compressed. When you select ALC columns the data is uncompressed.
|
Uncompressed Data for the Following Data Types… |
Requires the Following Bytes… |
|
TIME |
6 |
|
TIMESTAMP |
10 |
|
TIME WITH TIME ZONE |
8 |
|
TIMESTAMP WITH TIME ZONE |
12 |
Example
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:
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.
For more information about ALC, see SQL Data Types and Literals.
Example
The following SQL statement creates a TIMESTAMP column in t_timestamp that may be stored in compressed form:
CREATE TABLE table t_timestamp(i int, j timestamp(0) compress using td_sysfnlib.ts_compress decompress using td_sysfnlib.ts_decompress);