PHP& mySQL:2038年Bug:这是什么?怎么解决呢?

问题描述

我当时正在考虑使用TIMESTAMP来存储日期和时间，但是我读到它有2038年的限制.我不想将问题大量散发，而是将其分解成小部分，以便新手用户也易于理解.所以我的问题是

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

1. 2038年的问题到底是什么?
2. 为什么会发生?何时发生?
3. 我们如何解决?
4. 是否有使用它的其他可能的选择，而不会引起类似的问题?
5. 当使用TIMESTAMP的现有应用程序真正发生时，我们该怎么办?

谢谢.

推荐答案

我已将其标记为社区Wiki，因此随时可以编辑.

"2038年的问题(也称为Unix Millennium Bug，类似于Y2K问题的Y2K38)可能会导致某些计算机软件在2038年之前或之内发生故障.该问题影响将系统时间存储为的所有软件和系统.一个有符号的32位整数，并将此数字解释为自1970年1月1日00:00:00 UTC以来的秒数."

"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年1月19日星期二 03:14:07 UTC之后的时间将环绕"并在内部存储为负数，这些系统将其解释为12月13日的时间. 1901年而不是2038年.这是由于以下事实:自UNIX时代(1970年1月1日00:00:00 GMT)以来的秒数已经超过了32位带符号整数的计算机最大值.

Times beyond 03:14:07 UTC on Tuesday, 19 January 2038 will '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.

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

• Use long data types (64 bits is sufficient)
• For MySQL (or MariaDB), if you don't need the time information consider using the DATE column type. If you need higher accuracy, use DATETIME rather than TIMESTAMP. Beware that DATETIME columns 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 bug reported over a decade ago.

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

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.

因此，MySQL DATETIME 的范围为1000- 9999，但TIMESTAMP仅具有1970-2038的范围.如果您的系统存储了出生日期，将来的远期日期(例如30年的抵押贷款)或类似的日期，那么您已经遇到了这个错误.同样，如果这将成为问题，请不要使用TIMESTAMP.

So a MySQL DATETIME has 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.

到2038年，仍然很少有PHP应用程序出现，尽管人们很难预见到Web仍不是旧版平台.

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

这是更改数据库表列以将TIMESTAMP转换为DATETIME的过程.首先创建一个临时列:

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

# 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`

---

资源

• 2038年问题(维基百科)
• 互联网将在30年后终止

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

这篇关于PHP& mySQL:2038年Bug:这是什么?怎么解决呢?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！