2

I'm working on a project now that will require me to enable users to store negative-dates in the database. These dates have the potential to span many thousands of years BC, and as late as 'Today.' I've worked on many projects that stored dates, but they were all relatively-recent dates, and none of them were ever negative (BC).

What types of things should I take into considering when working with this type of data? I'm fully aware of the many calendar-shifts that have taken place in history, but I will be working with the modern calendar and not worrying too much about doing conversions. However, any information about conversions would be greatly appreciated as it is a very relevant topic, and one that I am academically interested in although not required to focus on during this project.

Update: I'll be storing YYYY-MM-DD (That format isn't required).

HoldOffHunger
  • 18,769
  • 10
  • 104
  • 133
Sampson
  • 265,109
  • 74
  • 539
  • 565

3 Answers3

2

How precise does your storage need to be? Is it years, days, or hours/minutes. If you are only looking at Days or Years, then maybe you don't need to actually store the Date, but rather an Offset of +/- Day/Years from your 0 Date

Paul Farry
  • 4,730
  • 2
  • 35
  • 61
0

I had the similar problem and I wanted to continue relay on date fields in the DB to allow me use date range search with accuracy of up-to a day for historic values. (My DB includes date of birth and dates of roman emperors...)

The solution was to add a constant year (example: 3000) to all the dates before adding them to the DB and subtracting the same number before displaying the query results to the users.

If you DB has already some dates value in it, remember to update the exiting value with the new const number.

Yochai
  • 2,783
  • 2
  • 12
  • 8
  • 2
    Exact duplicate of [your answer](http://stackoverflow.com/questions/2487543/does-mysql-support-historical-date-like-1200/22089006#22089006), stop posting duplicate answers and there by not creating redundancy. – Paresh Mayani Feb 28 '14 at 16:04
0

This Answer Should Cover all of Written/Recorded, Human History

The MySQL acceptable range for dates is from 1000-01-01 to 9999-12-31. (Source: Dev.MySQL.com: 11.2.2 The DATE, DATETIME, and TIMESTAMP Types.) So, in fact, the problem doesn't begin with negative or BCE dates, but with all dates preceding the year 1000, for instance, the birth of Trajan, 53 AD, etc..

So, let's hijack some dates! We are going to treat the year 3000 as the year 1000. I know this has been suggested by others, but where's the code and the demonstration? That's what I want to fill in.

When subtracting/adding years to hijack date ranges, we need to handle some odd edge cases. For instance, both BC and AD (up to 9999 AD) are broken, but these two date ranges work differently. What kind of logic can do a successful remap with both of the follow requirements?

  • In AD, we say, "This year is x, next year is x + 1."
  • In BC, we say, "This year is x, next year is x - 1."

Anyway, let's code!

Convert From Any DB Value to Date

Birth of Laozi, formally "571 BCE", technically, "-571 AD". It stores as 4571.

year = 4571;
if(year >= 3000) {
  diff = year - 3000;
  real_year_number = 1000 - diff;
  console.log(real_year_number);
}

Convert From Any Date to DB Value

So, once again, birth of Laozi, "571 BCE". It is treating as -571.

year = -571;

if(year < 1000) {
    diff = ((year - 1000) * (-1)) + 3000;
  console.log(diff);
}

Addendum -- About Dashes

If you're playing with this stuff, you'll soon realize that many date format libraries require only two dashes "year-month-day", etc., and using a negative, "-year-month-day", can just wreak havok. Personally, I have rewritten the above to have "BCE" prepended to bce dates, i.e., you'll have values like "bce571-01-01" instead of "-571-01-01", etc..

HoldOffHunger
  • 18,769
  • 10
  • 104
  • 133