On 8/27/08, Giuseppe Maxia <[EMAIL PROTECTED]> wrote: Everyone: please remember that users live in a definite TZ, and they > want to enter the time in their TZ. > If Drizzle allows me to enter 2008-08-26 10:00 when I am in London, > and retrieve it as 2008-08-26 01:00 when I am in Los Angeles, then > it's fine to store the time in UTC. If I (the user) have to convert > times to UTC before storing them, the proposed solution is wrong. > I think that TZ should be part of a DATETIME field.
Giuseppe, The user does not have to convert times to UTC -- the *programmer* does. The programmer is responsible for the interface between the data and the end user; and time zones (in my opinion) are a display feature. The programmer will likely want to store a "preferred timezone" for a user within an application, in order to display a default for the customer, but that's not a database issue. Let me also clarify that I'm not exactly sure what goes in the database wrt timezones. Saying "just make the timezone UTC" is a misnomer, because I'm proposing not having timezones in the database at all. ie, "making the timezone irrelevant by assuming there's only one, and assuming folks are going to put in UTC." Right now, MySQL takes the lazy way out (shocking!). It defaults to "whatever the system is", and it makes functions like "NOW()" and "SYSDATE()" and "CURRENT_DATE()", etc very easy to implement (my guess, correct me if I'm wrong). If your OS is in EST, what does it mean when we keep saying "store UTC in the database"? Should Drizzle know that EST is UTC-5 and EDT is UTC-4 and convert appropriately when someone wants to use NOW() or SYSDATE()? Does SYSDATE() still mean "system date" in this case? (I'd say yes, because it does right now when you've changed the default behavior of MySQL) My point being, that the reason MySQL is the way it is, is because that is the easy way. There's absolutely no way for the database to be able to enforce or even know what timezone a datetime is in. The 2 solutions to this are "add the timezone information to the datetime" and "make the database all one timezone and convert". Daylight Saving Time puts a monkey wrench in the whole scenario. That being said, it seems to me like timezone functionality is an *excellent* opportunity for a plugin. The plugin would override the data types and override the functions that deal with time. I'd love to hear from someone intimate with the code exactly how difficult it is to make the database store datetime information without timezone information, taking into account user behavior (imagine you've stored everything in datetimes and you're in EST/EDT, and now you want to convert your data to UTC....if the database didn't already convert it for you, you're going to have a nightmare trying to convert some of the times to +5 hours and some of the other times to +4 hours!) (also, at least one person said unix timestamps don't have time zones, which is absolutely untrue -- on the machines I've worked on, the timestamp is # of seconds since the epoch until the current system time -- which is in a time zone! And there's no way for it to be true, because there's no way a machine knows what the "real" date is....it could trust an ntp server, but it still doesn't guarantee that the ntp server is correct.) -Sheeri
_______________________________________________ Mailing list: https://launchpad.net/~drizzle-discuss Post to : [email protected] Unsubscribe : https://launchpad.net/~drizzle-discuss More help : https://help.launchpad.net/ListHelp
