Take the 2-minute tour ×
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's 100% free, no registration required.

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?

share|improve this question
1  
This is answered quite well over at SO: stackoverflow.com/q/2487543/514119 –  stanleykylee Oct 21 '11 at 22:12
    
@stanleykylee thanks for pointing that out. The question was different, but the issue came up in comments and was subsequently answered along the lines of 'pick a reference date and use a numeric field', with the caveat 'it'd be tedious to code, but quite straight-forward' and my question asks, among other things, if this is the best alternative. –  David Oct 21 '11 at 22:18
    
Ah, I somehow missed that in the question. Allow me to facepalm and remove these comments ... –  jcolebrand Oct 22 '11 at 1:04

2 Answers 2

up vote 5 down vote accepted

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.

share|improve this answer
    
I'd like to give this a +1, but I'm pretty sure date logic would be horrible right? –  Camilo Martin Jan 20 '13 at 20:48

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.

share|improve this answer
1  
If you are using SQL Server 2008+, DATETIME2 is the answer. Date range: January 1, 1 AD through December 31, 9999 AD. –  Nick Chammas Oct 21 '11 at 22:34
    
Good suggestion. For other systems I would additionally recommend a CHECK constraint to enforce a date string format. Alas, MySQL does not enforce CHECK constraints. –  Nick Chammas Oct 21 '11 at 22:49
2  
@Nick, agreed about DATETIME2 in SQL Server 2008+, (I am going to miss 1753) but OP is starting with MySQL as his stake in the ground. Also, even DATETIME2 falls down as soon as you need to add December 31, 1 BC to your calendar :) –  Joel Brown Oct 21 '11 at 22:55
    
MySql is an alien world for me so I'm wondering why a string would be preferable to a number type? –  Mark Storey-Smith Oct 21 '11 at 23:13
1  
You could always add a 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... –  Nick Chammas Oct 21 '11 at 23:13

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.