PHP & mySQL：2038 年错误：它是什么？如何解决？

PHP & mySQL: Year 2038 Bug: What is it? How to solve it?

提问by Devner

I was thinking of using TIMESTAMP to store the date+time, but I read that there is a limitation of year 2038 on it. Instead of asking my question in bulk, I preferred to break it up into small parts so that it is easy for novice users to understand as well. So my question(s):

我正在考虑使用 TIMESTAMP 来存储日期+时间，但我读到它有 2038 年的限制。与其大量问我的问题，我更喜欢把它分解成小部分，这样新手用户也很容易理解。所以我的问题：

1. What exactly is the Year 2038 problem?
2. Why does it occur and what happens when it occurs?
3. How do we solve it?
4. Are there any possible alternatives to using it, which do not pose a similar problem?
5. What can we do to the existing applications that use TIMESTAMP, to avoid the so-called problem, when it really occurs?

1. 2038年的问题究竟是什么？
2. 为什么会发生，发生时会发生什么？
3. 我们如何解决？
4. 是否有任何可能的替代方案来使用它，不会造成类似的问题？
5. 当所谓的问题真正发生时，我们可以对现有的使用 TIMESTAMP 的应用程序做些什么来避免所谓的问题？

Thanks in advance.

提前致谢。

回答by Corey Ballou

I have marked this as a community wiki so feel free to edit at your leisure.

我已将其标记为社区维基，因此您可以在闲暇时随意编辑。

What exactly is the Year 2038 problem?

2038年的问题究竟是什么？

"The year 2038 problem (also known as Unix Millennium Bug, Y2K38 by analogy to the Y2K problem) may cause some computer software to fail before or in the year 2038. The problem affects all software and systems that store system time as a signed 32-bit integer, and interpret this number as the number of seconds since 00:00:00 UTC on January 1, 1970."

“2038 年问题（也称为 Unix Millennium Bug，Y2K38 类似于 Y2K 问题）可能会导致某些计算机软件在 2038 年之前或之后出现故障。该问题影响所有将系统时间存储为带符号 32 的软件和系统-bit 整数，并将此数字解释为自 1970 年 1 月 1 日 00:00:00 UTC 以来的秒数。”

---

---

Why does it occur and what happens when it occurs?

为什么会发生，发生时会发生什么？

Times beyond 03:14:07 UTC on Tuesday, 19 January 2038will 'wrap around' and be stored internally as a negative number, which these systems will interpret as a time in December 13, 1901 rather than in 2038. This is due to the fact that the number of seconds since the UNIX epoch (January 1 1970 00:00:00 GMT) will have exceeded a computer's maximum value for a 32-bit signed integer.

超越时代3时14分07秒UTC在星期二，2038 1月19日将“环绕”并为负数，这些系统将在1901年12月13日，解释为一个时间，而不是在2038年内部存储这是由于事实上，自 UNIX 时代（1970 年 1 月 1 日 00:00:00 GMT）以来的秒数将超过计算机的 32 位有符号整数的最大值。

---

---

How do we solve it?

我们如何解决？

• Use long data types (64 bits is sufficient)
• For MySQL (or MariaDB), if you don't need the time information consider using the DATEcolumn type. If you need higher accuracy, use DATETIMErather than TIMESTAMP. Beware that DATETIMEcolumns do not store information about the timezone, so your application will have to know which timezone was used.
• Other Possible solutions described on Wikipedia
• Wait for MySQL devs to fix this bugreported over a decade ago.

• 使用长数据类型（64 位就足够了）
• 对于 MySQL（或 MariaDB），如果您不需要时间信息，请考虑使用DATE列类型。如果您需要更高的准确度，请使用DATETIME而不是TIMESTAMP。请注意，DATETIME列不存储有关时区的信息，因此您的应用程序必须知道使用了哪个时区。
• 维基百科上描述的其他可能的解决方案
• 等待 MySQL 开发人员修复这个十多年前报告的错误。

---

---

Are there any possible alternatives to using it, which do not pose a similar problem?

是否有任何可能的替代方案来使用它，不会造成类似的问题？

Try wherever possible to use large types for storing dates in databases: 64-bits is sufficient - a long long type in GNU C and POSIX/SuS, or sprintf('%u'...)in PHP or the BCmath extension.

尽可能尝试使用大类型在数据库中存储日期：64 位就足够了 - GNU C 和 POSIX/SuS 中的 long long 类型，或者sprintf('%u'...)PHP 或 BCmath 扩展。

---

---

What are some potentially breaking use cases even though we're not yet in 2038?

即使我们还没有进入 2038 年，有哪些潜在的突破性用例？

So a MySQL DATETIMEhas a range of 1000-9999, but TIMESTAMP only has a range of 1970-2038. If your system stores birthdates, future forward dates (e.g. 30 year mortgages), or similar, you're already going to run into this bug. Again, don't use TIMESTAMP if this is going to be a problem.

所以 MySQL DATETIME的范围是 1000-9999，而 TIMESTAMP 的范围只有 1970-2038。如果您的系统存储了出生日期、未来的远期日期（例如 30 年抵押贷款）或类似信息，您就会遇到这个错误。同样，如果这会成为一个问题，请不要使用 TIMESTAMP。

---

---

What can we do to the existing applications that use TIMESTAMP, to avoid the so-called problem, when it really occurs?

当所谓的问题真正发生时，我们可以对现有的使用 TIMESTAMP 的应用程序做些什么来避免所谓的问题？

Few PHP applications will still be around in 2038, though it's hard to foresee as the web hardly a legacy platform yet.

很少有 PHP 应用程序会在 2038 年仍然存在，尽管很难预见，因为网络还不是一个遗留平台。

Here is a process for altering a database table column to convert TIMESTAMPto DATETIME. It starts with creating a temporary column:

这是更改数据库表列以转换TIMESTAMP为DATETIME. 它从创建一个临时列开始：

# rename the old TIMESTAMP field
ALTER TABLE `myTable` CHANGE `myTimestamp` `temp_myTimestamp` int(11) NOT NULL;

# create a new DATETIME column of the same name as your old column
ALTER TABLE `myTable` ADD `myTimestamp` DATETIME NOT NULL;

# update all rows by populating your new DATETIME field
UPDATE `myTable` SET `myTimestamp` = FROM_UNIXTIME(temp_myTimestamp);

# remove the temporary column
ALTER TABLE `myTable` DROP `temp_myTimestamp`

---

---

Resources

资源

• Year 2038 Problem (Wikipedia)
• The Internet Will End in 30 Years

• 2038 年问题（维基百科）
• 互联网将在 30 年后终结

回答by Pascal MARTIN

When using UNIX Timestamps to store dates, you are actually using a 32 bits integers, that keeps count of the number of seconds since 1970-01-01 ; see Unix Time

使用 UNIX 时间戳存储日期时，您实际上使用的是 32 位整数，它记录自 1970-01-01 以来的秒数；见Unix 时间

That 32 bits number will overflow in 2038. That's the 2038 problem.

那个 32 位的数字会在 2038 年溢出。这就是 2038 年的问题。

To solve that problem, you must not use a 32 bits UNIX timestamp to store your dates -- which means, when using MySQL, you should not use TIMESTAMP, but DATETIME(see 10.3.1. The DATETIME, DATE, and TIMESTAMP Types) :

为了解决这个问题，你不能使用 32 位 UNIX 时间戳来存储你的日期——这意味着，当使用 MySQL 时，你不应该使用TIMESTAMP, 但是DATETIME（参见10.3.1. DATETIME、DATE 和 TIMESTAMP 类型）：

The DATETIMEtype is used when you need values that contain both date and time information. The supported range is '1000-01-01 00:00:00'to '9999-12-31 23:59:59'.

The TIMESTAMPdata type has a range of '1970-01-01 00:00:01'UTC to '2038-01-19 03:14:07'UTC.

DATETIME当您需要包含日期和时间信息的值时使用该类型。支持的范围是'1000-01-01 00:00:00'到 '9999-12-31 23:59:59'。

该TIMESTAMP数据类型的范围是'1970-01-01 00:00:01'为UTC '2038-01-19 03:14:07'UTC。

The (probably)best thing you can do to your application to avoid/fix that problem is to not use TIMESTAMP, but DATETIMEfor the columns that have to contain dates that are not between 1970 and 2038.

为了避免/修复该问题，您可以对应用程序做 的（可能）最好的事情是不使用TIMESTAMP，但DATETIME对于必须包含不在 1970 和 2038 之间的日期的列。

One small note, though : there is a very high probably (statistically speaking)that your application will have been re-written quite a couple of times before 2038 ^^ So maybe, if you don't have to deal with dates in the future, you won't have to take care of that problem with the current version of your application...

不过，有一点要注意：很有可能（从统计上讲）您的应用程序将在 2038 年之前被重写很多次 ^^ 所以也许，如果您将来不必处理日期，您不必处理当前版本的应用程序的问题...

回答by Rubens Farias

A quick search on Google will do the trick: Year 2038 problem

在 Google 上快速搜索即可解决问题：2038 年问题

1. The year 2038 problem (also known as Unix Millennium Bug, Y2K38 by analogy to the Y2K problem) may cause some computer software to fail before or in the year 2038
2. The problem affects all software and systems that store system time as a signed 32-bit integer, and interpret this number as the number of seconds since 00:00:00 UTC on January 1, 1970. The latest time that can be represented this way is 03:14:07 UTC on Tuesday, 19 January 2038. Times beyond this moment will "wrap around" and be stored internally as a negative number, which these systems will interpret as a date in 1901 rather than 2038
3. There is no easy fix for this problem for existing CPU/OS combinations, existing file systems, or existing binary data formats

1. 2038 年问题（也称为 Unix Millennium Bug，Y2K38 类比于 Y2K 问题）可能会导致某些计算机软件在 2038 年之前或之内出现故障
2. 该问题会影响所有将系统时间存储为有符号 32 位整数的软件和系统，并将此数字解释为自 1970 年 1 月 1 日 UTC 时间 00:00:00 以来的秒数。可以用这种方式表示的最晚时间是 2038 年 1 月 19 日星期二 UTC 时间 03:14:07。超过这一时间的时间将“环绕”并在内部存储为负数，这些系统将其解释为 1901 年而不是 2038 年的日期
3. 对于现有 CPU/OS 组合、现有文件系统或现有二进制数据格式，此问题没有简单的解决方法

回答by Addsy

http://en.wikipedia.org/wiki/Year_2038_problemhas most of the details

http://en.wikipedia.org/wiki/Year_2038_problem有大部分细节

In summary:

总之：

1) + 2) The problem is that many systems store date info as a 32-bit signed int equal to the number of seconds since 1/1/1970. The latest date that can be stored like this is 03:14:07 UTC on Tuesday, 19 January 2038. When this happens the int will "wrap around" and be stored as a negative number which will be interpreted as a date in 1901. What exactly will happen then, varies from system to system but suffice to say it probably won't be good for any of them!

1) + 2) 问题是许多系统将日期信息存储为 32 位有符号整数，等于自 1970 年 1 月 1 日以来的秒数。可以像这样存储的最新日期是 2038 年 1 月 19 日星期二 UTC 时间 03:14:07。当这种情况发生时，int 将“环绕”并存储为负数，该负数将被解释为 1901 年的日期。那么究竟会发生什么，因系统而异，但可以说它可能对他们中的任何一个都没有好处！

For systems that only store dates in the past, then I guess you don't need to worry for a while! The main problem is with systems that work with dates in the future. If your system needs to work with dates 28 years in the future then you should start worrying now!

对于只存储过去日期的系统，那我猜你就不用担心了！主要问题是与未来的日期一起工作的系统。如果您的系统需要处理未来 28 年的日期，那么您现在应该开始担心了！

3) Use one of the alternative date formats available or move to a 64-bit system and use 64-bit ints. Or for databases use an alternative time stamp format (eg for MySQL use DATETIME)

3) 使用可用的替代日期格式之一或移动到 64 位系统并使用 64 位整数。或者对于数据库使用替代时间戳格式（例如对于 MySQL 使用 DATETIME）

4) See 3!

4）见3！

5) See 4!!! ;)

5）见4！！！;)

回答by Dexter

Bros, if you need to use PHP to display timestamps, this is the BEST PHP solution without changing from UNIX_TIMESTAMP format.

兄弟，如果您需要使用 PHP 来显示时间戳，这是最好的 PHP 解决方案，无需更改 UNIX_TIMESTAMP 格式。

Use a custom_date() function. Inside it, use the DateTime. Here's the DateTime solution.

使用 custom_date() 函数。在其中，使用 DateTime。这是 DateTime 解决方案。

As long as you have UNSIGNED BIGINT(8) as your timestamps in database. As long as you have PHP 5.2.0 ++

只要您将 UNSIGNED BIGINT(8) 作为数据库中的时间戳。只要你有 PHP 5.2.0 ++

回答by Venkatesh GS Rao

As I did't want to upgrade anything, I asked my backend (MSSQL) to do this job instead of PHP!

由于我不想升级任何东西，我让我的后端 (MSSQL) 代替 PHP 来完成这项工作！

$qry = "select DATEADD(month, 1, :date) next_date ";
$rs_tmp = $pdo->prepare($qry);
$rs_tmp->bindValue(":date", '2038/01/15');
$rs_tmp->execute();
$row_tmp = $rs_tmp->fetch(PDO::FETCH_ASSOC);

echo $row_tmp['next_date'];

May not be an efficient way, but it works.

可能不是一种有效的方式，但它有效。