datetime vs timestamp

rubyonrails-talk
1

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?

2

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.

3

In the case of MySQL there are significant differences between DATETIME and TIMESTAMP.

From MySQL documentation:

4

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.

5

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:

6

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.

7

<http://dev.mysql.com/doc/refman/5.0/en/timestamp.html&gt;

8

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:

9

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:

10

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:

11

Woops, I meant 4 bytes instead of 8...its early...=) -Aryk