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!
Tim S.
_______________________________________________
Mailing list: https://launchpad.net/~drizzle-discuss
Post to : [email protected]
Unsubscribe : https://launchpad.net/~drizzle-discuss
More help : https://help.launchpad.net/ListHelp
