The Year 2038 problem (also referred to as the Y2K38 bug) refers to a problem that some computer systems might encounter when dealing with times past 2038-01-19 03:14:07.

Many computer systems, such as Unix and Unix-based systems, don’t calculate time using the Gregorian calendar. They calculate time as the number of seconds since 1 January 1970. Therefore, in these systems, time is represented as a big number (i.e. the number of seconds passed since 1970-01-01 00:00:00). This is typically referred to as Epoch time, Unix time, Unix Epoch time, or POSIX time. As I write this, Unix time is 1560913841. And as I write this next line, Unix time has incremented to 1560913879.

The 2038 problem is caused by the fact that many systems store this number as a signed 32-bit binary integer. The range of a signed 32-bit integer is -2,147,483,648 to 2,147,483,647. This means that the latest Epoch time that can be represented is 2147483647. This will occur at 03:14:07 on Tuesday, 19 January 2038.

After that, the result will largely depend on the system. In many systems, an integer overflow will occur, and any later times will wrap around and be stored internally as a negative number. The result is that one second later, the time will be interpreted as being on 13 December 1901 rather than 19 January 2038.

However, you could also end up with varying results, depending on the application in use. Even if your operating system has no problem, your own code might still have a problem. For example, if you’ve written custom code to return Unix time, and you store it in a signed 4 byte integer, you’ll have problems. In such cases, rewriting the code to use an 8 byte integer might be all you need to do.

Seeing as this website is all about databases, here are some database examples.

Example 1 – MySQL

In MySQL, the TIMESTAMP data type supports dates/times from ‘1970-01-01 00:00:01.000000’ UTC to ‘2038-01-19 03:14:07.999999’. Therefore, you could say that any database using this data type has a Y2K38 bug.

MySQL also has an inbuilt function called UNIX_TIMESTAMP() which, as you might expect, returns the Unix timestamp.

The UNIX_TIMESTAMP() function accepts an optional argument that allows you to specify a date to use for the Unix time (i.e. the number of seconds from ‘1970-01-01 00:00:00’ UTC to the time that you specify). The valid range of argument values is the same as for the TIMESTAMP data type, which is ‘1970-01-01 00:00:01.000000’ UTC to ‘2038-01-19 03:14:07.999999’ UTC. If you pass an out-of-range date to this function, it returns 0.

Here’s what happens if you attempt to use this function to return the Unix time from a date past ‘2038-01-19 03:14:07.999999’:

SELECT UNIX_TIMESTAMP('2038-01-20') Result;

Result:

+--------+
| Result |
+--------+
|      0 |
+--------+

We get 0 because the date argument is outside the supported range.

A related bug report was raised for the MySQL team in 2005 (although some of the specifics appear to be different), and as of this writing, still hasn’t been addressed.

A similar issue was also raised to address the limitations with the TIMESTAMP data type, which is also yet to be addressed.

Example 2 – SQL Server

SQL Server doesn’t currently have an equivalent of MySQL’s UNIX_TIMESTAMP function. Therefore, if you need to return Epoch time, you’ll need to do something like this:

SELECT DATEDIFF(SECOND,'1970-01-01', GETUTCDATE());

This is fine for dates prior to the 2038 problem. After that date, you’ll have issues, because the DATEDIFF() function returns the result as an int data type. The int data type has a range of -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647).

Here’s what happens if I try to return Epoch time later than ‘2038-01-19 03:14:07’:

SELECT DATEDIFF(SECOND,'1970-01-01', '2038-01-19 03:14:08') AS 'Result';

Result:

The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.

Fortunately, there’s also a DATEDIFF_BIG() function, which does exactly the same thing, except it returns the result as a bigint data type.

So we can rewrite the previous example to the following to overcome this issue:

SELECT DATEDIFF_BIG(SECOND,'1970-01-01 00:00:00', '2038-01-19 03:14:08') AS 'Result';

Result:

+------------+
| Result     |
|------------|
| 2147483648 |
+------------+

The bigint data type uses 8 bytes (as opposed to 4 bytes for an int), so you will need to decide whether or not to switch to DATEDIFF_BIG() now or later. If your application deals with future dates, it might be prudent to do it earlier than later.