On Wed, Jul 2, 2008 at 8:47 AM, Karen Stevenson <karen@elderweb.com> wrote:

...

...

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

You obviously have never dealt with timestamps for dates...

They are a most royal PITA, especially when dealing with timezones and DST.

Couldn't have said it better myself. The two people on this list who have probably spent more time than anyone else in Drupal trying to do things like proper timezone conversions on timestamps are in complete agreement that timestamps are a PITA.

They also don't work at all well for historical dates, nor for incomplete dates -- years and months with unspecified days or dates without times. They imply a level of precision to the second when that may not be at all true. They have no knowledge of timezones nor any way of dealing with timezones.

When you store a date in a database's native datetime format, the database can do timezone adjustments on the value, format the date values, and extract things like a month or year out of the date. If you store the date in a timestamp and need to do any of those things, you first have to come back and cast the timestamp as a datetime field, which is neither simple nor elegant, and works differently (and sometimes badly) in different databases.

Dates are complicated beasts. Treating them as timestamps doesn't solve the problems, it just creates different ones.

Timestamp - a date representation of some form... two possible representations are unix style integer timestamp or date-time formatted string representation. Integer timestamps really only limited in range of dates they can express. The currently signed 32bit unix timestamps will wrap on Jan 19 2038 and find themselves at some massively negative number of seconds before Jan 1 1970. We should be up to 64 bit computing by then.. which should buy the venerable unix timestamp about 292,471,208,609 more years... (warning not offseting for the slowing rotation of the early and the increasing frequency of leap seconds) since someone decided to use a signed representation historic dates can also be represented as negative values, but generally are not. I thinking covering 292 billion years forward and backward should be sufficient for most cases.... Timestamps in DateTime string notation can express any time from 0000AD to 9999AD and per RFC 3339. So it has a wider range than a 32 bit unix timestamp, and defines how to represent pre unix epoch dates. The timezone issue is still a problem for both representations of time. The simple way I've found for keeping the timezone straight is storing all time in UTC, and any time representation in database queries should be converted in UTC. The second timezone to keep track of, is the timezone of the user/server. With drupal core you can assuming a cascase of potential timezones in order of priority: user entered timezone, user profile timezone, drupal site timezone. You can use the timezone offset specified in each to convert to UTC... or convert for presentation. The important rule... keep every time representation in SQL in the same timezone... preferably UTC. For the specific case of handling dates in PHP5 the new datetime object makes it really easy to find offset by timezone and handling DST pretty simple, although the mechanics of converting to an integer timestamp are somewhat annoying becuase you do have to convert it to a date-time string representation to create the datetime object I believe.

I'm looking forward to getting them out of Drupal core.

The string representations also have their drawbacks, and at some point they all have to be converted to numeric values to process them... admittedly it is kind of easier to parse the day out of the date-time strings and add 2, rather than converting complex dates to integer representations... Then there is always the spectre of non-gregorian dates.... I don't think there is really a great need to remove basic timezone handling from Drupal Core. I think there is a great need to specify how timestamps and time representations should be handled for consistency. I think there is a proper place for both integer timestamps and string representations of time depending on performance or time period requirements, and PHP now provides tools for handling timezone and DST conversion fairly simply for both datetime timestamps and unix timestamps.