Brian Moon wrote:
On 12/23/08 3:37 PM, Baron Schwartz wrote:
Yes, but it's not stored in UTC in MySQL and it consumes 8 bytes (never
have figured out why since TIME and DATE each only use 3 bytes)
whereas,
currently, TIMESTAMP only stores 4 bytes.
Different byte-packing strategies (from manual):
DATE: A three-byte integer packed as DD + MM×32 + YYYY×16×32
TIME: A three-byte integer packed as DD×24×3600 + HH×3600 + MM×60 + SS
DATETIME: Eight bytes:
* A four-byte integer packed as YYYY×10000 + MM×100 + DD
* A four-byte integer packed as HH×10000 + MM×100 + SS
The reason for this is because the data has to be packed into a byte
structure so that when read, indexes can access each "part" of the
date/time
separately by simply dividing or bit-shifting the contents of the field.
I'm not sure if there is a better way of packing the pieces of a
datetime
into a smaller structure, but of course, always open to suggestions...
Yes, there is. SQL Server does it, though exactly how it does it
eludes me. (I also thing SQL Server doesn't have microsecond
granularity; seems like it's 3 microseconds +/- or something like
that.)
Perhaps they store it as a 4 byte integer? In Phorum we store all
dates/times as 4 byte integers. They are all stored as unix timestamps.
If we do away with time zones, I don't see why we can't store all
dates/times in drizzle the same way. For that matter, if no time zone
info is used in storage (local or otherwise) I would prefer to get dates
and times back from the server as unix timestamps. If I get it back in
a Y-m-d H:i:s format, I then have to convert that to a timestamp before
I can convert it to my desired date/time format with a time zone.
Now, that does not include microseconds. Perhaps with microseconds you
could do it in 8 bytes. 4 for the whole number and 4 for the decimal. I
am not great with bits and bytes so I may be oversimplifying it.
Bit-coded with support for a 16384-year range (sufficient to be
standards-compliant - range 1..9999) and micro-second precision fits in
60 bits.
As a binary number you may be able to squeeze out an additional bit, but
bit-coding allows you to support leap seconds in a convenient manner.
Unfortunately, 4 bits is too small for a time-zone specification (unless
you allow maximum 16 timezones to be used ;) So a timestamp WITH time
zone probably has to go in more than 8 bytes.
Thanks,
Roy
_______________________________________________
Mailing list: https://launchpad.net/~drizzle-discuss
Post to : [email protected]
Unsubscribe : https://launchpad.net/~drizzle-discuss
More help : https://help.launchpad.net/ListHelp
