I created a test table with "created_at" and "created_on" as types
DATETIME and TIMESTAMP respectively. I then created a record and saved
it.
I then ran a bunch of methods on created_at and created_on seeing if
they behaved differently. Basically, I found that the ONLY DIFFERENCE
between the two types is that TIMESTAMP has to be defaulted to a value,
whereas DATETIME does not. This, however, has no effect with Rails, as
I'll explain...
So far there are two reasons people tell me that DATETIME is used over
TIMESTAMP (both, which I'll disprove):
1. TIMESTAMP *always* automatically updates itself with the current time
when the record updates, hence removing that logic from Rails. This is
NOT TRUE. TIMESTAMP can be entered into MySQL without this behavior
present. See: http://dev.mysql.com/doc/refman/5.0/en/timestamp.html
2. TIMESTAMP requires a default value, hence interfering with the magic
timestamp logic from Rails. This is NOT TRUE either. In my tests, Rails
populates the created_at/created_on field the same way as it would for
DATETIME, since it ignores the default timestamp value assigned to it
from the TIMESTAMP data type.
So what is the reasoning behind forcing Rails migrations into using
DATETIME for timestamps? And can TIMESTAMP really take the place of
DATETIME in the RoR framework?
So far, my answer is a definite yes, but the guys behind Rails are smart
and I'm probably missing something, I just don't know what, yet. Maybe
someone can shed some like on this monumental issue. =)
2. TIMESTAMP requires a default value, hence interfering with the magic
timestamp logic from Rails. This is NOT TRUE either. In my tests, Rails
populates the created_at/created_on field the same way as it would for
DATETIME, since it ignores the default timestamp value assigned to it
from the TIMESTAMP data type.
What if I have a "published_at" field? If you force that to be a TIMESTAMP (since I'm assuming your arguing that a migration with t.datetime should turn into a MySQL TIMESTAMP) then all of my records will have some value in published_at when the non-published ones should have a null value.
Would that be a reason not to do it? It's late and I haven't thought it all the way through
1. Im not talking about changing t.datetime to timestamp. Right now,
even t.timestamp doesn't return timestamp for mysql.
2. Im questioning if Rails can function properly if you chose to use
TIMESTAMP over DATETIME *assuming* you are comfortable with the field
having a default timestamp value. In your case, you wouldnt be
comfortable with that behavior for "published_at", but might be for
"created_at".
My hypothesis based on the evidence I have is that changing a DATETIME
to TIMESTAMP should NOT effect anything in your Rails app assuming you
understand that you must have a default value on the timestamp field.
Thanks for the response. Yeah, I didn't bring up the range / date
limitations for timestamp because that's pretty well understood and
documented. That is, after all, the reason why it takes up half the
space in the database.
At this point, my conclusion on this whole topic is. Whenever you have a
timestamp field that you know will never have values before the year
1970, than you can use:
