Whats the best field type to use for unix timestamps?
Will int(10) be enough for a while?
+1
A:
For timestamps, you should use the TIMESTAMP or DATETIME field type.
Tatu Ulmanen
2010-01-03 00:48:35
doesnt sql timestamp update each time you update/edit a row?
Megaman
2010-01-03 00:57:31
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
2010-01-03 01:00:12
@Megaman: no. Timestamp is just a datatype, it doesn't have any such special behaviour.
bobince
2010-01-03 01:02:00
@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.
OMG Ponies
2010-01-03 01:05:11
@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...
pilcrow
2010-01-03 03:09:49
+2
A:
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.
Michael Petrotta
2010-01-03 00:50:59
+1
A:
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.
bobince
2010-01-03 01:23:09