PHP - Storing unix timestamp in mysql (avoiding the time_t overflow)

Question

I just realized about the 2038 year's problem when the unix time will be reseted to it's negative minimum range, so I decided to do a little research for this interesting topic.

Right now I am designing the structure of a database (in mysql) and I think these two considerations might resolve the problem:

1) - Storing the time data NOT in a timestamp field but in a bigint (or larger) column.

2) - The server that I will use for my application uses a 64 bits OS, so if I use the php date function it will return the date correctly.

Basing myself in those considerations I'm about to accept using timestamp, what do you think about it? Thank's..

Answer 1

MySQL DATETIME columns have a range all the way out to 9999-12-31 23:59:59 so I would suggest using those rather than a TIMESTAMP, if your worried about the Y2K38 problem.

The only advantage TIMESTAMP has over DATETIME is the automated timezone conversions but we tend to store all our times as UTC anyway so that's not an issue for us.

If you really want to use timestamps, then I'm pretty certain there'll be a concerted effort well before 2038 to upgrade MySQL and C systems to a time_t that has much more range. Since it's a distinct type under C, it'll be fairly easy to update.

And, unlike flat files in C, it'll be a lot easier to migrate database data simply because column meta-data (such as which columns contain timestamps) is easily available.