How to store very old dates in database?

Question

It's not actually a problem I'm having, but imagine someone's building a website about the medieval times and wants to store dates, how would they go about it?

The spec for MySQLs DATE says it won't go below the year 1000. Which makes sense when the format is YYYY-MM-DD. How can you store information about the death of Kenneth II of Scotland in 995? Of course you can store it as a string, but are there real date-type options?

Answer 1

Actually, you can store dates below year 1000 in MySQL despite even documentation clarification:

mysql> describe test;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
| birth | date    | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+

-you still need to input year in YYYY format:

mysql> insert into test values (1, '0995-03-05');
Query OK, 1 row affected (0.02 sec)

mysql> select * from test;
+------+------------+
| id   | birth      |
+------+------------+
|    1 | 0995-03-05 |
+------+------------+
1 row in set (0.00 sec)

-and you'll be able to operate with this as a date:

mysql> select birth + interval 5 day from test;                                                                              
+------------------------+                                                                                                   
| birth + interval 5 day |                                                                                                   
+------------------------+                                                                                                   
| 0995-03-10             |
+------------------------+
1 row in set (0.03 sec)

As for safety. I've never faced a case when this will not work in MySQL 5.x (that, of cause, does not mean that it will 100% work, but at least it is reliable with certain probability)

About BC dates (below Christ). I think that is simple - in MySQL there's no way to store negative dates as well. I.e. you will need to store year separately as a signed integer field:

mysql> select '0001-05-04' - interval 1 year as above_bc, '0001-05-04' - interval 2 year as below_bc;
+------------+----------+
| above_bc   | below_bc |
+------------+----------+
| 0000-05-04 | NULL     |
+------------+----------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+--------------------------------------------+
| Level   | Code | Message                                    |
+---------+------+--------------------------------------------+
| Warning | 1441 | Datetime function: datetime field overflow |
+---------+------+--------------------------------------------+
1 row in set (0.00 sec)

But I think, in any case (below/above year 0) it's better to store date parts as integers in that case - this will not rely to undocumented feature. However, you will need to operate with those 3 fields not as the dates (so, in some sense that is not a solution to your problem)

Answer 2

Choose a dbms that supports what you want to do. Among other free database management systems, PostgreSQL supports a timestamp range from 4713 BC to 294276 AD.

If you break up the date into separate columns for year, month, and day, you also need more tables and constraints to guarantee that values in those columns represent actual dates. If those columns let you store the value {2013, 2, 29}, your table is broken. A dbms that supports dates in your range entirely avoids this kind of problem.

Other problems you might run into

• Incorrect date arithmetic on dates that are out of range.
• Incorrect locale-specific formatting on dates that are out of range.
• Surprising behavior from date and time functions on dates that are out of range.
• Gregorian calendar weirdness.

Gregorian calendar weirdness? In Great Britain, the day after Sep 2, 1752 is Sep 14, 1752. PostgreSQL documents their rationale for ignoring that as follows.

PostgreSQL uses Julian dates for all date/time calculations. This has the useful property of correctly calculating dates from 4713 BC to far into the future, using the assumption that the length of the year is 365.2425 days.

Date conventions before the 19th century make for interesting reading, but are not consistent enough to warrant coding into a date/time handler.