如何在数据库中存储非常旧的日期？ [英] How to store very old dates in database?

问题描述

这实际上并不是我遇到的问题，但是想像有人建立一个关于中世纪时代的网站，想要存储日期，他们将如何进行？

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?

MySQL的规范 DATE 表示不会超过1000年。当格式为 YYYY-MM-DD 。如何在995年存储有关苏格兰的Kenneth II的死亡的信息？当然你可以把它存储成一个字符串，但是有真正的日期类型选项吗？

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?

推荐答案

即使文档可以在MySQL之前存储1000年以下的日期， a>澄清：

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    |       |
+-------+---------+------+-----+---------+-------+

- 您仍然需要以YYYY格式输入年份：

-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)

至于安全。我从来没有遇到这样的情况，当这不能在MySQL 5.x（这是因为，并不意味着它会100％工作，但至少它是可靠的一定的概率）

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)

关于BC日期（在基督之下）。我觉得这很简单 - 在MySQL里面，没有办法来存储负数。即您将需要将年份分开存储为签名整数字段：

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)

但是我认为，无论如何（0/0以上），最好在这种情况下将日期部分存储为整数 - 这不会依赖于未记录的功能。但是，您将需要使用这3个字段作为日期（因此在某种意义上说，这不是您的问题的解决方案）。

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)

这篇关于如何在数据库中存储非常旧的日期？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！