14

Should I be using a big integer or a regular integer in MySQL to store a timerstamp in? I plan on storing it in an INT and not the built in timestamp or datetime so which INT type should I use?

JasonDavis
  • 48,204
  • 100
  • 318
  • 537
  • I wish there was a valid tutorial on the net for PROPERLY doing timezones for users in MySQL and PHP, seems all the info is outdated now that mysql has changed the format of timestamp fields to be exactly the same as a datetime – JasonDavis Jan 08 '10 at 23:11

3 Answers3

18

Int would roll over to a negative in 2038 (if you are using UNIX timestamp): http://en.wikipedia.org/wiki/2038_problem.

so BIGINT is probably the safest choice

zmbush
  • 2,790
  • 1
  • 17
  • 35
  • 1
    There won't be a y2k38 problem any more than there was a y2k problem. Somewhere around 2030, there'll be a mad rush started to change all the databases to use 64-bit timestamps and this will be easy, at least for those people who intelligently used timestamp in the tables rather than integers :-) – paxdiablo Jan 08 '10 at 22:39
  • 2
    *shrug*....if you'd make it a unsigned int, you could go up well into 2100 and some (provided you have a function that keeps giving out the numbers, and you convert them) – Roland Bouman Jan 08 '10 at 22:42
  • 4
    Well. There intelligent people that use bigint for work with datases (databases are databases not database + programs for marketing) because, portability and issues with datetime formats with databases, or versions of any databases. Int are Int. and not problem with Oracle, SQL, MySQL, MariaDb, and others. Datatimes types, too many problems on google search with several databases updates, conpatibilties.. Mucha veces no es cuestion de inteligencia, es cuestion de experiencia ;-) Many times it is not a matter of intelligence, is a matter of experience;-) – abkrim May 14 '16 at 07:06
14

You should be storing it in a timestamp since that's most likely what the DBMS will optimize for timestamp data.

I'm curious as to why you would sacrifice all the hard work that the MySQL developers have put into making timestamps work the way they should, and replacing it with something that will almost certainly not work as well.

Since you don't state why you want to use an integer, I'm just going to assume it was temporary insanity and that you'll soon recover :-)

paxdiablo
  • 854,327
  • 234
  • 1,573
  • 1,953
  • 2
    Usually they're used because php and other such languages make it much easier to work with timestamps than with a proper sql date format. – Swizec Teller Jan 08 '10 at 22:37
  • 1
    In that case, I would use UNIX_TIMESTAMP() to get the underlying time_t which is what's in the DB anyway. – paxdiablo Jan 08 '10 at 22:44
  • 1
    In my situation it just works, newer versions on mysql store both datetime and timestamp in this format now 0000-00-00 instead of 0000000 (the numbers and dashes may be off but you get the idea. Timestamp used to be a REAL timestamp but not anymore. All my time functions need a real timestamp from mysql, so if I store a timestamp in an INT it uses less space and it is less processing/converting back and forth. Like a said all my function need a timestamp anyways so if I give it the newer style timestamp, I then need to convert it into a real timestamp in php. After several days of testing – JasonDavis Jan 08 '10 at 22:46
  • 1
    every possible solution I could find for showing time in a users timezone and storing a time in UTC time, the only way I could get it to work was with using an INT field. I do not have to worry about sorting from mysql being slower or anything as all my queries rely on a ID number and not dat/time. – JasonDavis Jan 08 '10 at 22:48
  • If timestamps were handled like they were in older versions of mysql it would be sooo much easiar, thats my reasoning anyways, doesn't mean you will agree but it is all I can get to work 100% – JasonDavis Jan 08 '10 at 22:49
  • 1
    Timestamps are stored in the DB as time_t types and you can get at them with UNIX_TIMESTAMP(). – paxdiablo Jan 08 '10 at 22:54
  • If I query the table to get all rows, I will just use * for example instead of individual rows, "SELECT * from users WHERE uer_id = 1" then how would I use UNIX_TIMESTAMP() to retrienve the item? OR I just use UNIX_TIMESTAMP() to store it, and then retieve normal way? Right now I am using UTC_TIMESTAMP() to store my value, if I use UTC_TIMESTAMP() to store into a datetime, then when I query the table it comes back in a format like this 2009-23-42 instead of a string of number like a timestamp 53453453 – JasonDavis Jan 08 '10 at 22:59
  • sorry I said when I store it as a datetime, I also meant to say when I store it into a DATETIME OR A TIMESTAMP I have the problem with both, I realize that the DATETIME is supposed to store and give it back in that format – JasonDavis Jan 08 '10 at 23:10
  • If you really have to select *, then do this: "select *, unix_timestamp(your_timestamp_column) from users where user_id=1" – Ian Clelland Jan 09 '10 at 00:20
  • 1
    Lol. Any "issues" for datetimes on mysql http://dba.stackexchange.com/questions/48704/mysql-5-6-datetime-incorrect-datetime-value-2013-08-25t1700000000-with-er http://stackoverflow.com/questions/17598629/mysql-incorrect-datetime-format and there a lot bug on bugtracker of mariadb, mysql, etc... thanks developers of databases. – abkrim May 14 '16 at 07:09
9

I think it entirely depends on what you want to do. There a few proper types for time/date types (see: http://dev.mysql.com/doc/refman/5.0/en/date-and-time-type-overview.html)

  • DATE - typically 3 bytes, range: 1000-01-01 to 9999-12-31.
  • DATETIME - 8 bytes, range 1000-01-01 00:00:00 to 9999-12-31 23:59:59
  • TIMESTAMP - 4 bytes range 1970-01-01 00:00:01 UTC to 2038-01-19 03:14:07 UTC. Then, there are some semantic issues to be aware of:

(int is 4 bytes like TIMESTAMP, bigint is 8 bytes like DATETIME)

  • date stores calendar days, datetime and timestamp both store date+time at a second precision (sub-second precision is supported in date arithmetic, but not for storage)
  • timestamp stores an UTC value. That is, any value you stick in there is converted from the session's timezone (by default, the server's timezone) to UTC and stored. On retrieval, the UTC value is converted back again to the timezone in effect in the session.
  • timestamp can be declared to automatically get the current timestamp on insert or update or both. you best study the manual for the details (see http://dev.mysql.com/doc/refman/5.1/en/timestamp.html)
Roland Bouman
  • 31,125
  • 6
  • 66
  • 67