我应该在MySQL中使用datetime或timestamp数据类型吗?

Would you recommend using a datetime or a timestamp field, and why (using MySQL)?

您是否建议使用datetime或timestamp字段，以及为什么(使用MySQL)?

I'm working with PHP on the server side.

我在服务器端使用PHP。

33 个解决方案

#1

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中的时间戳通常用于跟踪记录的更改，并且每次更改记录时都会更新。如果要存储特定值，则应使用datetime字段。

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 datetime字段之间做出决定，请使用本机格式。您可以在MySQL中进行计算(“选择DATE_ADD(my_datetime, INTERVAL 1 DAY)”)，如果您想使用PHP操作，则可以将该值的格式更改为UNIX时间戳(“SELECT UNIX_TIMESTAMP(my_datetime)”)。

#2

In MySQL 5 and above, TIMESTAMP values 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 not for other types such as DATETIME.)

在MySQL 5和上面，时间戳值从当前时区转换为UTC存储，并从UTC转换回当前时区进行检索。(这只发生于时间戳数据类型，而不是其他类型，如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服务器时区支持所述。

#3

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

除了行元数据(创建或修改的日期)之外，我总是使用DATETIME字段。

As mentioned in 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'.

当您需要包含日期和时间信息的值时，使用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.

时间戳数据类型的范围为'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.

一般来说，你很有可能达到时间戳的下限——比如存储生日。

#4

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

下面的示例显示了时间戳日期类型如何在将时区更改为“america/new_york”之后更改了值。

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和时间戳数据类型。

#5

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

主要区别是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.

用简单的话说:如果我在澳大利亚有一个数据库,和大便的数据库同步/填充数据库在美国,时间戳会实时更新,以反映事件的新时区,而DATETIME仍反映在非盟时区的时间。

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.)

时间戳应该被使用的一个很好的例子是在Facebook中，他们的服务器永远不知道跨时区所发生的时间。有一次，我正在进行一段对话，其中的时间说我在邮件发送之前回复邮件。(当然，这也可能是由于消息传递软件中出现了错误的时区翻译，而不是同步的。)

#6

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.

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

#7

TIMESTAMP is 4 bytes Vs 8 bytes for DATETIME.

时间戳是4字节对8字节的DATETIME。

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年。它对未来可能发生的任何事情都很好;

#8

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

   时间戳是四个字节，而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′.

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

时间戳数据类型的“1970-01-01 00:00:01′UTC“2038-01-09 03:14:07′UTC。它有不同的属性，取决于MySQL版本和服务器运行的SQL模式。

4. DATETIME is constant while TIMESTAMP is effected by the time_zone setting.
5. 日期时间是常量，时间戳是由time_zone设置完成的。

#9

I recommend using neither a 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或TIMESTAMP字段。如果你想要代表一个特定的一天(比如生日)，那么就使用日期类型，但是如果你比这更具体，你可能对记录一个实际的时刻而不是一个时间单位(天、周、月、年)感兴趣。与其使用DATETIME或TIMESTAMP，不如使用BIGINT，并简单地存储自epoch以来的毫秒数(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. 你避免厂商锁定。几乎每个数据库都以相对类似的方式支持整数。假设您想移动到另一个数据库。您是否想要担心MySQL的DATETIME值与Oracle如何定义它们之间的差异?即使在不同版本的MySQL中，时间戳的精确度也不同。只是在最近，MySQL在时间戳中支持毫秒。
3. 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.
4. 没有时区的问题。这里有一些很有见地的评论，关于不同数据类型的时区会发生什么。但这是常识吗?你的同事们会花时间去学习吗?另一方面，将BigINT转换为java.util.Date是非常困难的。使用BIGINT会导致很多时区的问题。
5. 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).
6. 不必担心范围或精度。您不必担心在未来的日期范围内被缩短的时间(时间戳只到2038年)。
7. 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.
8. 第三方工具的集成。通过使用整数，第三方工具(例如EclipseLink)与数据库的接口是微不足道的。并不是所有的第三方工具都能像MySQL那样理解“datetime”。要尝试在Hibernate中计算是否应该使用java.sql。时间戳或java.util。如果您使用这些自定义数据类型，那么Date对象?使用您的基本数据类型使用第三方工具。

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美元)密切相关。你应该使用十进制，还是数据库的货币类型，还是最坏的一倍?上述三种选择都很糟糕，原因与上面列出的许多相同。解决方案是使用BIGINT将货币的价值存储在美分中，然后在向用户显示值时将美分转换为美元。数据库的工作是存储数据，而不是对数据进行冒险。你在数据库中看到的所有这些花哨的数据类型(尤其是Oracle)都没有增加什么，并且让你从通往厂商锁定的道路开始。

#10

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).

因此，对于表示用户时间的值，比如预约或调度，datetime更好。它允许用户控制所需的确切日期和时间，而不考虑服务器设置。设置的时间是设置的时间，不受服务器时区、用户时区的影响，也不受夏令时的计算方式的改变(是的，它确实改变了)。

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.

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

#11

A timestamp field is a special case of the datetime field. You can create timestamp columns to have special properties; it can be set to update itself on either create and/or update.

时间戳字段是datetime字段的一个特例。您可以创建具有特殊属性的时间戳列;它可以被设置为在创建和/或更新上更新自己。

In "bigger" database terms, timestamp has a couple of special-case triggers on it.

在“更大”的数据库术语中，timestamp有几个特殊的触发器。

What the right one is depends entirely on what you want to do.

正确的选择完全取决于你想做什么。

#12

TIMESTAMP is always in UTC (that is, elapsed seconds since 1970-01-01, in UTC), and your MySQL server auto-converts it to the date/time for the server timezone. In the long-term, TIMESTAMP is the way to go because you know your temporal data will always be in UTC. For example, you won't screw your dates up if you migrate to a different server or if you change the timezone settings on your server.

时间戳总是在UTC中(也就是说，从1970-01-01开始，在UTC中)，而MySQL服务器自动将其转换为服务器时区的日期/时间。从长远来看，时间戳是一种方法，因为你知道你的时间数据总是在UTC。例如，如果您迁移到另一个服务器，或者更改服务器上的时区设置，您就不会把日期弄错。

#13

2016 +: what I advise is to set your Mysql timezone to UTC and use DATETIME:

Any recent front-end framework (Angular 1/2, react, Vue,...) can easily and automatically convert your UTC datetime to local time.

任何最近的前端框架(角1/2,react, Vue，…)都可以很容易地将UTC datetime自动转换为本地时间。

Additionally:

另外:

• DATETIME can now be automatically set to the current time value How do you set a default value for a MySQL Datetime column?
• DATETIME现在可以自动设置为当前时间值，如何为MySQL DATETIME列设置默认值?
• Contrary to what one might think, DATETIME is FASTER THAN TIMESTAMP, http://gpshumano.blogs.dri.pt/2009/07/06/mysql-datetime-vs-timestamp-vs-int-performance-and-benchmarking-with-myisam/
• 与人们可能认为的相反，DATETIME比时间戳更快，http://gpshumano.blogs.dri.pt/2009/07/07/07/06/mysql - DATETIME -vs-timestamp-vs- performance- benchmarkingwith-myisam/。
• TIMESTAMP is still limited to 1970-2038
• 时间戳仍然限制在1970-2038年。

(Unless you are likely to change the timezone of your servers)

(除非您可能更改服务器的时区)

---

Example with AngularJs

例子与AngularJs

// back-end: format for angular within the sql query
SELECT DATE_FORMAT(my_datetime, "%Y-%m-%dT%TZ")...

// font-end Output the localised time
{{item.my_datetime | date :'medium' }}

All localised time format available here: https://docs.angularjs.org/api/ng/filter/date

所有本地化的时间格式在这里:https://docs.angularjs.org/api/ng/filter/date。

#14

It is worth noting in MySQL you can use something along the lines of the below when creating your table columns:

在MySQL中值得注意的是，在创建表列时，可以使用下面的行:

on update CURRENT_TIMESTAMP

This will update the time at each instance you modify a row and is sometimes very helpful for stored last edit information. This only works with timestamp, not datetime however.

这将更新每个实例的时间，并且有时对存储最后的编辑信息非常有用。这只适用于时间戳，而不是datetime。

#15

I would always use a Unix timestamp when working with MySQL and PHP. The main reason for this being the the default date method in PHP uses a timestamp as the parameter, so there would be no parsing needed.

在使用MySQL和PHP时，我总是使用Unix时间戳。这是因为PHP中的默认日期方法使用时间戳作为参数，因此不需要解析。

To get the current Unix timestamp in PHP, just do time();
and in MySQL do SELECT UNIX_TIMESTAMP();.

要在PHP中获得当前的Unix时间戳，只需执行时间();在MySQL中，选择UNIX_TIMESTAMP();

#16

Comparison between DATETIME, TIMESTAMP and DATE

DATETIME、时间戳和日期的比较。

What is that [.fraction]?

这是什么(.fraction)?

• A DATETIME or TIMESTAMP value can include a trailing fractional seconds part in up to microseconds (6 digits) precision. In particular, any fractional part in a value inserted into a DATETIME or TIMESTAMP column is stored rather than discarded. This is of course optional.
• 一个DATETIME或TIMESTAMP值可以包括在微秒(6位)精度上的尾随分数秒部分。特别是，将值插入到DATETIME或TIMESTAMP列中的任何小数部分都是存储而不是丢弃的。这当然是可选的。

Sources:

来源:

• MySQL Date/Time data types reference
• MySQL日期/时间数据类型引用。
• MySQL Storage Requirements reference
• MySQL存储需求参考

#17

From my experiences, if you want a date field in which insertion happens only once and you don't want to have any update or any other action on that particular field, go with date time.

根据我的经验，如果你想要一个只发生一次插入的日期字段，而你不想在那个特定的字段上有任何更新或任何其他操作，那就使用date time。

For example, consider a user table with a REGISTRATION DATE field. In that user table, if you want to know the last logged in time of a particular user, go with a field of timestamp type so that the field gets updated.

例如，考虑一个具有注册日期字段的用户表。在该用户表中，如果您想知道某个特定用户的最后一次登录，请使用timestamp类型的字段，以便该字段得到更新。

If you are creating the table from phpMyAdmin the default setting will update the timestamp field when a row update happens. If your timestamp filed is not updating with row update, you can use the following query to make a timestamp field get auto updated.

如果从phpMyAdmin创建表，默认设置将在发生行更新时更新timestamp字段。如果您的时间戳没有更新到行更新，您可以使用以下查询来创建一个时间戳字段，使其自动更新。

ALTER TABLE your_table
      MODIFY COLUMN ts_activity TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

#18

The timestamp data type stores date and time, but in UTC format, not in the current timezone format as datetime does. And when you fetch data, timestamp again converts that into the current timezone time.

时间戳数据类型存储日期和时间，但在UTC格式中，不像datetime那样在当前时区格式中存储。当您获取数据时，timestamp再次将其转换为当前时区时间。

So suppose you are in USA and getting data from a server which has a time zone of USA. Then you will get the date and time according to the USA time zone. The timestamp data type column always get updated automatically when its row gets updated. So it can be useful to track when a particular row was updated last time.

假设你在美国，从一个拥有美国时区的服务器获取数据。然后你将根据美国时区得到日期和时间。时间戳数据类型列总是在它的行被更新时自动更新。因此，在上次更新某一行时，跟踪它是很有用的。

For more details you can read the blog post Timestamp Vs Datetime .

有关更多细节，您可以阅读博客post Timestamp Vs Datetime。

#19

I always use a Unix timestamp, simply to maintain sanity when dealing with a lot of datetime information, especially when performing adjustments for timezones, adding/subtracting dates, and the like. When comparing timestamps, this excludes the complicating factors of timezone and allows you to spare resources in your server side processing (Whether it be application code or database queries) in that you make use of light weight arithmetic rather then heavier date-time add/subtract functions.

我总是使用Unix时间戳，只是为了在处理大量的datetime信息时保持明智，特别是在对时区进行调整、添加/删除日期之类的时候。在比较时间戳时，这排除了时区的复杂因素，并允许您在服务器端处理(无论是应用程序代码还是数据库查询)中节省资源，因为您使用的是轻量级的算术，而不是更重的date-time添加/减函数。

Another thing worth considering:

另一件事值得考虑:

If you're building an application, you never know how your data might have to be used down the line. If you wind up having to, say, compare a bunch of records in your data set, with, say, a bunch of items from a third-party API, and say, put them in chronological order, you'll be happy to have Unix timestamps for your rows. Even if you decide to use MySQL timestamps, store a Unix timestamp as insurance.

如果您正在构建一个应用程序，您永远不知道您的数据将如何被使用。如果你不得不，比方说，比较你的数据集中的一堆记录，比如说，从第三方的API中收集一堆东西，然后说，按时间顺序排列，你会很高兴为你的行有Unix时间戳。即使您决定使用MySQL时间戳，也要将Unix时间戳存储为保险。

#20

Beware of timestamp changing when you do a UPDATE statement on a table. If you have a table with columns 'Name' (varchar), 'Age' (int), and 'Date_Added' (timestamp) and you run the following DML statement

当您在表上执行UPDATE语句时，要注意时间戳的变化。如果您有一个具有列“名称”(varchar)、“Age”(int)和“date_add”(时间戳)的表，您可以运行以下DML语句。

UPDATE table
SET age = 30

then every single value in your 'Date_Added' column would be changed to the current timestamp.

然后，“Date_Added”列中的每一个值都将更改为当前时间戳。

#21

The major difference is

主要的区别是

• a INDEX's on Timestamp - works
• 一个索引的时间戳-工作。
• a INDEX's on Datetime - Does not work
• 索引在Datetime上是无效的。

look at this post to see problems with Datetime indexing

查看这篇文章，查看Datetime索引的问题。

#22

Another difference between Timestamp and Datetime is in Timestamp you can't default value to NULL.

时间戳和Datetime之间的另一个区别是时间戳，您不能默认值为NULL。

#23

I found unsurpassed usefulness in TIMESTAMP's ability to auto update itself based on the current time without the use of unnecessary triggers. That's just me though, although TIMESTAMP is UTC like it was said.

我发现在TIMESTAMP的自动更新功能上，没有使用不必要的触发器，这是非常有用的。不过这只是我的想法，虽然时间戳是UTC，就像它说的那样。

It can keep track across different timezones, so if you need to display a relative time for instance, UTC time is what you would want.

它可以跟踪不同的时区，因此，如果您需要显示一个相对时间，那么UTC时间就是您想要的。

#24

Reference taken from this Article:

从本文中获取的参考资料:

The main differences:

主要差异:

TIMESTAMP used to track changes to 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.

用于跟踪记录更改的时间戳，并在记录更改时每次更新。DATETIME用于存储特定的和静态的值，这些值不受记录变化的影响。

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

时间戳也受不同时区相关设置的影响。DATETIME是恒定的。

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

时间戳将当前时区内部转换为UTC存储，在检索时转换回当前时区。DATETIME不能这样做。

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′

时间戳支持的范围:“1970-01-01 -01”UTC到2038-01-19 03:14:07 UTC DATETIME支持范围:“1000-01-01 00:00”到“9999-12-31 23:59:59”。

#25

I prefer using timestamp so to keep everything in one common raw format and format the data in PHP code or in your SQL query. There are instances where it comes in handy in your code to keep everything in plain seconds.

我更喜欢使用时间戳，这样可以将所有东西都保存在一个常见的原始格式中，并在PHP代码或SQL查询中格式化数据。有一些例子，它在你的代码中派上用场，以保持一切正常。

#26

I like a Unix timestamp, because you can convert to numbers and just worry about the number. Plus you add/subtract and get durations, etc. Then convert the result to Date in whatever format. This code finds out how much time in minutes passed between a timestamp from a document, and the current time.

我喜欢Unix时间戳，因为您可以转换成数字，只关心数字。加上你的加减和持续时间，等等。然后把结果转换成任何格式的日期。这段代码会发现时间戳与文档之间的时间间隔，以及当前时间。

$date  = $item['pubdate']; (etc ...)
$unix_now = time();
$result = strtotime($date, $unix_now);
$unix_diff_min = (($unix_now  - $result) / 60);
$min = round($unix_diff_min);

#27

A TIMESTAMP requires 4 bytes, whereas a DATETIME requires 8 bytes.

时间戳需要4个字节，而DATETIME需要8个字节。

#28

In my case, I set UTC as a time zone for everything: the system, the database server, etc. every time that I can. If my customer requires another time zone, then I configure it on the app.

在我的例子中，我将UTC设置为所有东西的时区:系统、数据库服务器等等。如果我的客户需要另一个时区，那么我在应用程序上配置它。

I almost always prefer timestamps rather than datetime fields, because timestamps include the timezone implicitly. So, since the moment that the app will be accessed from users from different time zones and you want them to see dates and times in their local timezone, this field type makes it pretty easy to do it than if the data were saved in datetime fields.

我几乎总是喜欢时间戳，而不是datetime字段，因为时间戳是隐式地包含时区的。因此，由于应用程序将从不同时区的用户访问，并且您希望他们在本地时区中查看日期和时间，因此该字段类型使其非常容易执行，而不是在datetime字段中保存数据。

As a plus, in the case of a migration of the database to a system with another timezone, I would feel more confident using timestamps. Not to say possible issues when calculating differences between two moments with a sumer time change in between and needing a precision of 1 hour or less.

另外，如果将数据库迁移到具有另一个时区的系统，那么使用时间戳会让我更加自信。在计算两个时刻之间的差异时，不要说可能出现的问题，而在两者之间，需要精确到1小时或更短的时间。

So, to summarize, I value this advantages of timestamp:

总之，我重视时间戳的好处:

• ready to use on international (multi time zone) apps
• 准备好使用国际(多时区)应用。
• easy migrations between time zones
• 在时区之间容易迁移。
• pretty easy to calculate diferences (just subtract both timestamps)
• 很容易计算diferences(只要减去两个时间戳)
• no worry about dates in/out a summer time period
• 不用担心夏天的时间。

For all this reasons, I choose UTC & timestamp fields where posible. And I avoid headaches ;)

出于所有这些原因，我选择了UTC & timestamp字段。我避免头痛;

#29

A lot of answers here suggest to store as timestamp in the case you have to represent well defined points in time. But you can also have points in time with datetime if you store them all in UTC by convention.

这里的许多答案都表明，作为时间戳，您必须在时间上表示定义良好的点。但是，如果按照惯例将它们全部存储在UTC中，您也可以在时间上使用datetime。

#30

TIMESTAMP is useful when you have visitors from different countries with different time zones. you can easily convert the TIMESTAMP to any country time zone

当您有来自不同时区的不同国家的访问者时，时间戳是有用的。您可以轻松地将时间戳转换为任何国家时区。