MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Keith C. Ivey Date:March 18 2003 5:44pm
Subject:Re: Stalking the wily 8-byte DATETIME !!
View as plain text  
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 <keith@stripped>
Tobacco Documents Online
http://tobaccodocuments.org
Phone 202-667-6653
Thread
Stalking the wily 8-byte DATETIME !!Paul DuBois18 Mar
  • Re: Stalking the wily 8-byte DATETIME !!Keith C. Ivey18 Mar