TeraData DATE-TIME part 8 - TIME datatype

• Prior to V2R3, keyword TIME was only selectable and would return current time in format 'HH:MM:SS'.

However this did not have any time intelligence in it. We could not perform any calculations on this time.

select TIME;

Time

16:06:57

In V2R3 a TIME data type was introduced and it had all the clock intelligence , just as DATE as calendar intelligence.

• TIME data type is also  made up of combination of multiple fields- Hour. Minute and Seconds.

• TIME data type supports precision of up to 6 places. .ie TIME(0) to TIME(6)

• When representing time literal, the character string should conform to the TIME data type format. The exact degree of the precision will be determined by the parser as it scans the literal. Maximum precision is 6 digits.

Ex:

If we have it a literal '12:21:31.4444' it will parse the literal and data type will be determined as TIME(4).

If we have a literal as '11:12:13.531983' it will parse the literal and the data type will be determined as TIME(6).

• Internal Storage required for TIME data type is 6 bytes.

Time data type is of the format HH:MM:SS.NNNNNN    

HH : hour part : data type is BYTEINT . Therefore 1 byte.

MM: Minute part : data type is BYTEINT . Therefore 1 byte.

SS.NNNNNN: Seconds part has 2 digits before the decimal and 6 digits after. Hence it gets a data type of DECIMAL(8,6) which takes 4 bytes.

Thus collectively TIME data type takes 6 bytes.

Note that the TIME data type requires 6 bytes irrespective of the precision we choose.

• When converting TIME to character strings the length of the target character string may differ depending upon the precision

TIME(0) --> HH:MM:SS --> CHAR(8)

TIME(6) --> HH:MM:SS.NNNNNN --> CHAR(15)