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?
+8
A:
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 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
2010-01-08 22:33:48
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
2010-01-08 22:37:53
In that case, I would use UNIX_TIMESTAMP() to get the underlying time_t which is what's in the DB anyway.
paxdiablo
2010-01-08 22:44:45
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
2010-01-08 22:46:50
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
2010-01-08 22:48:12
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
2010-01-08 22:49:22
Timestamps are stored in the DB as time_t types and you can get at them with UNIX_TIMESTAMP().
paxdiablo
2010-01-08 22:54:38
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
2010-01-08 22:59:56
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
2010-01-08 23:10:30
If you really have to select *, then do this: "select *, unix_timestamp(your_timestamp_column) from users where user_id=1"
Ian Clelland
2010-01-09 00:20:38
+1
A:
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
zipcodeman
2010-01-08 22:35:00
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
2010-01-08 22:39:22
*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
2010-01-08 22:42:54
+1
A:
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-01to9999-12-31.DATETIME- 8 bytes, range1000-01-01 00:00:00to9999-12-31 23:59:59TIMESTAMP- 4 bytes range1970-01-01 00:00:01UTC to2038-01-19 03:14:07UTC. 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
2010-01-08 22:58:30