ROR always converts :timestamp to :datetime. I''ve been googling and still havent found out why this is the case. The mysql data type DATETIME takes up twice the amount of space as TIMESTAMP. What will break in my app if I change DATETIME to TIMESTAMP. Is this well documented somewhere? -- 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 -~----------~----~----~----~------~----~------~--~---
Aryk Grosz wrote:> ROR always converts :timestamp to :datetime. I''ve been googling and > still havent found out why this is the case. > > The mysql data type DATETIME takes up twice the amount of space as > TIMESTAMP. What will break in my app if I change DATETIME to TIMESTAMP. > Is this well documented somewhere?I assume you''re talking about migrations. So when you create a field such as t.timestamp :my_field the column in the database is actually a datetime. Each database adapter uses a hash to convert what is used in the migration to what is used in the database. This is done with a method called native_database_types. For example, the Postgres adapter defines: class ActiveRecord::ConnectionAdapters::PostgreSQLAdapter # in order to add or change the datatypes, this function # must be overriden. Be careful, then, to not remove anything. # That carries with it the warning that if Rails Core changes # this function, this override will do away with those changes! def native_database_types { :primary_key => "serial primary key", :string => { :name => "character varying", :limit => 255 }, :text => { :name => "text" }, :integer => { :name => "integer" }, :float => { :name => "float" }, :decimal => { :name => "decimal" }, :datetime => { :name => "timestamp" }, :timestamp => { :name => "timestamp" }, :time => { :name => "time" }, :date => { :name => "date" }, :binary => { :name => "bytea" }, :boolean => { :name => "boolean" }, :bigint => { :name => "int8" } } end end This is actually my version to add support for bigints that I needed in a project. Notice that when :datetime or :timestamp is used, it becomes a timestamp in the database. If the MySQL adapter does not define this method explicitly, look at the abstract adapter. Also note the comment that I have in my overridden method. With great power comes great responsibility. Peace. -- 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 -~----------~----~----~----~------~----~------~--~---
In the case of MySQL there are significant differences between DATETIME and TIMESTAMP. From MySQL documentation: ------------------------------------------------- The DATETIME type is used when you need values that contain both date and time information. MySQL retrieves and displays DATETIME values in ''YYYY-MM-DD HH:MM:SS'' format. The supported range is ''1000-01-01 00:00:00'' to ''9999-12-31 23:59:59''. 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. ------------------------------------------------- It''s the limited range of TIMESTAMP that allows it to take up less storage than DATETIME. Note: If you''re optimizing for disk space, date/time fields are not the place to start anyways. ALSO: The TIMESTAMP data type in MySQL will automatically update itself if the column is not explicitly set in an UPDATE statement: Example: Person | id | first_name| last_name | created_at | updated_at | | 1 | null | null | 2008-10-29 15:00 | 2008-10-29 15:00 | Where created_at and updated_at were a TIMESTAMP not a DATETIME UPDATE people SET id = 1, first_name="Robert", last_name = "Walker"; | 1 | Robert | Walker | 2008-10-30 10:20:10 | 2008-10-30 10:20:10 | This would be the result of the update. This is NOT what you would want or expect. updated_at would have the correct value (even though it''s not specified in the update). However, created_at would be wrong. This is why Rails uses DATETIME and not TIMESTAMP. IMHO the only safe use of the TIMESTAMP data type is in columns that are not used directly by the application. They are good for "timestamping" a row in a database, but you must understand this self updating behavior. Aryk Grosz wrote:> ROR always converts :timestamp to :datetime. I''ve been googling and > still havent found out why this is the case. > > The mysql data type DATETIME takes up twice the amount of space as > TIMESTAMP. What will break in my app if I change DATETIME to TIMESTAMP. > Is this well documented somewhere?-- 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 -~----------~----~----~----~------~----~------~--~---
I haven''t seen anywhere in the mysql documentation that TIMESTAMP data type will automatically update itself. Where is this? It looks like timestamp HAS TO have a default value though, but I didn''t see anything about it not updating itself. Can you point me to it? Im wondering if anything will break in RoR between DATETIME and TIMESTAMP. -- 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 -~----------~----~----~----~------~----~------~--~---
Phillip, I see that you always use timestamp instead of datetime. Have you noticed any problems in Rails >2. How confident are you that timestamp and datetime can be used interchangeably? I know that TIMESTAMP needs to have a default value, but besides that, are there any issues? Aryk Phillip Koebbe wrote:> For example, the Postgres adapter defines: > > class ActiveRecord::ConnectionAdapters::PostgreSQLAdapter > # in order to add or change the datatypes, this function > # must be overriden. Be careful, then, to not remove anything. > # That carries with it the warning that if Rails Core changes > # this function, this override will do away with those changes! > def native_database_types > { > :primary_key => "serial primary key", > :string => { :name => "character varying", :limit => 255 }, > :text => { :name => "text" }, > :integer => { :name => "integer" }, > :float => { :name => "float" }, > :decimal => { :name => "decimal" }, > :datetime => { :name => "timestamp" }, > :timestamp => { :name => "timestamp" }, > :time => { :name => "time" }, > :date => { :name => "date" }, > :binary => { :name => "bytea" }, > :boolean => { :name => "boolean" }, > :bigint => { :name => "int8" } > } > end > end > > This is actually my version to add support for bigints that I needed in > a project. Notice that when :datetime or :timestamp is used, it becomes > a timestamp in the database. If the MySQL adapter does not define this > method explicitly, look at the abstract adapter. Also note the comment > that I have in my overridden method. With great power comes great > responsibility. > > Peace.-- 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 -~----------~----~----~----~------~----~------~--~---
Aryk Grosz wrote:> Phillip, > > I see that you always use timestamp instead of datetime. Have you > noticed any problems in Rails >2. How confident are you that timestamp > and datetime can be used interchangeably? > > I know that TIMESTAMP needs to have a default value, but besides that, > are there any issues? > > Aryk >I''m using Postgres, not MySQL. Postgres has only the timestamp data type (for date/time columns), which is why the native_database_types method has both :datetime and :timestamp mapped to it. And that is not my code. I redefined the method only to add :bigint at the bottom. Peace. -- 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 -~----------~----~----~----~------~----~------~--~---
On Fri, Oct 31, 2008 at 10:48 AM, Aryk Grosz <rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote:> > I haven''t seen anywhere in the mysql documentation that TIMESTAMP data > type will automatically update itself.<http://dev.mysql.com/doc/refman/5.0/en/timestamp.html> -- Hassan Schroeder ------------------------ hassan.schroeder-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
Right, but that is not the reason why timestamps dont use TIMESTAMP. You can always create the column without auto update functionality, according to mysql''s website. You must always have a default value for a timestamp, so that might be a reason why it''s not used, but that doesnt seem like a big enough reason, IMO. AFAIK, there is no reason why Rails must use DATETIME over TIMESTAMP except for the range of dates supported. Does anyone have a reason for why Rails uses DATETIME over TIMESTAMP for timestamp fields? I ran some tests and it looks like they behave the same within Rails. Aryk Hassan Schroeder wrote:> On Fri, Oct 31, 2008 at 10:48 AM, Aryk Grosz > <rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote: >> >> I haven''t seen anywhere in the mysql documentation that TIMESTAMP data >> type will automatically update itself. > > <http://dev.mysql.com/doc/refman/5.0/en/timestamp.html> > > -- > Hassan Schroeder ------------------------ hassan.schroeder-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org-- 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 -~----------~----~----~----~------~----~------~--~---
I stopped using TIMESTAMP due to the auto-updating that I was seeing in a real application. However, that was way back in version 3.x of MySQL. I did notice later that this behavior looks to have been changed. So I suppose if you need date/times fields that don''t require values before Jan 1, 1970 they are probably okay to use. But, I still don''t see much benefit in doing so over just using DATTIME. It makes sense to me for Rails to use DATETIME over TIMESTAMP. It''s simpler, and safer to do so. Aryk Grosz wrote:> Right, but that is not the reason why timestamps dont use TIMESTAMP. You > can always create the column without auto update functionality, > according to mysql''s website. You must always have a default value for a > timestamp, so that might be a reason why it''s not used, but that doesnt > seem like a big enough reason, IMO.-- 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 -~----------~----~----~----~------~----~------~--~---
The only real benefit is that it takes up half the space in the database (8 bytes instead of 4). There are also some querying benefits which a mysql expert could get into (easier to do range queries with, etc...). But yeah, if you have 100 mil rows in a table, using this will save you 4 bytes, or 400 MB which I guess you could argue is neglible in the grand scheme of things, but if do it across your database, it could save you a couple gigs, which isn''t too bad. Robert Walker wrote:> I stopped using TIMESTAMP due to the auto-updating that I was seeing in > a real application. However, that was way back in version 3.x of MySQL. > I did notice later that this behavior looks to have been changed. So I > suppose if you need date/times fields that don''t require values before > Jan 1, 1970 they are probably okay to use. But, I still don''t see much > benefit in doing so over just using DATTIME. It makes sense to me for > Rails to use DATETIME over TIMESTAMP. It''s simpler, and safer to do so. > > Aryk Grosz wrote: >> Right, but that is not the reason why timestamps dont use TIMESTAMP. You >> can always create the column without auto update functionality, >> according to mysql''s website. You must always have a default value for a >> timestamp, so that might be a reason why it''s not used, but that doesnt >> seem like a big enough reason, IMO.-- 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 -~----------~----~----~----~------~----~------~--~---
Woops, I meant 4 bytes instead of 8...its early...=) -Aryk -- 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 -~----------~----~----~----~------~----~------~--~---