[libdb-develop] =?UTF-8?B?W2ZlYXR1cmVdIERhdGUgUmFuZ2VzIGFuZCBEYXRhYmFzZSBNYW5pcHVsYXRp?= =?UTF-8?B?b25z?=

[<mo...@di...>]

 Project:      LibDB
 Version:      cvs
 Component:    Code
 Category:     feature requests
 Priority:     normal
 Assigned to:  Anonymous
 Reported by:  Morbus Iff
 Updated by:   Morbus Iff
 Status:       active

There are a few qualms I have with the current data model's date system,
and it's all due to implementation, not necessarily bad intent. The
intent is innocent: dates should be in the form of YYYY, YYYY-MM, or
YYYY-MM-DD. The goal here is to say that a "movie came out in 1997" or,
if you know the exact date of theatrical release, "1997-06-02". Same
sorta thing with birthdays: you may approximately know that someone was
born in "1817" or even in "1817-04", but not the exact moment in time.

The almost immediate problem with the above formats, however, is the
lack of support for "A.D. 143" or "B.C. 23" (though I hesitate to label
that a critical issue.) Technically, "A.D. 143" could be referenced as
just "143" (YYY, not YYYY), but I'm not sure the underlying database
will support that early of a year - regardless, MySQL would convert a
date of "AD 43" ("43") to "1943", which isn't what we want. We'd have
to zeropad non-YYYY dates ("0143" and "0043"). This doesn't help us
with B.C. dates at all. See also
http://dev.mysql.com/doc/mysql/en/DATETIME.html: "The supported range
is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. (``Supported'' means
that although earlier values might work, there is no guarantee that they
will.)"

Irregardless of how early a date is, our current data model expects to
receive YYYY-MM-DD and ONLY YYYY-MM-DD - there's no capability for just
YYYY or YYYY-MM. After some discussion on a #mysql IRC channel, it
appears that the only way to handle ranges (where "1997" is a range of
["1997-01-01" to "1997-12-31"] and "1997-04" is ["1997-04-01" to
"1997-04-31"]) is to have two database fields: date_start and date_end.
It'd then be necessary in our code to create the ranges ourselves (from
a user-entered "1997"). This is the only way to do the "right" thing,
with proper database columns, and use of WHERE date/time clauses (WHERE
date_start > '2004-04-25' for example).

* Is this the "right" thing to do?
* What about fictional dates ("Shiresmoon 23")?

Affected tables: libdb_work libdb_expression libdb_manifestation
libdb_person libdb_corporate_body libdb_events. 

Morbus Iff

-- 
View: http://drupal.org/node/view/10160
Edit: http://drupal.org/project/comments/add/10160