On 17-Apr-2015 13:25 -0500, CRPence wrote:
On 17-Apr-2015 11:39 -0500, Dan wrote:
<<SNIP>>
With the additional 6 digits, I presume that more space is needed
to store them than before.
No. Same internal storage irrespective the specified precision. The
difference is merely the amount of precision /visible/ both for
storage and for presentation. IIRC the total amount of storage for
the TIMESTAMP value is 10-bytes, irrespective the amount of precision
from 0 to 12 digits.
<<SNIP>>
After seeing Rob's reply, despite concerns with the possibility of
skewed results due to the calculation methodology of the example given,
I figured I must have been wrong in my above reply. I had to go
digging, to figure out what I must have mis-remembered.
Apparently I incorrectly had recalled what was the amount of internal
storage required for TIMESTAMP(6) values; I had indicated the storage
was already sufficiently capable of recording values of type
TIMESTAMP(12) too. Since confirmed, the larger precision requires more
storage. The storage for TIMESTAMP(6) is indeed 10-bytes, however any
additional precision will require minimally one byte, and up to a
maximum of three bytes *more storage* than is required to store the
default precision of six digits. That implies TIMESTAMP(P>6) will
require more than the 10-bytes.
Because... Apparently the well-defined storage requirement for the
internal representation of a TIMESTAMP value with a variable-precision
[from 0 to 12], ranges from 7 to 13 bytes. For any TIMESTAMP(P), the
amount of storage required, in bytes, is defined by the arithmetic
expression: 7+((P+1)/2)
That expression is derived from, essentially, an summation of the
storage-length requirements of the three distinct TIMESTAMP components
of Date, Time, and Subseconds:
stglen(date_data)+stglen(time_data)+stglen(subsecond_precision_P)
rewritten as: stglen(UINT4)+stglen(UDEC(6))+stglen(UDEC(P))
rewritten as: 4+3+((P+1)/2)
rewritten as: 7+((P+1)/2)
Assuming a typical precedence of arithmetic operators, the expression
7+(P+1)/2 is the same as the above with a parenthetical integer
division, and is what is used to describe the storage size calculation
in the doc reference below:
<
https://www.ibm.com/support/knowledgecenter/api/content/ssw_ibm_i_72/rzatk/MINDTCON.htm>
"...
_Date, Time, and Timestamp Concepts_
_Data Definitions_
...
TIMESTAMP
/Timestamp data type/ The internal format of a Timestamp is a seven
to thirteen byte composite value. The composite is two numbers: one for
date and time respectfully. The date and time numbers have the same
encoding as the date and time data types, with an exception to the time
number. The time number has an additional 0 to 6 bytes for a zero to
twelve packed digit fractional second value. The default is 3 bytes for
a six packed digit microsecond value. The DDAT number must reference a
DDAT with an internal Timestamp format code or be set to zero which
implies internal format. The internal format does not allow trailing blanks.
The timestamp data type supports a second precision. The length can
be determined from the precision by using the following formula:
7+(p+1)/2 where p is the precision. The default precision is 6 which
gives a length of ten.
..."
Contrast that updated 7.2 doc, to the previous release documentation,
in which the size is clearly noted to be a constant 10-bytes:
<
https://www.ibm.com/support/knowledgecenter/api/content/ssw_ibm_i_71/rzatk/MINDTCON.htm>
"...
_Date, Time, and Timestamp Concepts_
_Data Definitions_
...
TIMESTAMP
/Timestamp data type/ The internal format of a Timestamp is a ten
byte composite value. The composite is two numbers: one for date and
time respectfully. The date and time numbers have the same encoding as
the date and time data types, with an exception to the time number. The
time number has an additional 3 bytes for a six packed digit microsecond
value. The DDAT number must reference a DDAT with an internal Timestamp
format code or be set to zero which implies internal format. The
internal format is fixed length, trailing blanks are NOT allowed.
..."
Note: Despite knowing that fewer digits of precision require fewer
bytes of storage for actual TIMESTAMP values, I am not aware if that
variability necessarily would be reflected consistently for the column
definitions of a database file; the implementation for those *could* be
different, yet still accommodating, even though I am doubtful that would
be the case. For example there could be just two implementation sizes,
10b [accommodating P=range(0:6)] and 13b [accommodating P=range(7:12)];
such an implementation would have a payoff for an ALTER that merely
changes the TIMESTAMP precision to a value within the existing range and
could possibly benefit algorithms with reduced variance [perhaps keyed
access path building?], while probably having little impact to the
algorithms for storage and presentation because the required action
likely would be nothing more than an early-exit from including
remaining\additional precision digits [as input or output, respectively].
midrange.com
