Thread: Working with dates before 4713 BC

<div class="Section1"><p class="MsoNormal">Hi all,<p class="MsoNormal"> <p class="MsoNormal">I am building an
applicationwhich requires the storage of dates relating to the creation of archaeological items. The problem I am
facingis that although most of the dates are working fine, some of the items were created before the beginning of
recordedhistory (4713 BC). <p class="MsoNormal"> <p class="MsoNormal">Does anyone have any suggestions for making these
datesplay nicely with items created more recently (January 10<sup>th</sup>, 1968 for example)? My only other
requirementis that I need to be able to execute SQL queries which can find items made before a certain date, after a
certaindate, or between two dates.<p class="MsoNormal"> <p class="MsoNormal">Cheers,<p class="MsoNormal">Ryan</div> 

Ryan Wallace <rywall@interchange.ubc.ca> writes:
> I am building an application which requires the storage of dates relating to
> the creation of archaeological items. The problem I am facing is that
> although most of the dates are working fine, some of the items were created
> before the beginning of recorded history (4713 BC). 

The existing date/timestamp code has no chance of dealing with that,
I'm afraid --- I doubt the code is likely to work for negative Julian
dates.  Its underlying assumptions are pretty much fantasy anyway,
that far back.  Nobody was using the Gregorian calendar then ...

Do you really need microsecond, or even day, resolution in your dates?
I wonder if it'd not be good enough to store the year as an integer.
        regards, tom lane

> Do you really need microsecond, or even day, resolution in your dates? I
wonder if it'd not be good enough to store the year as an integer.

Maybe 2 columns:
date_col type date = For dates post 4713BC. Set to NULL if ealier than
4713BC.
year_col type integer = Store the year from date_col

Then write your queries appropriately to look at year_col if date_col IS
NULL?


THINK BEFORE YOU PRINT - Save paper if you don't really need to print this

*******************Confidentiality and Privilege Notice*******************

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments
e-mail.