2

Do I need a MySQL data type INT or BIGINT to store Unix timestamp that is returned by PHP's mktime and used by date functions?

c00000fd
  • 20,994
  • 29
  • 177
  • 400
  • 1
    Why are you not using MySQL built in `TIMESTAMP` or `DATETIME` ? – cmorrissey May 13 '16 at 19:33
  • Unless there is a very specific reason you need to store 'dates' as integers, I would recommend using MySQL's internal date/timestamp functions. – mferly May 13 '16 at 19:33
  • 2
    Possible duplicate of [How should unix timestamps be stored in int columns?](http://stackoverflow.com/questions/4289793/how-should-unix-timestamps-be-stored-in-int-columns) – Charlotte Dunois May 13 '16 at 19:34
  • Though you should really be using DATETIME. – Charlotte Dunois May 13 '16 at 19:34
  • @CharlotteDunois: Thanks, it seems like `BIGINT` is the way to go to future-proof it. As for `DATETIME`, it was my original design. Unfortunately PHP is so bad at converting dates to and from `YYYY-MM-DD HH:MM:SS` format for MySQL so that I decided to just stick with Unix timestamp for clarity. For instance, I store all my dates in UTC time zone. But when I do those conversions PHP tries to convert it into server's local time that is such a pain!!! – c00000fd May 13 '16 at 19:51
  • Use PHP's `DateTime` and then set the timezone to `Europe/London`. http://php.net/DateTime – Charlotte Dunois May 13 '16 at 20:03
  • Do you really think PHP is better at converting dates to timestamps than to 'YYYY-MM-DD HH:MM:SS' format? Once you have a timestamp (or a DateTime object), it's an easy step to convert that to the appropriate format.... any problems are parsing human readable dates into a timestamp or a DateTime object; and DateTime objects are timezone aware, so that's even easier – Mark Baker May 13 '16 at 20:40
  • @MarkBaker: Maybe I'm just used to working with low-level programming languages that just don't do sh*t implicitly (behind my back.) And that's what PHP methods dealing with time seem to be doing IMO. I just prefer working with an `int` knowing that some API won't change time zone on it, etc... may be just me though. – c00000fd May 14 '16 at 01:11

1 Answers1

1

You can check PHP's constant PHP_INT_MAX to find the maximum integer. This will differ between environments. On my local system:

echo date('Y-m-d H:i:s', PHP_INT_MAX) . '<br />';
// outputs: 292277026596-12-04 09:30:07

So the maximum timestamp I can create is:

echo mktime(9, 30, 7, 12, 4, 292277026596);
// outputs: 9223372036854775807

Anything larger results in something similar to a 2038 problem (the number turns negative).

So on my particular system, I would need to use mysql's BIGINT to store these values, as INT only goes up to 2147483647. Reference.

Community
  • 1
  • 1
mister martin
  • 6,197
  • 4
  • 30
  • 63
  • Thanks. Yeah, it sounds like `BIGINT` is the way to go. And, as someone suggested in comments above, `TIMESTAMP` would not work, as it's implicitly 32-bit. Plus `TIMESTAMP` does those implicit conversions to and from UTC (that drive me crazy!) – c00000fd May 13 '16 at 20:00