A timezone should not be stored within the same field as a time. It should be stored separately and used to calculate local times, as necessary. The entire idea of storing a timestamp as a local time alongside its timezone is bad on so many levels. Oh, and good luck dealing with your DB server sometimes having a different timezone than your web server. -----Original Message----- From: Ivan Sergio Borgonovo <mail@webthatworks.it> Date: Wed, 2 Jul 2008 14:52:28 To: <development@drupal.org> Subject: Re: [development] no date / time types in schema API On Wed, 2 Jul 2008 12:36:15 +0200 Damien <damz@prealable.org> wrote:
On Wed, Jul 2, 2008 at 12:18 PM, augustin (beginner) < drupal.beginner@wechange.org> wrote:
http://en.wikibooks.org/wiki/SQL_dialects_reference/Data_structure_definitio... 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