Re: Representing data on disk in an MV database - was Re: foundations of relational theory? - some references for the truly starving

Jonathan Leffler <jleffler_at_earthlink.net> wrote in message news:<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.

Dates on Pick are also stored as a number of days - with day 1 being 1 Jan 1968. Times are a number of seconds after midnight. We can't get down below milliseconds. We'd therefore use 5 chars for the date (until 15 Oct 2241), a delimiter, between 1-5 chars for the time, another delimiter and the number of milliseconds. So 223 milliseconds after 15:43:39 on 2003-10-22 would take up 15 bytes (13079*56621*223). I'm not really sure why this is particularly relevant - unless you want or need a timestamp on every update (which you can do very easily if you want, I hasten to add, using a trigger or whatever).

Mike. Received on Fri Oct 24 2003 - 12:55:00 CEST