In the case of MySQL there are significant differences between DATETIME
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.
The TIMESTAMP data type in MySQL will automatically update itself if the
column is not explicitly set in an UPDATE statement:
| 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?