[development] no date / time types in schema API

[Darrel O'Pry]

On Wed, Jul 2, 2008 at 8:47 AM, Karen Stevenson <karen at 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.