71

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.
Xeoncross
  • 55,620
  • 80
  • 262
  • 364
  • 1
    I'll worry about it then. ;) – Stephen Nov 27 '10 at 02:27
  • 3
    Even if you had 10 million rows, you would use a whole extra 9.5 MB... Just use the DATETIME type, really. – Vincent Savard Nov 27 '10 at 02:30
  • 2
    You should keep in mind that the purpose of the DateTime type is to protect you from having to deal with this problem at all. – Nick Nov 27 '10 at 02:34
  • 1
    @Vincent Actually, 300 million rows and growing is more correct. Plus, you forgot that the column is indexed which means it also takes that much more RAM to store in memory (besides disk space). – Xeoncross Nov 27 '10 at 02:44
  • Do you really think you app will still be running in 2038 in it's current manner? IMO, don't bother concerning yourself with problems you not likely to ever see. 17 years is a LONG time in software. – UnkwnTech Nov 27 '10 at 02:49
  • @Xeoncross : It's still going to be less than 1GB, most servers can handle it quite easily. I'm not a fan of using bad practices, but it's your call. – Vincent Savard Nov 27 '10 at 02:50
  • @Unkwntech but like I said, MySQL doesn't seem to support dates all the way to 2038 so It's possible that sometime *much sooner* my app will hit a wall. – Xeoncross Nov 27 '10 at 02:50
  • 1
    Even if the same app will not be running in 2038, there's very real reasons why you want to be able to store dates > 2038. You might want to represent dates in the future. – nos Nov 27 '10 at 02:53
  • 10
    Some of you youngsters might still be around in 2038 and have to deal with these choices then. ;-) – Don Roby Nov 27 '10 at 03:24
  • 4
    @Don he's just making sure there are still jobs for us in 2038. Keep up the good work, Xeoncross. – siride Nov 27 '10 at 04:19

1 Answers1

103

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.

Marc B
  • 356,200
  • 43
  • 426
  • 500
  • 3
    I'm glad you affirmed what I said, but what about the question of what this means for storing dates? – Xeoncross Nov 27 '10 at 02:43
  • 1
    A standard unix timestamp will go into a standard signed 'int' column in PHP without any conversion. There's no such thing as a signed unix time stamp - it's not in the standard libc, so you don't have to worry about any timestamps between 2,147,483,648 -> 4,294,967,295. PHP's timestamp support is directly based on libc as well, so PHP won't accept an unsigned timestamp either. – Marc B Nov 27 '10 at 02:45
  • 2
    It means you cannot store dates that way that's greater than 2^31-1 (That's 2147483647, which can represent time as seconds until 2038. 9,999,999,999 would get you to around 2287). This is a known problem;http://stackoverflow.com/questions/36239/what-should-we-do-to-prepare-for-2038 – nos Nov 27 '10 at 02:49
  • Oh, so the answer then is that 9999999999 is incorrect as the 2038 date (my bad) and ints don't need to be unsigned since 2147483647 fits fine in a MySQL signed int. So, no worries I really do have until 2038. – Xeoncross Nov 27 '10 at 02:54
  • 28
    Only if you don't need to deal with future values. Consider a bank working on a 30-year mortgage quote. That puts the end of the mortgage in 2040, past the 2038 limit. If/when the 64bit switch occurs, then we'll have the y292,277,026,596k problem. But if someone wants to arrange for me to be around then to fix it, I'd be happy to oblige. – Marc B Nov 27 '10 at 13:42