9

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?

3
  • 1
    This is answered quite well over at SO: stackoverflow.com/q/2487543/514119 Oct 21, 2011 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. Oct 21, 2011 at 22:18
  • Ah, I somehow missed that in the question. Allow me to facepalm and remove these comments ...
    – jcolebrand
    Oct 22, 2011 at 1:04

3 Answers 3

7

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.

2
  • 1
    I'd like to give this a +1, but I'm pretty sure date logic would be horrible right? Jan 20, 2013 at 20:48
  • How about having single field where we store dates in numeric format for e.g. 2015-10-12 10:12:05
    – atpatil11
    Oct 19, 2016 at 7:13
9

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.

8
  • 1
    If you are using SQL Server 2008+, DATETIME2 is the answer. Date range: January 1, 1 AD through December 31, 9999 AD. Oct 21, 2011 at 22:34
  • 1
    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. Oct 21, 2011 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, 2011 at 22:55
  • 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... Oct 21, 2011 at 23:13
  • 1
    Mark, the reason I'd recommend a string is that, as long as you formulate it properly, it will be human readable (and even to some extent editable) without any processing and you can construct special date manipulation functions with relative ease. Storing dates as numbers is terribly, terribly inconvenient, unless you have a library of functions that can interpret that kind of encoding for mere humans. Of course, when such a library is in place, encoding a date as a number is vastly more sensible.
    – Joel Brown
    Oct 21, 2011 at 23:30
1

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.

2
  • 1
    negative numbers for BC? Feb 2, 2017 at 14:20
  • 1
    151012 and 150819 would both be in 15AD, -151109 would be in 15BC Feb 2, 2017 at 14:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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