在 Ruby on Rails 中，DateTime、Timestamp、Time 和 Date 之间有什么区别？

In Ruby on Rails, what's the difference between DateTime, Timestamp, Time and Date?

提问by Nick May

In my experience, getting dates/times right when programming is always fraught with danger and difficulity.

根据我的经验，在编程时获取正确的日期/时间总是充满危险和困难。

Ruby and Rails have always eluded me on this one, if only due to the overwhelming number of options; I never have any idea which I should pick.

Ruby 和 Rails 在这个问题上总是让我望而却步，只是因为选项太多了。我从来不知道我应该选择哪个。

When I'm using Rails and looking at ActiveRecord datatypes I can find the following

当我使用 Rails 并查看 ActiveRecord 数据类型时，我可以找到以下内容

:datetime, :timestamp, :time, and :date

:datetime、:timestamp、:time 和 :date

and have no idea what the differences are or where the gotchas lurk.

并且不知道有什么区别或陷阱潜伏在哪里。

What's the difference? What do you use them for?

有什么不同？你用它们做什么？

(P.S. I'm using Rails3)

（PS 我正在使用 Rails3）

回答by vonconrad

The difference between different date/time formats in ActiveRecord has little to do with Rails and everything to do with whatever database you're using.

ActiveRecord 中不同日期/时间格式之间的差异与 Rails 无关，而与您使用的任何数据库有关。

Using MySQL as an example (if for no other reason because it's most popular), you have DATE, DATETIME, TIMEand TIMESTAMPcolumn data types; just as you have CHAR, VARCHAR, FLOATand INTEGER.

使用MySQL作为一个例子（如果没有其他原因，因为它是最流行的），你有DATE，DATETIME，TIME和TIMESTAMP列数据类型; 就像你有CHAR, VARCHAR,FLOAT和 一样INTEGER。

So, you ask, what's the difference? Well, some of them are self-explanatory. DATEonly stores a date, TIMEonly stores a time of day, while DATETIMEstores both.

所以，你问，有什么区别？嗯，其中一些是不言自明的。DATE只存储日期，TIME只存储一天中的时间，同时DATETIME存储两者。

The difference between DATETIMEand TIMESTAMPis a bit more subtle: DATETIMEis formatted as YYYY-MM-DD HH:MM:SS. Valid ranges go from the year 1000 to the year 9999 (and everything in between. While TIMESTAMPlookssimilar when you fetch it from the database, it's really a just a front for a unix timestamp. Its valid range goes from 1970 to 2038. The difference here, aside from the various built-in functions within the database engine, is storage space. Because DATETIMEstores every digit in the year, month day, hour, minute and second, it uses up a total of 8 bytes. As TIMESTAMPonly stores the number of seconds since 1970-01-01, it uses 4 bytes.

DATETIME和之间的区别TIMESTAMP有点微妙：DATETIME格式为YYYY-MM-DD HH:MM:SS. 有效范围从 1000 年到 9999 年（以及两者之间的所有内容。虽然从数据库中获取它时TIMESTAMP看起来很相似，但它实际上只是unix timestamp 的一个前端。它的有效范围从 1970 年到 2038 年。差异这里除了数据库引擎内置的各种功能外，就是存储空间，因为DATETIME存储年月日时分秒的每一位数字，一共占用8个字节，因为TIMESTAMP只存储数字自 1970-01-01 以来的秒数，它使用 4 个字节。

You can read more about the differences between time formats in MySQL here.

您可以在此处阅读有关 MySQL 中时间格式之间差异的更多信息。

In the end, it comes down to what you need your date/time column to do. Do you need to store dates and times before 1970 or after 2038? Use DATETIME. Do you need to worry about database size and you're within that timerange? Use TIMESTAMP. Do you only need to store a date? Use DATE. Do you only need to store a time? Use TIME.

最后，它归结为您需要日期/时间列执行的操作。您是否需要存储 1970 年之前或 2038 年之后的日期和时间？使用DATETIME. 您是否需要担心数据库大小并且您在该时间范围内？使用TIMESTAMP. 你只需要存储一个日期吗？使用DATE. 你只需要存储一个时间吗？使用TIME.

Having said all of this, Rails actually makes some of these decisions for you. Both :timestampand :datetimewill default to DATETIME, while :dateand :timecorresponds to DATEand TIME, respectively.

说了这么多，Rails 实际上为您做出了一些决定。双方:timestamp并:datetime会默认为DATETIME，同时，:date和:time对应于DATE和TIME分别。

This means that within Rails, you only have to decide whether you need to store date, time or both.

这意味着在 Rails 中，您只需决定是否需要存储日期、时间或两者。

回答by mingca

1. :datetime (8 bytes)

   • Stores Date and Time formatted YYYY-MM-DD HH:MM:SS
   • Useful for columns like birth_date

2. :timestamp (4 bytes)

   • Stores number of seconds since 1970-01-01
   • Useful for columns like updated_at, created_at
3. :date (3 bytes)
   • Stores Date
4. :time (3 bytes)
   • Stores Time

1. :datetime (8 字节)

   • 存储格式为 YYYY-MM-DD HH:MM:SS 的日期和时间
   • 对诸如birth_date之类的列很有用

2. :timestamp (4 字节)

   • 存储自 1970-01-01 以来的秒数
   • 对诸如 updated_at、created_at 之类的列很有用
3. ：日期（3 个字节）
   • 店铺日期
4. ：时间（3 个字节）
   • 商店时间

回答by Stanislav Modrák

Here is an awesome and preciseexplanation I found.

这是我发现的一个很棒且准确的解释。

TIMESTAMP used to track changes of records, and update every time when the record is changed. DATETIME used to store specific and static value which is not affected by any changes in records.

TIMESTAMP also affected by different TIME ZONE related setting. DATETIME is constant.

TIMESTAMP internally converted a current time zone to UTC for storage, and during retrieval convert the back to the current time zone. DATETIME can not do this.

TIMESTAMP is 4 bytes and DATETIME is 8 bytes.

TIMESTAMP supported range: ‘1970-01-01 00:00:01′ UTC to ‘2038-01-19 03:14:07′ UTC DATETIME supported range: ‘1000-01-01 00:00:00′ to ‘9999-12-31 23:59:59′

TIMESTAMP 用于跟踪记录的变化，并在每次记录发生变化时更新。DATETIME 用于存储不受任何记录更改影响的特定和静态值。

TIMESTAMP 也受不同 TIME ZONE 相关设置的影响。DATETIME 是常数。

TIMESTAMP 在内部将当前时区转换为 UTC 进行存储，并在检索期间将其转换回当前时区。DATETIME 不能这样做。

TIMESTAMP 为 4 个字节，DATETIME 为 8 个字节。

TIMESTAMP 支持范围：'1970-01-01 00:00:01' UTC 到 '2038-01-19 03:14:07' UTC DATETIME 支持范围：'1000-01-01 00:00:00' 到 '9999 -12-31 23:59:59'

source: https://www.dbrnd.com/2015/09/difference-between-datetime-and-timestamp-in-mysql/#:~:text=DATETIME%20vs%20TIMESTAMP%3A,DATETIME%20is%20constant.

来源：https: //www.dbrnd.com/2015/09/difference-between-datetime-and-timestamp-in-mysql/#:~: text=DATETIME%20vs%20TIMESTAMP%3A,DATETIME%20is%20constant。

Also...

还...

table with different column "date" types and corresponding rails migration types depending on the database

根据数据库具有不同列“日期”类型和相应的 rails 迁移类型的表