FEATURE REQUEST - More dynamic date type?

Started by Chris McCormickover 23 years ago3 messages

cmccormick@thestate.com

over 23 years ago

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