On 18 Mar 2003, at 10:49, Paul DuBois wrote:
> Pursuant to last week's discussion of why, if DATE and TIME each take
> three bytes, does DATETIME take eight bytes, I found the following in
> the internals.texi document:
Thanks for tracking that down. I spent a little while looking in the
source and had figured out DATETIME, more or less, but not DATE or
TIME.
> @strong{DATE}
> @itemize @bullet
> @item
> Storage: fixed-length series of binary integers, always three bytes
> long.
> @item
> Example: a DATE column containing '0001-01-01' looks like:@*
> @code{hexadecimal 21 02 00}
> @end itemize
That explanation leaves a little to be desired. Can anyone explain
how '0001-01-01' maps to 0x0221 (which is decimal 545)?
> @strong{DATETIME}
> @itemize @bullet
> @item
> Storage: eight bytes.
> @item
> Part 1 is a 32-bit integer containing year*10000 + month*100 + day.
> @item
> Part 2 is a 32-bit integer containing hour*10000 + minute*100 + second.
> @item
> Example: a DATETIME column for '0001-01-01 01:01:01' looks like:@*
> @code{hexadecimal B5 2E 11 5A 02 00 00 00}
> @end itemize
At first I thought this example made sense, since 0x025a112eb5 is
decimal 10101010101. But it's supposed to be two 32-bit integers
rather than one 64-bit integer, and the two parts would be 0x00000002
and 0x5a112eb5, which don't seem to convert to a date and time.
Anyway, it appears that the date part of a DATETIME is stored less
efficiently than a DATE, and the time part is stored less efficiently
than a TIME (though that's just because 1 byte of the 4 is always
00). Presumably that's for ease (and hopefully speed) of
calculation.
> @strong{TIME}
> @itemize @bullet
> @item
> Storage: a value offset from 8385959, always three bytes long.
> @item
> Example: a TIME column containing '01:01:01' looks like:@*
> @code{hexadecimal 75 27 00}
> @end itemize
0x2775 is decimal 10101, so the example doesn't seem to involve an
offset.
[Filter fodder: SQL]
--
Keith C. Ivey <[EMAIL PROTECTED]>
Tobacco Documents Online
http://tobaccodocuments.org
Phone 202-667-6653
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
