Resolved: What datatype for historical and anthropological data?
Hello, I am just curious. The given range for DATE datatype is from 1000 AD to 9999 AD, and that of TIMESTAMP is from 1970 to 2038.
What happens when we have to deal with historical and anthropological data with date values in BC and BCE which actually runs to the left of the first year AD in date & time chronology?
Is there any datatype specifically to deal with such data or we have to rely completely on arithmetic operations on the currently available datatype DATE & TIMESTAMP?
Hi Anas!
Thanks for reaching out.
The MySQL acceptable range for dates is from 1000-01-01 to 9999-12-31. So, in fact, the problem doesn't begin with negative or BC dates but with all dates preceding the year 1000. For instance we are going to treat the year 3000 as the year 1000.
- 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."
For example the year 571 BC:
year = 4571;
if(year >= 3000) {
diff = year - 3000;
real_year_number = 1000 - diff;
console.log(real_year_number);
}
Hope this helps.
Best,
Tsvetelin
Hello Mr. Tsonkov,
It is Jigar again. Thank you for your quick and detailed reply.
To be honest, I didn't understand the code especially the '3000' part and then going for 571 BC.
I am currently trying some exercises on dates in SQL.
I will reach you out again with a more specific query.
Regards,
Jigar