Re: Representing data on disk in an MV database - was Re: foundations of relational theory? - some references for the truly starving
Date: Fri, 24 Oct 2003 03:49:49 GMT
Message-ID: <xj1mb.3927$wc3.1603_at_newsread3.news.pas.earthlink.net>
Mike Preece wrote:
> Jonathan Leffler <jleffler_at_earthlink.net> wrote:
>>[...] Informix databases,
>>for example, store a TIMESTAMP(5) - actually, DATETIME YEAR TO
>>FRACTION(5) in Informix's rather weird notation, but equivalent to the
>>SQL standard TIMESTAMP type - in just 11 bytes of data, but the text
>>version requires 25 bytes. Further, that's a fixed 25 bytes (or, at
>>best, the variable part is just 0-5 digits after the decimal point
>>separating seconds from fractional seconds, and possibly the decimal
>>point itself - 19-25 bytes).
> > I'm sorry. I've read that a couple of times. Are you interested in the > number of bytes required to store the current date & time? And you > also want to store fractions of seconds? Milliseconds?
Yes - though the 5 represents 10s of microseconds or hundredths of milliseconds. Sorry for assuming too much familiarity with the SQL standard. In SQL, the TIMESTAMP(N) value records the year, month, day, hour, minute, second and N (decimal) digits of fractions of a second. For example, the SQL standard notation (almost conforming to ISO 8601:2000, which is not the SQL standard - that's ISO/IEC 9075:1999, with a number of parts) is:
2003-10-22 15:43:39.22391 (for N = 5).
Informix only happens to support 5 digits (10s of microseconds) for a variety of hysterical raisins; the SQL standard requires support for 6 digits (microseconds). For the value shown, the default printed value uses 25 characters with the separators as shown; it could be compressed to 19 digits with implied separators, I suppose. As it happens, Informix internally uses a floating point decimal number stored in base-100 (centesimal) digits, so it needs 10 bytes for the digits and one byte for the exponent and sign bits - hence 11 bytes.
For plain DATE values, Informix stores the integer number of days since a reference date (which happens to be 1899-12-31, so 1900-01-01 was day 1). This is even more compact, using 4 bytes as against 10 with explicit separators or 8 with implicit separators.
-- Jonathan Leffler #include <disclaimer.h> Email: jleffler_at_earthlink.net, jleffler_at_us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/ PS: Read "historical reasons" for "hysterical raisins".Received on Fri Oct 24 2003 - 05:49:49 CEST