NYCPHP Meetup

[John Lacey]

David Sklar wrote:

>> Any "good" ways to handle BCE/CE (BC/AD) dates that are stored in 
>> MySQL?  A quick check didn't come up with anything in either Paul 
>> DuBois's MySQL Cookbook or David and Adam's PHP Cookbook.
> 
> 
> How "good" the way is will depend a lot on what kind of operations you 
> need to do with the data. The MySQL DATETIME and DATE types go from 
> 1/1/1000 CE to 12/31/9999 CE, so depending on how far back you want to 
> go, you could store things as a DATETIME with a year offset, so if 
> you've got "5/12/500 (BCE)", you store that as 5/12/2500, adding or 
> subtracting 3000 years as you put it into or take it out of the database.
> 
> However, any comparative date math will be error-prone, since calendars 
> changed so much over time in different places. "May 12" doesn't really 
> have much meaning in 500 BCE anywhere in the world.
> 
> You could also use a BIGINT UNSIGNED column and store timestamps as 
> TAI64 labels (see http://cr.yp.to/proto/utctai.html and 
> http://cr.yp.to/libtai.html and http://cr.yp.to/libtai/tai64.html). This 
> gets you a culturally-unbiased measurement scale (or, to be more 
> accurate, a measurement scale that is more-or-less equally biased 
> towards each of humanity's many time measurement plans) but you will 
> still have to do a lot of conversion between TAI64 labels and whatever 
> human-readable format is appropriate for your app.
> 

thanks, that helps a lot David... I began thinking about things 
like dates before Gregorian 1582, etc and thought I'd ask.

John