I have a logging table that will contain millions of writes for statistical reasons. All the columns are int foreign keys. I am also going to add a timestamp column for each row. Given that DATETIME takes 8bits - I will be using int(10) unsigned
to cut the storage space (and index on that column) in half.
However, I'm wondering when this column would no longer work. At 3:14:07AM on 19th January 2038 the value 9,999,999,999 will be a problem for UNIX timestamps - but an unsigned int in MySQL only holds up to 4,294,967,295 and the timestamp 4294967295 is showing an invalid number in my PHP application.
So what does this mean? Is the end of the storing int timestamps in MySQL going to be sometime in 2021 since it can't make it all the way to 9999999999?
Answer:
unsigned
isn't needed since 2147483647 fits fine in a signed MySQL int.Unix timestamps should be stored as long numbers; if they are store as Java int values, then this leads to a 2038 year problem. 32-bit variables cannot encode times after 03:14:07 UTC on 19 January 2038.
A Unix timestamp is a large integer (the number of seconds since 1970-01-01 00:00:00 UTC), so INT(11) is the correct datatype.
Unix epoch timestamps10 digit epoch time format surrounded by brackets (or followed by a comma). The digits must be at the very start of the message. For example, [1234567890] or [1234567890, other] followed by the rest of the message. 13 digit epoch time.
Unix timestamps are always based on UTC (otherwise known as GMT). It is illogical to think of a Unix timestamp as being in any particular time zone. Unix timestamps do not account for leap seconds.
Standard UNIX timestamps are a signed 32bit integer, which in MySQL is a regular "int" column. There's no way you could store 9,999,999,999, as that's way outside the representation range - the highest a 32bit int of any sort can go is 4,294,967,295. The highest a signed 32bit in goes is 2,147,483,647.
If/when UNIX timestamps go to a 64bit data type, then you'll have to use a MySQL "bigint" to store them.
As for int(10)
, the (10)
portion is merely for display purposes. MySQL will still use a full 32bit internally to store the number, but only display 10 whenever you do a select on the table.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With