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 [email protected] To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
