15.00 - TS_COMPRESS - Teradata Database

Teradata Database SQL Functions, Operators, Expressions, and Predicates

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1145-015K
Language
English (United States)

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:

  • 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.

    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);