Timestamp
A timestamp is a six-part or seven-part value (year, month, day, hour, minute, second, and optional fractional second) with an optional time zone specification, that represents a date and time.
The time portion of a timestamp value can includes a specification of fractional seconds. The number of digits in the fractional seconds portion is specified using an attribute in the range 0 - 12 with a default of 6. The time zone is the difference in hours and minutes between local time and UTC. The range of the hour offset is -12 to 14, and the minute offset is 00 to 59. The optional time zone is specified in the format ±th:tm, with values ranging from -12:59 to +14:00. A timestamp data type is TIMESTAMP WITHOUT TIME ZONE (generically referred to as TIMESTAMP) or TIMESTAMP WITH TIME ZONE.
- TIMESTAMP WITHOUT TIME ZONE
- The internal representation of a timestamp is a string of 7 to
13 bytes, each of which consists of two packed decimal digits. The
first 4 bytes represent the date, the next 3 bytes the time, and the
remaining bytes the fractional seconds based on the precision of the
timestamp.
The length of a TIMESTAMP WITHOUT TIME ZONE column as described in the catalog is the internal length, which is 7 to 13 bytes.
The length of a TIMESTAMP WITHOUT TIME ZONE column as described in the SQLDA is between 19 and 32 bytes, which corresponds to the length for the character-string representation of the value. For example, a 19 byte character-string representation has no fractional seconds; a 26 byte character-string representation has 6 digits of fractional seconds; and a 29 byte character-string representation has 9 digits of fractional seconds.
A character-string representation must have an actual length that is not greater than 255 bytes and must not be a CLOB or DBCLOB.
- TIMESTAMP WITH TIME ZONE
- The external representation of a TIMESTAMP WITH TIME ZONE value
is the local timestamp followed by the time zone offset. For example,
New York is 5 hours behind London during standard time, so New York
time "8:15" on 2010-02-10 can be represented as '2010-02-10-08.15.00-5:00'.
This timestamp with time zone value represents a UTC value '2010-02-10-13.15.00',
which is derived by subtracting the time zone offset from local timestamp.
The internal representation of a timestamp is a string of 9 to 15 bytes that contains the UTC timestamp followed by the time zone. Each byte consists of 2 packed decimal digits. The first byte consists of two packed decimal digits representing time zone hour and the first bit is used to represent the sign of the time zone offset. The second byte of time zone, representing the time zone minute, also consists of two packed decimal digits. For example, time zone "-3:30" is represented as X'8330' and time zone "5:30" is represented as X'0530'.
The length of a TIMESTAMP WITH TIME ZONE column as described in the catalog is the internal length, which is between 9 to 15 bytes (a 7 to 13 bytes timestamp followed by 2 bytes time zone).
The length of a TIMESTAMP WITH TIME ZONE column as described in the SQLDA is the external length, which is between 147 and 160 bytes and corresponds to the length for the character-string representation of the value. For example, a 147 byte character representation has no fractional seconds, and a 160 byte character-string representation has 12 digits of fractional seconds, where the time zone component is 7 bytes.
A character-string representation must have an actual length that is not greater than 255 bytes and must not be a CLOB or DBCLOB. DCLGEN therefore defines 147 to 160 byte, varying-length string variables for TIMESTAMP WITH TIME ZONE columns.