CODEWITHSUNDEEP
sql
Megaman
Megaman

Reputation: 341

field type for unix timestamp

Whats the best field type to use for unix timestamps?

Will int(10) be enough for a while?

Upvotes: 34

Views: 34099

Answers (4)

CodeStock
CodeStock

Reputation: 99

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;

Upvotes: 7

bobince
bobince

Reputation: 536469

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.

Upvotes: 16

Michael Petrotta
Michael Petrotta

Reputation: 60912

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.

Upvotes: 79

Tatu Ulmanen
Tatu Ulmanen

Reputation: 124788

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

Upvotes: 3

Related Questions