3

I don't want to start another discussion of pro or contra between using DATETIME vs TIMESTAMP vs INT. (I've already read articles like Using MySQL's TIMESTAMP vs storing timestamps directly.)

I sometimes use INT data type to store unix timestamps in database. That's because date and time calculations in my applications where done with unix timestamps often (e.g. for session timeouts and token expiration). Furthermore data selection in database is faster than using DATETIME when I can compare integer values in WHERE clause simply. There are few tables with 10+ million rows (up to 100 million) where this 4 bytes really save storage (on disk and in memory because of smaller indexes) also.

Regarding Y2K38 problem, I assume (and hope so), that UNIX_TIMESTAMP in MySQL an time() in PHP will return 64bit values in future, so there will be no need to change something in the applications itself. The point is, I've stored all these pseudo integer timestamp in MySQL as unsigned integers (I mean INT not BIGINT). Sure, unsigned integer timestamps will overflow in year 2106, but that's a bit more time than 2038.

My question is now: assuming that UNIX_TIMESTAMP itself will work after 2038, could there be any problems in MySQL and/or PHP until 2106 when these timestamps are stored as unsigned integer in MySQL? (Please don't argue with: there will be a lot time to fix that until 2038, I want to clarify this from the point of the application are not touched any longer)

EDIT: Because the question came up: I store only current timestamps in these columns, no birthdates, no future dates. Only current timestamps, so I want to clarify if this would work after 2038.

Community
  • 1
  • 1
rabudde
  • 7,498
  • 6
  • 53
  • 91
  • 1
    If you need values outside the 32-bit range; then don't use unix timestamps on 32-bit systems. Use DateTime objects in PHP, and use DATETIMEs in MySQL... and you couldn't store my birthdate as an unsigned unix timestamp, because it's before 1970, so you're simply shifting the limitations into the future at the cost of the past – Mark Baker Aug 02 '13 at 09:28
  • @MarkBaker That just depends on what you wish to store. Say I wish to store a date my blogpost was created. (UNSIGNED) INT is fine there. – AmazingDreams Aug 02 '13 at 09:32
  • @MarkBaker "e.g. for session timeouts and token expiration" – rabudde Aug 02 '13 at 09:34
  • 4
    But OP isn't simply talking about storing today's dates, they're talking about storing dates that fall outside of the 32-bit unix timestamp limit (> 2038). If they're simply storing session timeouts and token expiration; then they shouldn't even be thinking about values > 2038.... is their app really so groundbreaking that it will still be running in its present form in 25 years time? And that (if it is) they'll still be worried about saving a few bytes of disk storage in 2038? – Mark Baker Aug 02 '13 at 09:34
  • I can see two solutions to this. Either you store your timestamps as an offset from a custom time or, perhaps better, you change the datatype to something capable of storing the information you need. Storing a longer timestamp will pretty much always be less data-hungry than a datetime. The addition of another byte would allow IRO 34000 more timestamp years so it is perfectly possible for the implementation to be changed in future versions of mysql. There is no need for nice even byte numbers after all. In the meanwhile, consider a bigint if a few MB are not so important. – Robert Seddon-Smith Aug 02 '13 at 10:04
  • 2
    "I assume that UNIX_TIMESTAMP in MySQL an time() in PHP will return 64bit values in future". Under these assumptions, then of course nothing can go wrong! – RandomSeed Aug 03 '13 at 01:49

1 Answers1

1

Your assumption surrounding UNIX_TIMESTAMP() is a big one.

Currently, UNIX_TIMESTAMP returns 0 if you try

mysql> select unix_timestamp("2038-01-19" );
+-------------------------------+
| unix_timestamp("2038-01-19" ) |
+-------------------------------+
|                    2147468400 |
+-------------------------------+
1 row in set (0.00 sec)

mysql> select unix_timestamp("2038-01-20");
+------------------------------+
| unix_timestamp("2038-01-20") |
+------------------------------+
|                            0 |
+------------------------------+
1 row in set (0.00 sec)

While the storage of INTs longer than 32-bits will work, unless you know something about how the implementation of unix_timestamp(int64) will work, then the question is really more guesswork than facts.

This implies that any integer arithmetic you do will still be valid with 64-bit ints, so for finding expired sessions (timestamp + timeout < (seconds since 1970 in 64-bits)) will still work. Whether or not you can rely on from_unixtime() and unix_timestamp()-functions depend whether the solution is just to up the ante to 64-bits or if the whole world in the next 20-odd years decide to set a new epoch.

Nobody knows for sure.

andy
  • 696
  • 5
  • 14
  • Regarding the other comments and your answer (for sure, this is a theoretical assumption), I think, for the situations where I store an `UNIX_TIMESTAMP()` (represented as 32bit int at the moment and as 64bit in future hopefully) could still work after Y2K38 because of unsigned int columns in database. – rabudde Sep 10 '13 at 08:49
  • Well as you say, and I didn't make entirely clear in my answer. The number of seconds since 1970 (or any epoch) will still be valid in 2038+, whether or not the world will still be using the rather arbitrary 1970s epoch is up in the air. So worst case scenario, you will have to implement the timestamp-functions for ease of use yourself. Your data will still be valid regardless - as long as you know which epoch you used. – andy Sep 17 '13 at 11:46