datetime vs timestamp

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?

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.

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

From MySQL documentation:

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.

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:

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.

<http://dev.mysql.com/doc/refman/5.0/en/timestamp.html>

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:

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:

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:

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