8

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?

  • 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 LeBauer 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
8

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.

  • 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
  • How about having single field where we store dates in numeric format for e.g. 2015-10-12 10:12:05 – atpatil11 Oct 19 '16 at 7:13
7

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.

  • 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
  • 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. – 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
  • 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
  • 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 '11 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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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