Forum: Ruby on Rails Why is datetime used over timestamp in ROR?

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
525128e48ca2b4c7fb6176ea166fccfd?d=identicon&s=25 Eric G. (gotskill10)
on 2008-11-01 01:22
Hey guys,

I have a similar thread at http://www.ruby-forum.com/topic/169664, 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. =)
2505b282d57c29be797dc35b245adb4c?d=identicon&s=25 Philip Hallstrom (Guest)
on 2008-11-01 04:00
(Received via mailing list)
> 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 :)
525128e48ca2b4c7fb6176ea166fccfd?d=identicon&s=25 Eric G. (gotskill10)
on 2008-11-01 05:12
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 Hallstrom wrote:
>> 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 :)
525128e48ca2b4c7fb6176ea166fccfd?d=identicon&s=25 Eric G. (gotskill10)
on 2008-11-02 00:55
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.
280b78a61a968391b7e07e912be102a8?d=identicon&s=25 Robert Walker (robert4723)
on 2008-11-02 04:25
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 Grosz wrote:
> Actually, I researched it somewhere, it turns out TIMESTAMP should be
> completely able to replace DATETIME without caveats.
525128e48ca2b4c7fb6176ea166fccfd?d=identicon&s=25 Eric G. (gotskill10)
on 2008-11-03 18:21
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?
This topic is locked and can not be replied to.