I'm trying to figure out the best datatype and size in my DB to store unix timestamps...
In otherwords:
INT(32)
etc...
Thanks for any tips.
Add a comment
|
2 Answers
TIMESTAMP is a pretty straight-forward choice. It's implemented as a UNIX timestamp internally, but externally it appears as a date string (e.g. "1970-01-01 00:00:01").
EDIT: To migrate an INT to a timestamp, where time_test is the table and ts is the original column:
ALTER TABLE time_test ADD ts_new TIMESTAMP;
UPDATE time_test SET ts_new = FROM_UNIXTIME(ts);
ALTER TABLE time_test DROP ts;
ALTER TABLE time_test CHANGE COLUMN ts_new ts TIMESTAMP;
You may have to tweak it slightly if you care about the column order.
4 Comments
Shackrock
I see. I didn't realize that... Answer me this: If I'm already set up as an INT, can I just go through and change everything to TIMESTAMP and be good to go? Do I need to specify a size?
Shackrock
Now I'm startnig to doubt what I thought I understood.. haha. I want to continue using the column as a unix timestamp - that is, the bulk of numbers. When you say "externally displayed" - what does that really mean?
Matthew Flaschen
@Shackrock, if you just do a
SELECT ts FROM time_test you'll see a date string. But if you do SELECT UNIX_TIMESTAMP(ts) FROM time_test; you'll get exactly the number you put in (no precision loss). There may be ways to avoid the explicit function call.Shackrock
Ahh... I see. In this case, I think it's best if I don't fool too much with it. What if I asked this: What is the ideal INT size for this? haha!
MySql supports Unix timestamps directly as the TIMESTAMP data type.
You have the normal limitations; dates must be from 1 Jan 1970 until 19 Jan 2038.
1 Comment
Shackrock
What about going beyond 2038... is there no fix to that really yet? ha.