[development] no date / time types in schema API

[Ivan Sergio Borgonovo]

On Wed, 2 Jul 2008 12:36:15 +0200
Damien <damz at prealable.org> wrote:

> On Wed, Jul 2, 2008 at 12:18 PM, augustin (beginner) <
> drupal.beginner at wechange.org> wrote:

> > http://en.wikibooks.org/wiki/SQL_dialects_reference/Data_structure_definition/Data_types/Date_and_time_types
> > All (but one) SQL dialects have a date type.
> > However, Drupal's Schema API does not seem to have a type for
> > date only: http://drupal.org/node/159605

> As I said in #252916 [1], I think DATE / TIME and DATETIME types
> are really things of the past. I understand how they could have
> been useful in the 80s and 90s, especially on text-based terminals
> (they are fixed-length!), but nowadays they are more a nuisance
> than anything. Database engines have their own implementation

Most decent DB let you modify the default format or pass specific
parameters to adjust the input/output format and php handles formats
pretty well too.
If altering the default date format is not an option, it shouldn't
be since none of the DB I know force you to set it on an instance
basis, conversion can be made at substitution time in db_query or
inside a new wrapper that will support prepared statements.

PHP 5 has a DateTime object that seems to support timezones pretty
well.

> specificities: for example, the Oracle implementation expects the
> order of arguments to be locale dependant!

locale is a pretty complicated subject.

It would be nice if it was thread-safe in php:
http://it.php.net/setlocale
The locale information is maintained per process, not per thread. If
you are running PHP on a multithreaded server api like IIS or Apache
on Windows you may experience sudden changes of locale settings
while a script is running although the script itself never called
setlocale() itself. This happens due to other scripts running in
different threads of the same process at the same time changing the
processwide locale using setlocale().

Having a thread-safe locale would solve a lot of problems for i18n
since implementing a reasonably complete and flexible data
localisation is complicated and resource hungry in php.

But that doesn't look as an Oracle DB problem since you can alter
the default date/time format at many levels (DB instance, connection,
statement).
PostgreSQL can do the same with "set datestyle" or DB wide with
postmaster -e.
I forgot how to achieve this with MS SQL, but it can be done.
I bet someone more versed than me with MySQL know the cetacean
equivalent.

> A solution based on timestamps is much more elegant: timestamps
> need no (database-specific) parsing whatsoever (both on reading
> and writing to the database), they play nice with arithmetic (and
> thus do not need any helper functions), and are consistent with
> the implementation of most application, including Drupal.

timestamps don't have even a rough idea about what a calendar or a
timezone are.
Most reasonable DB have it and this knowledge can be exploited to
large extents without the need to re-implement it in php or force
php to work as if it was sql and/or moving unnecessarily data around.

eg. filter all events happening on Sunday between 11:00am and 1:00pm
GMT or local time.

Considering timestamps and dates interchangeable is risky at least.

augustin, I hope this can help even if it just move the problem
elsewhere: most DB also support a form of extract (that
"surprisingly" is part of SQL standard).

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html
http://www.postgresql.org/docs/8.1/static/functions-datetime.html

-- 
Ivan Sergio Borgonovo
http://www.webthatworks.it