34

Whats the best field type to use for unix timestamps?

Will int(10) be enough for a while?

Megaman
  • 341
  • 1
  • 3
  • 4

4 Answers4

78

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
  • 59,888
  • 27
  • 145
  • 179
  • 3
    Please be aware that `int(8)` does not always do what this answer suggests it does; [bobince's answer has an explanation.](http://stackoverflow.com/a/1993585/2014893) – Robert K. Bell Aug 30 '16 at 05:25
16

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
  • 528,062
  • 107
  • 651
  • 834
6

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;
CodeStock
  • 89
  • 1
  • 2
  • 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) https://stackoverflow.com/questions/58938358/mysql-warning-1681-integer-display-width-is-deprecated – Syed Waqas Bukhary Oct 09 '20 at 23:09
  • "If your app will stop working because of this, you will be long dead ;)" Ahahaha))) lol – Ivan Zaruba Mar 09 '21 at 14:16
3

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

Tatu Ulmanen
  • 123,288
  • 34
  • 187
  • 185
  • 6
    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 Jan 03 '10 at 01:00
  • @Megaman: no. Timestamp is just a datatype, it doesn't have any such special behaviour. – bobince Jan 03 '10 at 01:02
  • 1
    @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 Jan 03 '10 at 01:05
  • @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 Jan 03 '10 at 03:09
  • 1
    @TatuUlmanen [you almost certainly don't want `TIMESTAMP`](http://stackoverflow.com/questions/22860351/mysql-column-type-timestamp-implicitly-includes-not-null-default-current-time). – jameshfisher Apr 04 '14 at 11:34