Whats the best field type to use for unix timestamps?
Will int(10) be enough for a while?
-
3What database are you using? And wouldn't you use a native datetime data type?OMG Ponies– OMG Ponies2010-01-03 01:11:12 +00:00Commented Jan 3, 2010 at 1:11
Add a comment
|
4 Answers
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.
1 Comment
Robert K. Bell
Please be aware that
int(8) does not always do what this answer suggests it does; bobince's answer has an explanation.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.
Comments
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;
2 Comments
Syed Waqas Bukhary
int(4) here (4) is just display width so if number is 13 it will display like 0013. And display width is deprecated in MySQL 8 and will be removed. Use only int rather than int(x) stackoverflow.com/questions/58938358/…
Ivan Zaruba
"If your app will stop working because of this, you will be long dead ;)" Ahahaha))) lol
For timestamps, you should use the TIMESTAMP or DATETIME field type.
5 Comments
bobince
However, this puts you at the mercy of the database and access layer for date types and string representations, which can make writing cross-environment applications more difficult. It is often simpler and more expedient to use plain integers whose behaviour is totally predictable, where there aren't other date types in the database against which you hope to compare.
bobince
@Megaman: no. Timestamp is just a datatype, it doesn't have any such special behaviour.
OMG Ponies
@Megaman: MySQL timestamp columns only update on record updates when you've set a DEFAULT constraint using the
ON UPDATE clause. This is the second time in the last few days I've seen people talk of the misunderstanding.
pilcrow
@OMG Ponies: MySQL
TIMESTAMP behavior is complex, and easy to misunderstand, IMHO. In 5.0, the first such column in a field is automagical if it lacks a DEFAULT and ON UPDATE specifier -- they're silently implied/applied -- and behavior was different in previous versions. These misunderstandings are unlikely to die down any time soon...
jameshfisher
@TatuUlmanen you almost certainly don't want
TIMESTAMP.