Tim Soderstrom wrote:
On Dec 23, 2008, at 9:10 AM, Jay Pipes wrote:
Tim Soderstrom wrote:
On Dec 23, 2008, at 8:46 AM, Jay Pipes wrote:
Tim Soderstrom wrote:
On Dec 22, 2008, at 9:47 PM, Jay Pipes wrote:
Hi all!
A while ago, the list discussed removing timezones from the server
and met with little resistance from folks.
So, some work went in to removing timezones from the server.
However, for reasons I won't go into here, timezones are still
present in the server and are causing, let us say, a bit of a
headache. If we can fully commit to getting rid of timezones, I
think I can dramatically improve the simplicity of how time and
dates are handled in the server.
Questions for everyone:
1) What do people think about changing the prescribed behaviour of
FROM_UNIXTIME() and UNIX_TIMESTAMP() to *not* take into account
the system time zone? Currently, even though we don't support
time zones, for some reason, we're currently accounting for the
system time zone in these functions. I can't really understand
why, and it's causing bugs. :)
I'd say GMT/UTC, but I suppose the application could handle it. I
like pushing GMT/UTC though because then you know the time is
consistent relative to at least one area of the world and can,
thus, simply do the math (which the application could easily do at
that point). What I don't want to go back to is being able to store
these things in local time. I think that'd be a step backward IMHO.
Plus, if someone is replicating to the other side of the world,
having times stored in one common, well known, standardized way,
would make sense.
<offtopic>
Heck, actually I've been wanting to do an experiment where *I*
switch to UTC, just so I can prove to the world (or really just
myself) that timezones are stupid and we all should be using UTC no
matter where we live. Getting up at 15:30pm or 6:30am is relative.
Of course, I also think the US should adopt metric (coming from a
native born US citizen even!). Anyways...
</offtopic>
2) What do people think about adding microsecond to the TIMESTAMP
data type by default? This would require 4 more bytes of storage
per row.
Well, does a Blog post really need microsecond granularity? So I'd
say no here, because by 2038, won't we have to expand this again,
making it 10 or 12 bytes? I think there should be an additional
data-type that does to this (so users have the choice). Like, uhh,
FULLTIMESTAMP or something (I'm not very creative this
morning...maybe it's because it's before coffee :)
Well, in this case, you would just use a DATETIME, which has
precision to the second, no?
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.
Oh snap! I've been wondering that for quite some time. Where is that
exactly in the manual (I've looked, but was probably right under my
nose)? Thanks Jay!
http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html
Or, alternately, the source code files:
drizzled/field/timetype.cc
drizzled/field/date.cc
drizzled/field/datetime.cc
Here is an example from date.cc:
int Field_date::store_time(DRIZZLE_TIME *ltime,
enum enum_drizzle_timestamp_type time_type)
{
long tmp;
int error= 0;
<snip>
tmp=ltime->year*16*32+ltime->month*32+ltime->day;
<snip>
int3store(ptr,tmp);
return error;
}
Cheers!
-jay
_______________________________________________
Mailing list: https://launchpad.net/~drizzle-discuss
Post to : [email protected]
Unsubscribe : https://launchpad.net/~drizzle-discuss
More help : https://help.launchpad.net/ListHelp
