Rails' datetime and timestamp in a migration file are the same for MySQL and Sqlite3?

Question

:datetime and :timestamp in a migration file seems like the same in MySQL and Sqlite3, and they both map to datetime in the database side, except I can't find that in a formal documentation.

Also, what about when if our Rails project may use other DBMS, then should we use :datetime or :timestamp when we script/generate (or rails generate) our Model or Scaffold?

Solution

I would recommend using the :datetime, because so it's clear what to use. I believe rails is using DATETIME for both in the DB is because of the Problem that the datetimes representable with the unix timestamp or the MySQL TIMESTAMP field. Since timestamp is by default a 32bit Integer (see Wikipedia:Timestamp) it can only represent dates between 1901-12-13 (or 1970-01-01 if no negative values are allowed like in MySQL) and 2038-01-19. After or before that it will overflow. This is the year 2038 problem.

So to make it clear to everybody I would name it :datetime in the migration.

The TIMESTAMP data type has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 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. Source

Comments

[Radix]

Thanks @jigfox for this useful information. I'm wondering to know that Rails uses created_at and updated_at as timestamp. Shouldn't it be datetime as timestamp will create problem after 19th Jan, 2038?