0

I just realized about the 2038 year's problem when the unix time will be reseted to it's negative minimum range, so I decided to do a little research for this interesting topic.

Right now I am designing the structure of a database (in mysql) and I think these two considerations might resolve the problem:

1) - Storing the time data NOT in a timestamp field but in a bigint (or larger) column.

2) - The server that I will use for my application uses a 64 bits OS, so if I use the php date function it will return the date correctly.

Basing myself in those considerations I'm about to accept using timestamp, what do you think about it? Thank's..

Neo
  • 395
  • 2
  • 6
  • 23

1 Answers1

0

MySQL DATETIME columns have a range all the way out to 9999-12-31 23:59:59 so I would suggest using those rather than a TIMESTAMP, if your worried about the Y2K38 problem.

The only advantage TIMESTAMP has over DATETIME is the automated timezone conversions but we tend to store all our times as UTC anyway so that's not an issue for us.

If you really want to use timestamps, then I'm pretty certain there'll be a concerted effort well before 2038 to upgrade MySQL and C systems to a time_t that has much more range. Since it's a distinct type under C, it'll be fairly easy to update.

And, unlike flat files in C, it'll be a lot easier to migrate database data simply because column meta-data (such as which columns contain timestamps) is easily available.

paxdiablo
  • 854,327
  • 234
  • 1,573
  • 1,953
  • Thank's for your answer, in the beginning I was deciding to use timestamp because the server I'm using (a free hosting) doesn't have the same time-zone as mine, so a quick solution could be to make the time difference with php (taking away) and then take that time as a base for everything (including registering through that function), so what possible / decent solution could exist in order to do the same but for datetime? – Neo Aug 07 '13 at 03:14
  • @Neo, where our application runs in a different timezone, the queries are simply adjusted to suit, based on a configuration item. In other words, time info from the user is shifted to UTC as early as possible while time info to the user is shifted back to local as late as possible. But, see mu penultimate paragraph, this will never become an issue because, long before 2038, the underlying type will have changed to fix this. – paxdiablo Aug 07 '13 at 03:17
  • I just found out that there's a mysql function called CONVERT_TZ, which takes 3 parameters, the date, the from timezone and the totimezone, probably it's my best solution :) – Neo Aug 07 '13 at 03:26
  • @Neo, that's not going to help with your y2k38 problem. From the docs: If the value falls out of the supported range of the TIMESTAMP type when converted from from_tz to UTC, no conversion occurs. It'll work for now but so will using a timestamp :-) – paxdiablo Aug 07 '13 at 03:28
  • I was talking about using it with a datetime field, does it work? if so then there is no problem with the y2k38 problem since datetime is recommended for avoid that. – Neo Aug 07 '13 at 03:29
  • @Neo, yes, it'll work with datetimes but I suspect only those that can be represented as timestamps, so not the full range. If I were you, I'd test it by trying to convert the datetime '2040-01-01 12:34:56' and see what happens. I suspect it won't change it becuase it's out of range. – paxdiablo Aug 07 '13 at 03:32
  • I would think so, @Neo, simply because the docs state the timestamp range regardless of whether you're on a 32 or 64 bit system. But don't take my word for it, give it a try. I still think you're probably expending unnecessary effort since MySQL is going to be useless come 2038 if they don't fix this, there's a _huge_ vested interest in fixing it, and it's a reasonably easy fix as stated. Or maybe Oracle _won't_ fix it, forcing everyone across to their flagship product. Perhaps this is a good time to start thinking about MariaDB :-) – paxdiablo Aug 07 '13 at 03:38
  • I'll tell you this though, the time functions in C will be fixed (or replaced with something better). Guaranteed. – paxdiablo Aug 07 '13 at 03:40
  • You're right, I decided to keep using timestamp in the 64 bits server, getting the correct time with the programming language, register it and avoding any 'extra' call to a mysql function, by the way the system I'm developing probably gets "updated" before that year arrives, I appretiate your help :) – Neo Aug 07 '13 at 03:43
  • I don't think there's any problem, mysql can hold as long as I specify, and the php functions returns correct conversion (in 64 bits of course) and the server (and all the new computers) will use OS like that (specially servers).. – Neo Aug 07 '13 at 03:47