我应该在 MySQL 中使用日期时间还是时间戳数据类型？

Should I use the datetime or timestamp data type in MySQL?

提问by karlipoppins

Would you recommend using a datetimeor a timestampfield, and why (using MySQL)?

你会推荐使用日期时间还是时间戳字段，为什么（使用 MySQL）？

I'm working with PHP on the server side.

我正在服务器端使用 PHP。

采纳答案by blivet

Timestamps in MySQL are generally used to track changes to records, and are often updated every time the record is changed. If you want to store a specific value you should use a datetime field.

MySQL 中的时间戳通常用于跟踪记录的更改，并且通常在每次记录更改时更新。如果要存储特定值，则应使用日期时间字段。

If you meant that you want to decide between using a UNIX timestamp or a native MySQL datetime field, go with the native format. You can do calculations within MySQL that way ("SELECT DATE_ADD(my_datetime, INTERVAL 1 DAY)")and it is simple to change the format of the value to a UNIX timestamp ("SELECT UNIX_TIMESTAMP(my_datetime)")when you query the record if you want to operate on it with PHP.

如果您的意思是要在使用 UNIX 时间戳或本机 MySQL 日期时间字段之间做出决定，请使用本机格式。您可以通过这种方式在 MySQL 中进行计算， 如果您想使用 PHP 对其进行操作，则在查询记录时("SELECT DATE_ADD(my_datetime, INTERVAL 1 DAY)")将值的格式更改为 UNIX 时间戳很简单("SELECT UNIX_TIMESTAMP(my_datetime)")。

回答by Nir

In MySQL 5 and above, TIMESTAMPvalues are converted from the current time zone to UTC for storage, and converted back from UTC to the current time zone for retrieval. (This occurs only for the TIMESTAMP data type, and notfor other types such as DATETIME.)

在 MySQL 5 及以上版本中，TIMESTAMP值从当前时区转换为 UTC 进行存储，并从 UTC 转换回当前时区以进行检索。（这仅适用于 TIMESTAMP 数据类型，不适用于其他类型，例如 DATETIME。）

By default, the current time zone for each connection is the server's time. The time zone can be set on a per-connection basis, as described in MySQL Server Time Zone Support.

默认情况下，每个连接的当前时区是服务器的时间。可以在每个连接的基础上设置时区，如MySQL 服务器时区支持中所述。

回答by scronide

I always use DATETIME fields for anything other than row metadata (date created or modified).

我总是将 DATETIME 字段用于除行元数据（创建或修改日期）以外的任何内容。

As mentionedin the MySQL documentation:

至于提到的MySQL文档中：

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.

当您需要包含日期和时间信息的值时，使用 DATETIME 类型。MySQL 以 'YYYY-MM-DD HH:MM:SS' 格式检索和显示 DATETIME 值。支持的范围是“1000-01-01 00:00:00”到“9999-12-31 23:59:59”。

...

TIMESTAMP 数据类型的范围是 '1970-01-01 00:00:01' UTC 到 '2038-01-09 03:14:07' UTC。它具有不同的属性，具体取决于 MySQL 版本和服务器运行的 SQL 模式。

You're quite likely to hit the lower limit on TIMESTAMPs in general use -- e.g. storing birthdate.

在一般使用中，您很可能会达到 TIMESTAMP 的下限——例如​​存储生日。

回答by mr_eclair

The below examples show how the TIMESTAMPdate type changed the values after changing the time-zone to 'america/new_york'where DATETIMEis unchanged.

下面的示例显示了TIMESTAMP日期类型如何在更改time-zone to 'america/new_york'whereDATETIME不变后更改值。

mysql> show variables like '%time_zone%';
+------------------+---------------------+
| Variable_name    | Value               |
+------------------+---------------------+
| system_time_zone | India Standard Time |
| time_zone        | Asia/Calcutta       |
+------------------+---------------------+

mysql> create table datedemo(
    -> mydatetime datetime,
    -> mytimestamp timestamp
    -> );

mysql> insert into datedemo values ((now()),(now()));

mysql> select * from datedemo;
+---------------------+---------------------+
| mydatetime          | mytimestamp         |
+---------------------+---------------------+
| 2011-08-21 14:11:09 | 2011-08-21 14:11:09 |
+---------------------+---------------------+

mysql> set time_zone="america/new_york";

mysql> select * from datedemo;
+---------------------+---------------------+
| mydatetime          | mytimestamp         |
+---------------------+---------------------+
| 2011-08-21 14:11:09 | 2011-08-21 04:41:09 |
+---------------------+---------------------+

I've converted my answer into article so more people can find this useful, MySQL: Datetime Versus Timestamp Data Types.

我已将我的答案转换为文章，以便更多人可以发现它很有用，MySQL: Datetime Versus Timestamp Data Types。

回答by ekerner

The main difference is that DATETIME is constant while TIMESTAMP is affected by the time_zonesetting.

主要区别在于 DATETIME 是常数，而 TIMESTAMP 受time_zone设置影响。

So it only matters when you have — or may in the future have — synchronized clusters across time zones.

因此，只有当您拥有（或将来可能拥有）跨时区同步集群时才重要。

In simpler words: If I have a database in Australia, and take a dump of that database to synchronize/populate a database in America, then the TIMESTAMP would update to reflect the real time of the event in the new time zone, while DATETIME would still reflect the time of the event in the au time zone.

简单来说：如果我在澳大利亚有一个数据库，并转储该数据库以同步/填充美国的数据库，那么 TIMESTAMP 将更新以反映新时区中事件的实时时间，而 DATETIME 将更新仍然反映 au 时区中事件的时间。

A great example of DATETIME being used where TIMESTAMP should have been used is in Facebook, where their servers are never quite sure what time stuff happened across time zones. Once I was having a conversation in which the time said I was replying to messages before the message was actually sent. (This, of course, could also have been caused by bad time zone translation in the messaging software if the times were being posted rather than synchronized.)

在应该使用 TIMESTAMP 的地方使用 DATETIME 的一个很好的例子是在 Facebook 中，他们的服务器从不完全确定跨时区发生的时间。有一次我进行了一次对话，其中时间说我在消息实际发送之前回复了消息。（当然，如果时间被发布而不是同步，这也可能是由消息传递软件中的错误时区转换引起的。）

回答by ekerner

I make this decision on a semantic base.

我根据语义做出这个决定。

I use a timestamp when I need to record a (more or less) fixed point in time. For example when a record was inserted into the database or when some user action took place.

当我需要记录（或多或少）固定时间点时，我会使用时间戳。例如，当一条记录被插入到数据库中或发生了一些用户操作时。

I use a datetime field when the date/time can be set and changed arbitrarily. For example when a user can save later change appointments.

当日期/时间可以任意设置和更改时，我使用日期时间字段。例如，当用户可以保存以后更改约会时。

回答by user64141

I recommend using neithera DATETIME or a TIMESTAMP field. If you want to represent a specific day as a whole (like a birthday), then use a DATE type, but if you're being more specific than that, you're probably interested in recording an actual moment as opposed to a unit of time (day,week,month,year). Instead of using a DATETIME or TIMESTAMP, use a BIGINT, and simply store the number of milliseconds since the epoch (System.currentTimeMillis() if you're using Java). This has several advantages:

我建议使用既不datetime或时间戳字段。如果您想将特定的一天表示为一个整体（如生日），则使用 DATE 类型，但如果您比这更具体，您可能对记录实际时刻而不是一个单位感兴趣时间（日、周、月、年）。不使用 DATETIME 或 TIMESTAMP，而是使用 BIGINT，并简单地存储自纪元以来的毫秒数（System.currentTimeMillis()，如果您使用的是 Java）。这有几个优点：

1. You avoid vendor lock-in. Pretty much every database supports integers in the relatively similar fashion. Suppose you want to move to another database. Do you want to worry about the differences between MySQL's DATETIME values and how Oracle defines them? Even among different versions of MySQL, TIMESTAMPS have a different level of precision. It was only just recently that MySQL supported milliseconds in the timestamps.
2. No timezone issues. There's been some insightful comments on here on what happens with timezones with the different data types. But is this common knowledge, and will your co-workers all take the time to learn it? On the other hand, it's pretty hard to mess up changing a BigINT into a java.util.Date. Using a BIGINT causes a lot of issues with timezones to fall by the wayside.
3. No worries about ranges or precision. You don't have to worry about what being cut short by future date ranges (TIMESTAMP only goes to 2038).
4. Third-party tool integration. By using an integer, it's trivial for 3rd party tools (e.g. EclipseLink) to interface with the database. Not every third-party tool is going to have the same understanding of a "datetime" as MySQL does. Want to try and figure out in Hibernate whether you should use a java.sql.TimeStamp or java.util.Date object if you're using these custom data types? Using your base data types make's use with 3rd-party tools trivial.

1. 您可以避免供应商锁定。几乎每个数据库都以相对相似的方式支持整数。假设您要移动到另一个数据库。您是否要担心 MySQL 的 DATETIME 值与 Oracle 如何定义它们之间的差异？即使在不同版本的 MySQL 中，TIMESTAMPS 的精度水平也不同。直到最近，MySQL 才支持时间戳中的毫秒。
2. 没有时区问题。关于不同数据类型的时区会发生什么，这里有一些有见地的评论。但这是常识吗，你的同事都会花时间学习吗？另一方面，很难将 BigINT 更改为 java.util.Date。使用 BIGINT 会导致很多时区问题被搁置。
3. 无需担心范围或精度。您不必担心未来日期范围会缩短什么时间（TIMESTAMP 仅到 2038 年）。
4. 第三方工具集成。通过使用整数，第 3 方工具（例如 EclipseLink）与数据库的接口是微不足道的。并非每个第三方工具都会像 MySQL 那样对“日期时间”有相同的理解。想要尝试在 Hibernate 中弄清楚如果您正在使用这些自定义数据类型，是否应该使用 java.sql.TimeStamp 或 java.util.Date 对象？使用您的基本数据类型使得与 3rd-party 工具的使用无关紧要。

This issue is closely related how you should store a money value (i.e. $1.99) in a database. Should you use a Decimal, or the database's Money type, or worst of all a Double? All 3 of these options are terrible, for many of the same reasons listed above. The solution is to store the value of money in cents using BIGINT, and then convert cents to dollars when you display the value to the user. The database's job is to store data, and NOT to intrepret that data. All these fancy data-types you see in databases(especially Oracle) add little, and start you down the road to vendor lock-in.

这个问题与您应该如何在数据库中存储货币价值（即 1.99 美元）密切相关。您应该使用 Decimal 或数据库的 Money 类型，还是最糟糕的 Double 类型？由于上面列出的许多相同原因，所有这三个选项都很糟糕。解决方案是使用 BIGINT 以美分存储货币的价值，然后在向用户显示价值时将美分转换为美元。数据库的工作是存储数据，而不是解释这些数据。您在数据库（尤其是 Oracle）中看到的所有这些花哨的数据类型几乎没有增加，并让您走上供应商锁定之路。

回答by Alex

TIMESTAMP is 4 bytes Vs 8 bytes for DATETIME.

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

http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html

http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html

But like scronide said it does have a lower limit of the year 1970. It's great for anything that might happen in the future though ;)

但是就像 scronide 所说的那样，它确实有 1970 年的下限。不过，这对于将来可能发生的任何事情都很好；)

回答by Vivek S

1. TIMESTAMP is four bytes vs eight bytes for DATETIME.

2. Timestamps are also lighter on the database and indexed faster.

3. 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′.

1. TIMESTAMP 是 4 个字节，而 DATETIME 是 8 个字节。

2. 数据库上的时间戳也更轻，索引速度更快。

3. 当您需要包含日期和时间信息的值时，使用 DATETIME 类型。MySQL 以 'YYYY-MM-DD HH:MM:SS' 格式检索和显示 DATETIME 值。支持的范围是“1000-01-01 00:00:00”到“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.

TIMESTAMP 数据类型的范围是 '1970-01-01 00:00:01' UTC 到 '2038-01-09 03:14:07' UTC。它具有不同的属性，具体取决于 MySQL 版本和服务器运行的 SQL 模式。

1. DATETIME is constant while TIMESTAMP is effected by the time_zone setting.

4. DATETIME 是常量，而 TIMESTAMP 受 time_zone 设置的影响。

回答by ianaré

Depends on application, really.

取决于应用程序，真的。

Consider setting a timestamp by a user to a server in New York, for an appointment in Sanghai. Now when the user connects in Sanghai, he accesses the same appointment timestamp from a mirrored server in Tokyo. He will see the appointment in Tokyo time, offset from the original New York time.

考虑将用户设置为纽约服务器的时间戳，以便在 Sanghai 进行约会。现在，当用户在 Sanghai 连接时，他从东京的镜像服务器访问相同的约会时间戳。他将在东京时间看到约会，与原来的纽约时间有偏差。

So for values that represent user time like an appointment or a schedule, datetime is better. It allows the user to control the exact date and time desired, regardless of the server settings. The set time is the set time, not affected by the server's time zone, the user's time zone, or by changes in the way daylight savings time is calculated (yes it does change).

因此，对于代表用户时间（如约会或日程安排）的值，日期时间更好。无论服务器设置如何，它都允许用户控制所需的确切日期和时间。设置时间是设置时间，不受服务器时区、用户时区或夏令时计算方式变化的影响（是的，它确实发生了变化）。

On the other hand, for values that represent system time like payment transactions, table modifications or logging, always use timestamps. The system will not be affected by moving the server to another time zone, or when comparing between servers in different timezones.

另一方面，对于代表系统时间的值，如支付交易、表修改或日志记录，始终使用时间戳。将服务器移动到另一个时区，或在不同时区的服务器之间进行比较时，系统不会受到影响。

Timestamps are also lighter on the database and indexed faster.

数据库上的时间戳也更轻，索引速度更快。