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

Question

Hi all, 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. 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?

Thanks in advance.

Answer 1

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

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

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

The DATETIME type 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 TIMESTAMP data type has a range of '1970-01-01 00:00:01' UTC to '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 DATETIME for the columns that have to contain dates that are not between 1970 and 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...

Answer 2

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

What exactly is the Year 2038 problem?

"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."

---

Why does it occur and what happens when it occurs?

"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." This is due to the fact the number of seconds since the UNIX epoch (January 1 1970 00:00:00 GMT) will have exceeded a 32 bit computers maximum number of characters for a 32 bit machine unsigned int.

---

How do we solve it?

• Use long data types (64 bits is sufficient)
• For MySQL, store dates as DATETIME rather than TIMESTAMP
• Other Possible solutions
• Other Possible solutions described on Wikipedia

---

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 an unsigned long using sprintf in PHP.

---

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

Most real world PHP applications will not still be around when 2038 rolls around as the web is not really a legacy platform.

The process for altering your database table columns to convert TIMESTAMP to DATETIME involves creating a temporary field:

# 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

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

Answer 3

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

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

Answer 4

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

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!

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!

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)

4) See 3!

5) See 4!!! ;)

Answer 5

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

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

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