[development] Do not let postgresql hold back great patches

[Karen Stevenson]

The question of how to handle dates isn't something we're going to 'solve' in a few
emails back and forth on this email list and I figure maybe we should finish
this thread up or move it elsewhere.


The PHP5 function variant_date_from_timestamp seems to be something designed to work on Windows, I've not seen that used in any other situations. It just converts raw dates to and from timestamps, with the timezone adjustment unknown or ambiguous, part of the problem we have when storing dates as timestamps now. 

This whole issue is quite complex, even if we standardize on storing
all dates in the database using native date formats, the SQL to
manipulate the dates is still completely different from one database to
another. And we need better ways to associate timezones with the stored
dates, for dates that should be associated with specific timezones, so you know how to transform them when they're displayed.


For date SQL that works across databases, a simple regex find and
replace
won't work because the code needed to extract date values from datetime
columns can be completely different from one
database to another (like the completely different ways you extract a
week number from a datetime value in MYSQL and Postgres). My solution,
for now,
is to create a function that composes SQL date snippets from specified
criteria. That has been enough for me to be able to do
things like get Views queries correctly constructed in both MYSQL and
Postgres. It's not a perfect solution, but it's much simpler than a
complete abstraction layer.





And even if we start storing all core date fields in native datetime format, we still need to have ways to handle dates in non-native formats
if we want to be able to do things like import iCal or RSS feeds or
otherwise use date values that originate elsewhere.


Once I get the new Date API working (hopefully pretty soon), I'm it can be used as a focus for further work and discussion about how to handle dates in core going forward.

If nothing else, maybe we can set up a time at the next DrupalCon for a meeting of anyone interested in this subject to hammer out ideas.

Karen


----- Original Message ----
From: Earnie Boyd <earnie at users.sourceforge.net>
To: development at drupal.org
Sent: Tuesday, December 4, 2007 8:52:09 AM
Subject: Re: [development] Do not let postgresql hold back great patches

Isn't it preferred to use the ansi-sql92 TIMESTAMP column and allow the
 
DB engine to do TRT with it?  Then we can use PHP5 functions 
variant_date_from_timestamp and variant_date_to_timestamp to convert
 it.







-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.drupal.org/pipermail/development/attachments/20071204/d2546259/attachment.htm