Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How should unix timestamps be stored in int columns?

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:

  1. 2147483647 is 2038 (not 9999999999) so there is no problem.
  2. unsigned isn't needed since 2147483647 fits fine in a signed MySQL int.
like image 382
Xeoncross Avatar asked Nov 27 '10 02:11

Xeoncross


People also ask

Can Unix time be stored in 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.

What datatype is used to store Unix timestamps?

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.

What is the format of Unix timestamp?

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.

Are Unix timestamps always UTC?

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.


1 Answers

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.

like image 165
Marc B Avatar answered Oct 15 '22 15:10

Marc B