NYCPHP Meetup

[David Sklar]

> 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.

David