Обсуждение: FEATURE REQUEST - More dynamic date type?
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 recordsin 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
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.)
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