0

I've always been careful to use unsigned values whenever I can. As a result, all my timestamp fields are unsigned. Rather than dealing with a 2038 bug as all of you will be facing in a couple decades, someone else can deal with the year 2108 problem after I'm likely dead or at least long retired.

Or so I thought. However, it's come to my attention that all my effort may be entirely useless in any case where I've relied on MySQL's super handy UNIX_TIMESTAMP() function rather than having PHP parse the query using time(). It would be nice if UNIX_TIMESTAMP() was smart (or dumb) enough to wrap so that it would still be useful after this period, but apparently it just returns 0. (And all this time I thought I was being dumb by using time() when it's much more efficient to let MySQL use UNIX_TIMESTAMP().)

In some cases, I've been transitioning to a new standard I call 'millitime' which is the number of milliseconds since the Unix epoch stored in a 64-bit number. Seems a much better trade-off to have millisecond precision for 292 million years than to have seconds for 292 billion years. Since most servers deal with a large number of requests per second, it can be important to have this level of precision, whereas I stress to think of a practical application for managing dates of event 300 million years in the future. (Will humanity even exist then?) There doesn't seem to be a standard for this so I believe it needs to be manually parsed with PHP from the microtime() function.

  function millitime () {
    $a = microtime();
    return substr($a, 11).$a[2].$a[3].$a[4];
  }

So I have a few questions:

  1. Is this true. Will UNIX_TIMESTAMP() fail even if being put into an unsigned 4-byte field?
  2. Do you expect that UNIX_TIMESTAMP() may be updated before 2038 to wrap or otherwise fix this issue, or will MySQL simply break? What do you think will be the most likely way that MySQL will adapt as this date approaches.
  3. What is the simplest and most efficient unsigned alternative when I know I wont need more than seconds of precision and there's no requirement for the system to be used past 2108? Does MySQL have an equivalent that's 64-bit or unsigned and could still be stored in a 4-byte field?
  4. What's the best (fastest) way to generate millitime() as described above to be placed in a database or used in PHP? Not opposed to bit bashing.
azoundria
  • 940
  • 1
  • 8
  • 24
  • Possible duplicate of [PHP & mySQL: Year 2038 Bug: What is it? How to solve it?](http://stackoverflow.com/questions/2012589/php-mysql-year-2038-bug-what-is-it-how-to-solve-it) – Patrick Q Jan 12 '16 at 14:58
  • 1
    [This blog post](http://zavaboy.org/2013/01/29/alternative-for-mysql-unix_timestamp/) may also help. – Patrick Q Jan 12 '16 at 14:59
  • That question had many helpful resources, although most are unrelated to UNIX_TIMESTAMP(). Wrap around would be fine for most applications I work on, infinitely more valuable than 0. I've found DATETIME awkward to deal with (can't remember specifics) and it also requires doubling the storage. I like to keep multiple timestamps on every table so I know when things are generated and updated, so the space difference could be significant. That blog post was particularly useful. I wonder if there's any way to make it more readable in queries and if it's more efficient than using PHP's time(). – azoundria Jan 12 '16 at 16:10
  • @PatrickQ Is there a new blog post link? the one you referenced is broken. – melutovich Sep 12 '21 at 09:46

0 Answers0