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. =) -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
> 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 :) --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
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 :)-- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
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.
--
Posted via http://www.ruby-forum.com/.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
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.-- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
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? -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---