field type for unix timestamp

Whats the b开发者_开发知识库est field type to use for unix timestamps?

Will int(10) be enough for a while?

Unix time_t is either 32 bits wide, or 64. So, int(8) or binary(8) is sufficient, at least for the next 293 billion years.

The number in a MySQL INT(n) datatype doesn't specify how much storage space is reserved, it's a display-width for formatting purposes only. As such an INT(10) is the same as a plain INTEGER, that is to say a 32-bit signed number.

So this is certainly an appropriate datatype for a 32-bit Unix timestamp. But if you want 64-bit timestamps it's not going to be enough; you would have to use a BIGINT.

For Unix timestamp you can easily use INT(4) UNSIGNED which max value is 4294967295. It's far enough for you to store time() values for the next ~133 years. If your app will stop working because of this, you will be long dead ;)

You can also try to use TIMESTAMP data type, which is less problematic and when you want to convert it to Unix timestamp you can use UNIX_TIMESTAMP() function.

ex.

SELECT UNIX_TIMESTAMP(col_timestamp) FROM tbl_name;

For timestamps, you should use the TIMESTAMP or DATETIME field type.