[development] no date / time types in schema API

[Ivan Sergio Borgonovo]

On Wed, 2 Jul 2008 10:21:29 -0500 (CDT)
David Timothy Strauss <david at fourkitchens.com> wrote:

> ----- "Ivan Sergio Borgonovo" <mail at 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