Re: FEATURE REQUEST - More dynamic date type? - Mailing list pgsql-hackers

On Thu, 2002-06-13 at 16:39, Chris McCormick wrote:
...
> THE BACKGROUND - I am creating a web site where people can study the
> humanities.  They can upload, discuss, and peer-review information.
>  They can also create, edit, approve, and delete records in a postgresql
> db, using web forms.  Many of these forms need a way to enter historical
> dates - a person DOB, the date an empire was founded, the date a book
> was published, etc.
>
> MY PROBLEM - Because this site deals with, among other things, ancient
> art, acheaology, and anthropology, I need a way to handle dates as
> specific as a single day, and as far back as 100,000 BC.  According to
> the docs (I looked at
> http://www.postgresql.org/idocs/index.php?datatype-datetime.html), the
> farthest back any date type reaches is 4713 BC.  So far, I have tried to
> deal with this problem by creating a numeric field for the year, and
> radio buttons for AD/BC.  I then do a lot of form validation.  Not only
> that, if I want to be as specific as a month or a day, then those are
> separate fields on my forms.  Plus, I can't combine all of the fields
> and put them into a pg data type, because once again, they don't extend
> that far back.  So, I have to maintain and validate the year, month, and
> days fields separately.  Then imagine what I have to do if a user wants
> to _sort_ by date, or select events by date range!
>
> Ideally, I would like to figure this out on two fronts.  I'd like to
> find out what's the best way to store dates that far back (with pg), and
> then on the PHP end I'll have to figure out how to parse entry so that
> it is as simple as possible for the end user.  Knowing how to store
> these ancient dates in pg would help me a great deal.
>
> There are a lot of university and hobby sites out there working on
> digitizing collections of ancient texts, artifacts, etc.  I don't know
> how the date range is chosen for a type like timestamp (4713BC -
> 1,465,001 AD), but it seems to me that there would be way more people
> working on recording the past (and thereby needed a date range that
> extends into ancient civilization) than working with dates in the far
> future (more than a million years ahead???).
>
> I hope that someone will be kind enough to reply with some ideas, or
> even to take up the cause and consider a date type that could be used
> for historical purposes.  I am an avid fan of open source and pg,
> especially as compared to mySQL.  I hope to continue using pg, and build
> a first-class web site that may one day serve as a great working example
> of what pg can do.  Any help would be greatly appreciated.

I have seen an implementation to deal with this problem; it was in a
museum package developed in New Zealand which I saw about 7 years ago.
I can't now remember what it was called, but it allowed objects to be
catalogued with fuzzy dates.  (The package was written in Revelation,
which was a PICK-like database.)

I think that the solution will have to be to develop a special type.
Your fields have to hold dates that vary from very specific (4th August
1914) or quite close (1520 AD) to pretty vague (Louis Quatorze, 850-880,
ca.1230, 5th century BC) or even very vague (4th Dynasty, Paleolithic).
The good news is that PostgreSQL will let you do this, if you can devise
the algorithms; I'm not sure if there's another RDBMS that would.

Your type would need a flag byte to determine the type of date or period
(I think 256 different types of date/period might be enough, but you
could give it 2 bytes if you wanted to be sure(?) never to run out) and
a value field -- integer or long integer.  You would have to define
comparison routines for sorting, equality and inclusion or intersection
("1540" is included in "16th century", "Napoleonic" intersects "18th
century" and "19th Century").

If you like this idea, I might be interested in developing it, in my
infrequent moments of spare time...

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
    "Cease from anger, and forsake wrath; do not fret-       it leads only to evil."        Psalms 37:8