Year 2038 Bug: What is it? How to solve it?

Question

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?

Disunion

That is still 28 years to go. Are you still using any computer related technology from 1982? Unlikely. So don't worry, because by 2038 it likely won't be an issue anymore. – Characterize

Gordon, I make an application that does forecasting. I save how much money I have each month, and can then estimate when I will be a millionaire. In my case, 28 years is not that much, and I'm sure I'm not the only one who have this problem right now (I solved it by using 64-bit numbers to represent the timestamp). – Alvarado

@Emil By using 64bit integers right now, you have found yourself an easy solution to a concrete problem. Not applicable to (or needed by) everyone, but working for your usecase. Point is, if the OP does not have a concrete problem, like forecasting, then this is might be an interesting topic, but nothing he should worry about, because solving this on a general level is not a PHP (mind the tag) issue. Just my 2c. – Characterize

@Emil: May I ask you, how exactly you used '64-bit numbers' to represent the timestamp ? What change would one need to do to their present table structure to make it so? Or what would they need to do, if they are creating the TIMESTAMP for the first time? Thanks. – Disunion

By 2038, parsing the string "YYYY-MM-DD HH:MM:SS:mmmm..." will be the cheapest operation you can dream of. By 2038, 32 Bit will be obsolete. I doubt the Unix timestamp as we know it will exist then, and if it does, our 256 Bit systems will handle dates that reach far beyond the age where 4096 bit systems are given out in happy meals. – Belaud

Gordon, its 9 years later now. TIMESTAMPS are still used. We still use technology from 28 years ago. Its called the world wide web. – Peristalsis

Answer 1

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

---

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 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
• Upgrade your Mysql to 8.0.28 or higher

---

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.

---

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

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.

---

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

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

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`

---

Resources

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

Chromaticity

Awesome. To me, your answer is the most complete. Thanks much. – Disunion

In MySQL, if future version changes the underlying storage data type of TIMESTAMP to 64-bit, then there is no need to change your code to DATETIME, right? I just don't think discouraging anyone from using TIMESTAMP is the right thing to do because that data type does have its purpose. – Hessney

MySQL's signed BIGINT field seems like it would work fine for storing timestamps and I've done some local testing on MySQL 5.5 which confirms it works. Obviously using signed would be better than unsigned as you can represent dates in the past as well. Any reason not to use BIGINT for timestamps? – Bridewell

One thing to note, MySQL only has auto setting to current date/time (CURRENT_TIMESTAMP) for timestamp fields. Hopefully, this functionality will eventually port to all date types. – Bowers

Just unsigned your Int values, it'll add 68 more years :-) And then the problem will be in 2106 – Ripping

2 of your resource links appear to now be broken! – Dewees

It is absolutely absurd that MySQL (and MariaDB) don't use 64 bit integers to store timestamps on 64 bit systems. Using DATETIME is not an adequate solution because we have no idea about time zone. This was reported way back in 2005, but still no fix is available. – Cristycriswell

@Dewees Fixed one, removed the other. Feel free to add some more relevant links if you find any. – Cristycriswell

I use datetime and I still get this bug – Astri

I used DateTime and still have the bug my version is 5.6 maria and ubuntu and 5.6.log all same issue on live and develop and local !!!! how to fix please any updates – Engulf

I take issue with "Few PHP applications will still be around in 2038"... which sounds like an unfounded opinion. Not to mention this answer will likely still be on the web in 5 years... 10 years... maybe 15 years from now – Dorison

I just don't understand what is so complicated about converting the timestamp type to a 64 bit integer in an update. And have a script or config option that will automatically go through an existing database and convert the datatype to a 64 bit. – Proline

From what I can tell, this is still an issue for MySQL, but not PHP on 64-bit systems. In PHP, date("Y-m-d H:i:s",time()[phone]); results in a valid date of 2338-08-09 07:10:00. But in MySQL trying to go past 2038 does throw a warning and the date gets pushed back to "0000-00-00 00:00:00" which is quite a pain. Since timestamps are mostly used for keeping track of create/modified stamps, this isn't an immediate issue. I imagine it will get fixed long before it is a real problem. (fingers crossed for luck.) – Matrilateral

According to that bug bug you linked to, it's fixed in MySQL version 8.0.28. – Eer

You can convert the column from TIMESTAMP to DATETIME. See #24721200 . Can be it's version dependent, ymmv. – Conditioning

from the mysql8 document linked The behavior of the TIMESTAMP type is also unaffected by this change; its maximum allowed value remains '2038-01-19 – Hinds

Answer 2

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

Burrell

+1 For the info. The attempt here is to adapt best practices right from start so that one does not have to worry about the problem later on. So although for now 2038 may not sound like an issue, I just want to follow the best practices and follow it for each & every application that I create, right from the start. Hope that makes sense. – Disunion

Yes, it makes sense, I see your point. But "best practice" can also mean "what answers the need" -- If you know a timestamp will be enough, there is no need to use a datetime (those need more memory to be stored, for instance ; and that can matter if you have millions of records) ;; I have some applications in which I use timestamp for some columns (columns that contain current date, for instance), and datetime for some others (columns that contain past/future dates, for instance) – Burrell

I see your procedure also makes sense and works well especially when concerning storage space. If it's alright, may I ask you what structure and length you use generally for the TIMESTAMP column in your applications? Thanks. – Disunion

Well, when I want to use a TIMESTAMP, it's for/because my "date/time" data fits between 1970 and 2038 -- and, so, I use the MySQL TIMESTAMP data type. – Burrell

Thanks for the info. From your response, I understand that it's enough to just declare the column as TIMESTAMP and we need not provide any length for it(unlike and int or var, where we provide the length). Am I right? – Disunion

@Disunion : you are right : a TIMESTAMP, with MySQL, is always stored using 4 bytes -- the 32 bits of an UNIX Timestamp – Burrell

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

Newsmagazine

Didn't answer the question which was specifically about to handle the PHP / MySQL problem. – Selectee

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

Plug

Your points 4 & 5 remind me of 'Call by Reference'. That put a smile on my face. Thanks & +1 for the same. – Disunion

Answer 5

Start from MySQL 8.0.28 the functions FROM_UNIXTIME(), UNIX_TIMESTAMP(), and CONVERT_TZ() now handle 64-bit values on platforms that support them, including 64-bit versions of Linux, MacOS, and Windows.

https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-28.html#mysqld-8-0-28-feature

Radioluminescence

Answer 6

I was asking myself thses questions recently and want to share the solution I landed on for new projects.

Bigint

After reading variouse responses to questions like this one I found that storing a Unix timestamp in a Bigint column is a much better solution moving forward.

Bigints range will cover you from before the begining of time till the year 292277026596 wich may as well be called forever.

Pluss:

• It uses the same 8bytes of storage that DATETIME uses.
• Its timezone agnostic.
• You can still use auto generated timestamps via DEFAULT (unix_timestamp())
• range is so massive your server will turn into dust before wraparounds can happen, even if storing time in miliseconds.

DECIMAL

This is the solution I landed on as you get a bit more controll. You can store an overkill date range like bigint or reduce it to something realistic and use less storage.

In my case I also want to store frations of a second as actual fractions, and I still wanted the timestamps gerated on insert.

Heres the column definition from my create table schema:

`created` decimal(18,2) NOT NULL DEFAULT (unix_timestamp(now(2))) COMMENT 'unix timestamp'

Using decimal(18,2) provides an absolutly overkill time range with the same storage as bigint/datetime while showing fractions of a second down to 2 digits.

Storage is based on the number of digits, 9 digits = 4bytes signed or unsigned it doesnt matter.

You could limit the range to something more realistic and use significantly less than datetime or increase the precision to nanoseconds. You decide whats important.

Another advantage is, if in the distant future you hit the limit of your range, Mysql will just tell you. No wrap around issue will happen, instead you will get an error preventing the insertion and can easily alter the table again to add another digit.

This makes for a perfect sense to me I highly recommend starting new databases with this appraoch.

Hinds

Answer 7

As I did't want to upgrade anything, I asked my backend (MSSQL) to do this job instead of 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.

Gusti

Please share more details. How does the code you've shared resolve the problem? – Temper