Should I use a big INT or regular INT in MySQL to store a timestamp?

Should I be using a big integer or a regular integer in MySQL to store a timerstamp in? I plan on storing it in an INT and开发者_开发知识库 not the built in timestamp or datetime so which INT type should I use?

---

Int would roll over to a negative in 2038 (if you are using UNIX timestamp): http://en.wikipedia.org/wiki/2038_problem.

so BIGINT is probably the safest choice

---

You should be storing it in a timestamp since that's most likely what the DBMS will optimize for timestamp data.

I'm curious as to why you would sacrifice all the hard work that the MySQL developers have put into making timestamps work the way they should, and replacing it with something that will almost certainly not work as well.

Since you don't state why you want to use an integer, I'm just going to assume it was temporary insanity and that you'll soon recover :-)

---

I think it entirely depends on what you want to do. There a few proper types for time/date types (see: http://dev.mysql.com/doc/refman/5.0/en/date-and-time-type-overview.html)

• DATE - typically 3 bytes, range: 1000-01-01 to 9999-12-31.
• DATETIME - 8 bytes, range 1000-01-01 00:00:00 to 9999-12-31 23:59:59
• TIMESTAMP - 4 bytes range 1970-01-01 00:00:01 UTC to 2038-01-19 03:14:07 UTC. Then, there are some semantic issues to be aware of:

(int is 4 bytes like TIMESTAMP, bigint is 8 bytes like DATETIME)

• date stores calendar days, datetime and timestamp both store date+time at a second precision (sub-second precision is supported in date arithmetic, but not for storage)
• timestamp stores an UTC value. That is, any value you stick in there is converted from the session's timezone (by default, the server's timezone) to UTC and stored. On retrieval, the UTC value is converted back again to the timezone in effect in the session.
• timestamp can be declared to automatically get the current timestamp on insert or update or both. you best study the manual for the details (see http://dev.mysql.com/doc/refman/5.1/en/timestamp.html)