On Wed, 2 Jul 2008 10:21:29 -0500 (CDT) David Timothy Strauss <david@fourkitchens.com> wrote:
----- "Ivan Sergio Borgonovo" <mail@webthatworks.it> wrote:
You're not forced to store dates with tz but having a data type that understand tz and support tz operations inside the DB is pretty helpful.
Again... suppose you store datetime in GMT and you want to know who had a beer before 1:00am local time maybe during a DTS change [evil grin]? a tz is not an integer. You can't compute the interval on the PHP side and you don't want to return all the times and filter on the PHP side either.
I have never written an application that needed to answer a time question based on the local time for all rows.
Lucky you.
Your proposal of using date/time columns in the database doesn't fix it, either. You can't query based on "local time" even if MySQL knows all the timezone data.
You can at least in postgresql if you use the right functions and define a suitable schema. I think it would even be easier on MySQL that store tz data in tables... or well maybe I don't know well enough internals of pg in this area, but I know it would be possible without moving around data. I didn't say it is common or trivial but missing the functions that support tz in the DB is going to make the task much harder once you've to answer a time question based on local time. If you want to organise a meeting and you want to collect stats about what are the most busy hours among your managers you've to deal with GMT as well as local times. Storing data in a proper data type will make dealing with tz inside the DB efficient even if not directly from the Drupal DB API. tz aren't integers either so properly dealing with tz is not going to be easy... I think every solution to the tz problem in Drupal is going to be a hack for the near future, timestamps aren't going to help providing a solid solution.
Oh, and good luck dealing with your DB server sometimes having a different timezone than your web server.
That's life. Anyway MySQL as well as PostgreSQL provide nifty settings to cope with the problem.
Why cope when you can avoid the problem entirely?
Because sometimes you've to cope and you can't avoid the problem. Having support for tz in the DB doesn't seem bad... but it is an area of larger jeopardization than datetime support across DB and it looks as it is going to be solved later than just datetime support in D7. Anyway having support for proper dates in the DB opens the possibility of much more complex queries that otherwise would require a lot of data moving around. Simple things like returning all the submitted posts on Sunday aren't possible other than moving a lot of data around if you store dates in timestamps... oh yes you can... once you build up a query that convert the timestamp to a native data type and... but well that's not going to make your query faster and still you'll have to deal with conversion somewhere... So better that DB know the nature of the data it is storing: it helps building up where clauses and it helps maintaining consistency and quality of stored data. Otherwise we would be storing everything in a BLOB or use the filesystem and grep. -- Ivan Sergio Borgonovo http://www.webthatworks.it