CODEWITHSUNDEEP
phpmysqlunix-timestampunsigned-integeryear2038
rabudde
rabudde

Reputation: 7722

Would there be any benefit of storing unix timestamp as unsigned integer?

I don't want to start another discussion of pro or contra between using DATETIME vs TIMESTAMP vs INT. (I've already read articles like Using MySQL's TIMESTAMP vs storing timestamps directly.)

I sometimes use INT data type to store unix timestamps in database. That's because date and time calculations in my applications where done with unix timestamps often (e.g. for session timeouts and token expiration). Furthermore data selection in database is faster than using DATETIME when I can compare integer values in WHERE clause simply. There are few tables with 10+ million rows (up to 100 million) where this 4 bytes really save storage (on disk and in memory because of smaller indexes) also.

Regarding Y2K38 problem, I assume (and hope so), that UNIX_TIMESTAMP in MySQL an time() in PHP will return 64bit values in future, so there will be no need to change something in the applications itself. The point is, I've stored all these pseudo integer timestamp in MySQL as unsigned integers (I mean INT not BIGINT). Sure, unsigned integer timestamps will overflow in year 2106, but that's a bit more time than 2038.

My question is now: assuming that UNIX_TIMESTAMP itself will work after 2038, could there be any problems in MySQL and/or PHP until 2106 when these timestamps are stored as unsigned integer in MySQL? (Please don't argue with: there will be a lot time to fix that until 2038, I want to clarify this from the point of the application are not touched any longer)

EDIT: Because the question came up: I store only current timestamps in these columns, no birthdates, no future dates. Only current timestamps, so I want to clarify if this would work after 2038.

Upvotes: 4

Views: 4751

Answers (2)

João Vitor
João Vitor

Reputation: 11

It seems you were right on your assumptions, therefore it would be possible to keep using UNIX_TIMESTAMP and store all your date in an unsigned INT column.

This was taken from the MySQL documentation (https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_from-unixtime):

On 32-bit platforms, the maximum useful value for unix_timestamp is 2147483647.999999, which returns '2038-01-19 03:14:07.999999' UTC. On 64-bit platforms running MySQL 8.0.28 or later, the effective maximum is 32536771199.999999, which returns '3001-01-18 23:59:59.999999' UTC. Regardless of platform or version, a greater value for unix_timestamp than the effective maximum returns 0.

Upvotes: 1

andy
andy

Reputation: 706

Your assumption surrounding UNIX_TIMESTAMP() is a big one.

Currently, UNIX_TIMESTAMP returns 0 if you try

mysql> select unix_timestamp("2038-01-19" );
+-------------------------------+
| unix_timestamp("2038-01-19" ) |
+-------------------------------+
|                    2147468400 |
+-------------------------------+
1 row in set (0.00 sec)

mysql> select unix_timestamp("2038-01-20");
+------------------------------+
| unix_timestamp("2038-01-20") |
+------------------------------+
|                            0 |
+------------------------------+
1 row in set (0.00 sec)

While the storage of INTs longer than 32-bits will work, unless you know something about how the implementation of unix_timestamp(int64) will work, then the question is really more guesswork than facts.

This implies that any integer arithmetic you do will still be valid with 64-bit ints, so for finding expired sessions (timestamp + timeout < (seconds since 1970 in 64-bits)) will still work. Whether or not you can rely on from_unixtime() and unix_timestamp()-functions depend whether the solution is just to up the ante to 64-bits or if the whole world in the next 20-odd years decide to set a new epoch.

Nobody knows for sure.

Upvotes: 2

Related Questions