MySQL DATE、TIME、DATETIME 和 TIMESTAMP 类型之间有什么区别

What difference between the DATE, TIME, DATETIME, and TIMESTAMP Types

提问by Shafizadeh

I need to store both timeand datein the mysql. So I used of NOW()function for that. But I don't know what should I use for type columnim phpmyadmin. It should be noted that NOW()returns both time and date like this:

我需要在 mysql 中存储时间和日期。所以我NOW()为此使用了函数。但我不知道我应该为phpmyadmin类型列使用什么。应该注意的是NOW()，像这样返回时间和日期：

2014-11-11 12:45:34

Here is a solution, I can use of a separator for separating date and time (2014-11-11and 12:45:34) and then store them in the DATE type and TIME type individually. Or I can use of VARCHAR type for storing both of them in one column. But I think these ways are not standard. what is standard type for storing both date and time ?

这是一个解决方案，我可以使用分隔符来分隔日期和时间（2014-11-11和12:45:34），然后将它们分别存储在 DATE 类型和 TIME 类型中。或者我可以使用 VARCHAR 类型将它们都存储在一列中。但我认为这些方式并不标准。存储日期和时间的标准类型是什么？

Here is my query: (also I don't know why NOW()function does not works)

这是我的查询：（我也不知道为什么NOW()函数不起作用）

INSERT INTO table (timedate) VALUES (NOW())

回答by Saty

DATE:It is used for values with a date part but no time part. MySQL retrieves and displays DATE values in YYYY-MM-DDformat. The supported range is 1000-01-01to 9999-12-31.

DATE：它用于具有日期部分但没有时间部分的值。MySQL 以YYYY-MM-DD格式检索和显示 DATE 值。支持的范围是1000-01-01到9999-12-31。

DATETIME:It is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in YYYY-MM-DD HH:MM:SSformat. The supported range is 1000-01-01 00:00:00to 9999-12-31 23:59:59.

DATETIME：它用于包含日期和时间部分的值。MySQL 以YYYY-MM-DD HH:MM:SS格式检索和显示 DATETIME 值。支持的范围是1000-01-01 00:00:00到9999-12-31 23:59:59。

TIMESTAMP:It is also used for values that contain both date and time parts, and includes the time zone. TIMESTAMP has a range of 1970-01-01 00:00:01UTC to 2038-01-19 03:14:07UTC.

TIMESTAMP：它还用于包含日期和时间部分的值，并包括时区。TIMESTAMP 的范围是1970-01-01 00:00:01UTC 到2038-01-19 03:14:07UTC。

TIME:Its values are in HH:MM:SSformat (or HHH:MM:SSformat for large hours values). TIME values may range from -838:59:59to 838:59:59. The hours part may be so large because the TIME type can be used not only to represent a time of day (which must be less than 24 hours), but also elapsed time or a time interval between two events (which may be much greater than 24 hours, or even negative).

TIME：其值采用HH:MM:SS格式（或HHH:MM:SS格式用于大小时值）。TIME 值范围从-838:59:59到838:59:59。小时部分可能如此之大，因为 TIME 类型不仅可以用于表示一天中的某个时间（必须小于 24 小时），还可以表示经过的时间或两个事件之间的时间间隔（可能远大于24 小时，甚至是负面的）。

回答by Booboo

I have a slightly different perspective on the difference between a DATETIMEand a TIMESTAMP. A DATETIMEstores a literal value of a date and time with no reference to any particular timezone. So, I can set a DATETIMEcolumn to a value such as '2019-01-16 12:15:00' to indicate precisely when my last birthday occurred. Was this Eastern Standard Time? Pacific Standard Time? Who knows? Where the current session time zone of the server comes into play occurs when you set a DATETIMEcolumn to some value such as NOW(). The value stored will be the current date and time using the current session time zone in effect. But once a DATETIMEcolumn has been set, it will display the same regardless of what the current session time zone is.

我对DATETIME和TIMESTAMP之间的区别的看法略有不同。甲DATETIME存储日期和时间与没有参考任何特定的时区的一个文字值。因此，我可以将DATETIME列设置为诸如“2019-01-16 12:15:00”之类的值，以准确指示我上一个生日的发生时间。这是东部标准时间吗？太平洋标准时间？谁知道？当您将DATETIME列设置为某个值（例如NOW() ）时，服务器的当前会话时区就会发挥作用。存储的值将是使用当前会话时区的当前日期和时间。但是一旦DATETIME列已设置，无论当前会话时区是什么，它都会显示相同的内容。

A TIMESTAMPcolumn on the other hand takes the '2019-01-16 12:15:00' value you are setting into it and interprets it in the current session time zone to compute an internal representation relative to 1/1/1970 00:00:00 UTC. When the column is displayed, it will be converted back for display based on whatever the current session time zone is. It's a useful fiction to think of a TIMESTAMPas taking the value you are setting and converting it from the current session time zone to UTC for storing and then converting it back to the current session time zone for displaying.

甲TIMESTAMP另一方面列占据您正在设置“2019年1月16日12时十五分○○秒”值到它并解释它在当前会话中时区的内部表示相对于计算到1/1/1970 00： 00:00 UTC。当该列显示时，它将根据当前会话时区的任何内容转换回显示。将TIMESTAMP视为获取您正在设置的值并将其从当前会话时区转换为 UTC 以进行存储，然后将其转换回当前会话时区以进行显示是一个有用的虚构。

If my server is in San Francisco but I am running an event in New York that starts on 9/1/1029 at 20:00, I would use a TIMESTAMPcolumn for holding the start time, set the session time zone to 'America/New York' and set the start time to '2009-09-01 20:00:00'. If I want to know whether the event has occurred or not, regardless of the current session time zone setting I can compare the start time with NOW(). Of course, for displaying in a meaningful way to a perspective customer, I wouldneed to set the correct session time zone. If I did not need to do time comparisons, then I would probably be better off just using a DATETIMEcolumn, which will display correctly (with an implied EST time zone) regardless of what the current session time zone is.

如果我的服务器在旧金山，但我正在纽约举办一个活动，该活动于 1029 年 9 月 1 日 20:00 开始，我将使用TIMESTAMP列来保存开始时间，将会话时区设置为“美国/纽约”并将开始时间设置为“2009-09-01 20:00:00”。如果我想知道事件是否发生，无论当前会话时区设置如何，我都可以将开始时间与NOW()进行比较。当然，对于以有意义的方式显示给客户的角度来看，我会需要设置正确的会话时区。如果我不需要进行时间比较，那么使用DATETIME可能会更好列，无论当前会话时区是什么，它都会正确显示（带有隐含的 EST 时区）。

TIMESTAMP LIMITATION

时间戳限制

The TIMESTAMPtype has a range of '1970-01-01 00:00:01' UTCto '2038-01-19 03:14:07' UTCand so it may not usable for your particular application. In that case you will have to use a DATETIMEtype. You will, of course, always have to be concerned that the current session time zone is set properly whenever you are using this type with date functions such as NOW().

该TIMESTAMP类型的范围为'1970-01-01 00:00:01 ' UTC到'2038-01-19 03:14:07' UTC，因此它可能不适用于您的特定应用程序。在这种情况下，您将不得不使用DATETIME类型。当然，每当您将此类型与日期函数（如NOW().

回答by Hossein Mobasher

Satydescribed the differences between them. For your practice, you can use datetimein order to keep the output of NOW().

Saty描述了它们之间的差异。对于您的练习，您可以使用datetime为了保持NOW().

For example:

例如：

CREATE TABLE Orders
(
  OrderId int NOT NULL,
  ProductName varchar(50) NOT NULL,
  OrderDate datetime NOT NULL DEFAULT NOW(),
  PRIMARY KEY (OrderId)
)

You can read more at w3schools.

您可以在w3schools阅读更多内容。