On Tuesday 23 December 2008 16:10:11 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. > > 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... > > -jay
Well as you pointed out a datetime packed similarly to date+time require at most six bytes and there is space left for hundreds of seconds, extending it to eight bytes give a nanosecond precision YYYY 2^14 == 16384 MM 2^4 DD 2^5 hh 2^5 mm 2^6 ss 2^6 ==== 40 bits = 5 bytes add 3 bytes for nanosecond precision if needed 8 bytes for a serious DATETIME format \o/ /me for one would be glad to be able to use these the stuff about indexes can access each "part" still apply with bit shifting - francesco (vivo) _______________________________________________ Mailing list: https://launchpad.net/~drizzle-discuss Post to : [email protected] Unsubscribe : https://launchpad.net/~drizzle-discuss More help : https://help.launchpad.net/ListHelp
