Datetime vs timestamp

Rails
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 G. 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:

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 G. 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?

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

Aryk G. 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.

6

On Fri, Oct 31, 2008 at 10:48 AM, Aryk G.
[email protected] 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 S. ------------------------ [email protected]

7

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 S. wrote:

On Fri, Oct 31, 2008 at 10:48 AM, Aryk G.
[email protected] 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 S. ------------------------ [email protected]

8

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 K. 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.

9

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 W. 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 G. 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.

10

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 G. 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.

11

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