Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
I am creating a database for records that extend prior to 1000 AD, but MySQL Date and DateTime fields only support dates starting at 1000.
Is there a way that would be more convenient than either using a bigint type to count seconds before/after 1/1/1970 using a Unix timestamp, or switching to a database software that supports larger date ranges?
An alternative is to store each part of the date in a numeric field. So you would have three fields:
year SMALLINT # Store positive values for AD and negative for BC years.
month TINYINT
day TINYINT
This way it would still be human readable. The range of values for different numeric data types in MySQL are available at Overview of Numeric Types. The storage requirements are available at Data Type Storage Requirements.
No native RDBMS date data type is going to do for applications that require very old (and for some, even distant future) dates.
If I were you, I'd use a string type for the native storage and stick with a place-significant format like: +YYYY-MM-DD to accomodate BC/AD and any foreseeable historical or reasonable future date.
If it might help, you could build a library class that converts your internal storage format into a more presentable one for the UI layer. You might even include library functions that convert to a native date type, if your language of choice supports the dates that you will have in your database.
CHECK constraint to enforce a date string format. Alas, MySQL does not enforce CHECK constraints.
Oct 21, 2011 at 22:49
BIT or BOOLEAN column to indicate date polarity. :) Of course, if you do that, you're on your own if you perform calculations on those "B.C." dates. Who knows what kind of calendar adjustments that are normally taken care of for us by library functions would be missed while manipulating the "fake" B.C. dates...
Oct 21, 2011 at 23:13
How about having a single float field in table where we store dates in numeric format for e.g. 2015-10-12 10:12:05 will get stored 20151012. 101205. Its always better to have sorting on single field instead of having 3 or more different fields.
Above logic doesn't work for few scenario. So, we have converted date into Seconds considering 1 day = 86400 seconds. Used negative for BC dates. It works as expected.
