Why is datetime used over timestamp in ROR?

Hey guys,

I have a similar thread at Datetime vs timestamp - Rails - Ruby-Forum, but I
decided to open a new thread since I felt the original one was not clear
from my end and didn’t quite get to the bottom of the issue.

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. =)

  1. 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 :slight_smile:

Philip, thanks for responding. Two things:

  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.

Anyone else like to step up to the plate?

Aryk

Philip H. wrote:

  1. 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 :slight_smile:

Actually, I researched it somewhere, it turns out TIMESTAMP should be
completely able to replace DATETIME without caveats.

Currently in Rails

create_table(“some_table”) do |t|
t.timestamp(:created_at)
end

Gives

“add column created_at DATETIME”

I think you can reproduce the same exact behavior with:

“add column created_at TIMESTAMP NULL DEFAULT NULL”

This should behave exactly the same in rails, AFAIK.

Hey Robert,

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:

“add column created_at TIMESTAMP NULL DEFAULT NULL”

instead of datetime without any problems.

Does anyone see anything wrong with this?

Again from the MySQL docs.

MySQL Doumentation

The TIMESTAMP data type has a range of ‘1970-01-01 00:00:01’ UTC to
‘2038-01-09 03:14:07’ UTC. It has varying properties, depending on the
MySQL version and the SQL mode the server is running in. These
properties are described later in this section.

Actually, I researched it somewhere, it turns out TIMESTAMP should be
completely able to replace DATETIME without caveats.

So this statement is NOT true. DATETIME can store values before Jan 1,
1970 TIMESTAMP cannot. Therefore, you cannot say that TIMESTAMP is a
replacement for DATETIME. And then there’s the issue of not allowing
null values, which also apparently makes this statement false.

It was me that said that TIMESTAMP auto-updates. However, that
information was based on an old version of MySQL. TIMESTAMP in the
latest versions of MySQL have the option of auto-updating, but can be
configured not to do that. Sorry I was unaware of this change because I
stopped using TIMESTAMP data type a long time ago based on the old
behavior.

Here is the documentation from MySQL prior to 4.1, which my earlier
statements were based upon:

Automatic updating of the first TIMESTAMP column in a table occurs under
any of the following conditions:

You explicitly set the column to NULL.

The column is not specified explicitly in an INSERT or LOAD DATA INFILE
statement.

The column is not specified explicitly in an UPDATE statement and some
other column changes value. An UPDATE that sets a column to the value it
does not cause the TIMESTAMP column to be updated; if you set a column
to its current value, MySQL ignores the update for efficiency.

So as you can see my statements were in fact based on old information.
Again, sorry for the confusion.

Aryk G. wrote:

Actually, I researched it somewhere, it turns out TIMESTAMP should be
completely able to replace DATETIME without caveats.