Thanks for reading.  A few disclaimers:

1.  I am a newbie.  I program for a living, but my work in pg has so far 
been at the "devoted hobby level," using pg and PHP.  For an example of 
what I have done with pg, you can visit www.the-athenaeum.org, a site I 
one day hope to make into a business.

2.  I've searched the boards, but can't find a good solution to my 
problem.  I realize that there may be better ways to solve my issues 
than expanding pg's feature set, or there may be features I'm not 
familiar with.  This message is partly to find out how I should approach 
my problem.

3.  I know you are all busy, and there are more pressing issues.  I am 
extremely grateful for any advice you can give me, and will be ecstatic 
if I can get a solution out of this.

So, on to my issue.

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.

Thanks in advance,
Chris McCormick


Thu, 13 Jun 2002 11:39:55 -0400
"Chris McCormick" <cmccormick@thestate.com>
FEATURE REQUEST - More dynamic date type?
On Thu, Jun 13, 2002 at 11:39:55 -0400,
  Chris McCormick <cmccormick@thestate.com> wrote:
> Thanks for reading.  A few disclaimers:
> 
> 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!  

Is there really a standard for how long individual months were in 100000BC!
Can't you use Julian dates for this? It is well defined (though conversion
to normal dates may not be that far back) and should be easy to work with.
(There may be problems if you go back so far that you need to worry about
days not really being 24 hours long.)

Fri, 14 Jun 2002 16:45:39 -0500
Bruno Wolff III <bruno@wolff.to>
Re: FEATURE REQUEST - More dynamic date type?
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
15 Jun 2002 04:52:35 +0100
Oliver Elphick <olly@lfix.co.uk>
Re: FEATURE REQUEST - More dynamic date type?