Jan 2038 Unixtime Question

[curlyptc]

I've been using unixtime on my web pages for decades. And I have always known there is a limit to how far in the future I can use it... specifically Jan 19, 2038. I noticed the other day, though, that a page I created to create and process unix times was allowing me to go beyond that date. It's here and not password protected... http://www.widgetseniority.com/unixtime.php.   However, thinking it was now allowed, I attempted to add a field to a table in a MySQL database that showed unixtimes for dates, many of which would go beyond Jan 2038, and when I used mktime() to calculate them, it had that max limit. 

Both these pages are on the same server. 

Any ideas why one might honor the limit, and the other not? I asked the help desk of my web host, who are normally wonderful, and they punted it, saying it was an issue for PHP experts. 

[Barand]

Depends on the column definition for your date/time column (DATETIME or TIMESTAMP)

From the manual ...

Quote

... the range for DATETIME values is '1000-01-01 00:00:00.000000' to '9999-12-31 23:59:59.999999', and the range for TIMESTAMP values is '1970-01-01 00:00:01.000000' to '2038-01-19 03:14:07.999999'.

The limit of a timestamp is the maximum value of a signed integer,

mysql> SELECT from_unixtime(2147483647);
+---------------------------+
| from_unixtime(2147483647) |
+---------------------------+
| 2038-01-19 03:14:07       |
+---------------------------+

When storing dates and times in a database, use DATETIME, TIMESTAMP, DATE or TIME types. As well as DATE AND DATETIME having a greater date range, they are infinitely more readable for mere humans.

[requinix]

What's happening behind the scenes is a matter of 32-bit versus 64-bit integers. You know how everything is 64-bit now, right? That's kinda true.

Signed (=capable of representing positive and negative values) 32-bit integers are limited to +/- 2.1 billion. If each of those is 1 second and you start counting from January 1970 then that stops sometime in January 2038. A lot of software is still 32-bit, but less and less each year.
Signed 64-bit integers are limited to... well, a very large number in the unfathomably-distant future - which is to say, as far as timestamps are concerned, it's effectively unlimited. Unless you want to try to count sub-second intervals, in which case it's slightly less than but still more or less unlimited.

For a simpler example, consider Y2K. If you represent years as just 00-99 then December 1999 ends with you starting January 1900. The best solution to that is to use more digits: four digits means you can tell the difference between 1900 and 2000, just like how with 64-bit numbers you could tell the difference between Jan 1st 1970 (0) and Feb 7th 2106 (2^32).

Regarding PHP, it's been 64-bit for a very long time, except on Windows when it's only been available since PHP 7.0 (which most people would also count as "a very long time"). But Y2K38 is still a thing.

[curlyptc]

On 4/30/2021 at 8:42 AM, Barand said:

Depends on the column definition for your date/time column (DATETIME or TIMESTAMP)

From the manual ...

The limit of a timestamp is the maximum value of a signed integer,

mysql> SELECT from_unixtime(2147483647);
+---------------------------+
| from_unixtime(2147483647) |
+---------------------------+
| 2038-01-19 03:14:07       |
+---------------------------+

When storing dates and times in a database, use DATETIME, TIMESTAMP, DATE or TIME types. As well as DATE AND DATETIME having a greater date range, they are infinitely more readable for mere humans.

I actually define the column in the table as a 15 digit integer. I don't define it as a date field in any way. In both the page that works to go past Jan 2038, and this one that doesn't, I use the same technique, either mktime() or strtotime(). In the one that doesn't work I then try to insert the value into the table. 

[curlyptc]

On that unixtime page, the one that's calculating values larger than 2147483647, I added an update query to enter that value in a record. Even though the field isn't a date or time field, again it enters a max of 2147483647. I then checked to see what type of variable PHP was making that number, and it was an integer. 

SOMEWHERE something at the server is saying "Oh no, the max for this number is 2147483647.

[Barand]

Check the column definition in your table. If you make it UNSIGNED you can double that value, which will take you to the year 2106.

echo date('Y-m-d', 4294967294);     // 2106-02-07

Better still, use date or DATE/DATETIME fields for dates - that's what they're there for.

[curlyptc]

On 5/3/2021 at 10:27 AM, Barand said:

Check the column definition in your table. If you make it UNSIGNED you can double that value, which will take you to the year 2106.

echo date('Y-m-d', 4294967294);     // 2106-02-07

Better still, use date or DATE/DATETIME fields for dates - that's what they're there for.

Wow! Changing the column attribute to unsigned did it. THANK YOU!