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.