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

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

您会建议使用日期时间或时间戳字段,以及为什么(使用MySQL)?

I'm working with PHP on the server side.

我在服务器端使用PHP。

35 个解决方案

#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日期时间字段之间做出决定,请使用本机格式。您可以在MySQL中以这种方式进行计算(“SELECT DATE_ADD(my_datetime,INTERVAL 1 DAY)”),当您查询记录时,可以很容易地将值的格式更改为UNIX时间戳(“SELECT UNIX_TIMESTAMP(my_datetime)”)如果你想用PHP操作它。

#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及更高版本中,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服务器时区支持中所述。

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

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.

在一般情况下,你很有可能达到TIMESTAMPs的下限 - 例如存储生日。

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

以下示例显示TIMESTAMP日期类型如何在将时区更改为“america / new_york”后更改值,其中DATETIME未更改。

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:日期时间与时间戳数据类型。

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

换句话说:如果我在澳大利亚有一个数据库,并且转储该数据库以同步/填充美国的数据库,则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,他们的服务器永远不能确定跨时区发生的时间。一旦我正在进行对话,其中时间说我在实际发送消息之前回复了消息。 (当然,这也可能是由于消息传递软件中的时区转换不正确而导致发布时间而不是同步。)

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

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

#7

TIMESTAMP is 4 bytes Vs 8 bytes for DATETIME.

对于DATETIME,TIMESTAMP是4字节Vs 8字节。

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,TIMESTAMP是四个字节对八个字节。

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 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模式。

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

当TIMESTAMP受time_zone设置影响时,DATETIME是常量。

#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字段。如果你想要表示一个特定的日子(如生日),那么使用DATE类型,但如果你比这更具体,你可能有兴趣记录一个实际的时刻,而不是一个单位的时间(日,周,月,年)。使用BIGINT而不是使用DATETIME或TIMESTAMP,只需存储自纪元以来的毫秒数(如果您使用的是Java,则为System.currentTimeMillis())。这有几个好处:

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.

您可以避免供应商锁定。几乎每个数据库都以相对类似的方式支持整数。假设您要移动到另一个数据库。您是否想要担心MySQL的DATETIME值与Oracle如何定义它们之间的差异?即使在不同版本的MySQL中,TIMESTAMPS也具有不同的精度级别。直到最近,MySQL才支持时间戳中的毫秒数。

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.

没有时区问题。关于具有不同数据类型的时区会发生什么,这里有一些富有洞察力的评论。但这是常识,你的同事是否都会花时间去学习它?另一方面,将BigINT更改为java.util.Date非常困难。使用BIGINT会导致许多时区问题陷入困境。

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

不用担心范围或精度。您不必担心未来的日期范围会缩短什么(TIMESTAMP只会到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.

第三方工具集成。通过使用整数,第三方工具(例如EclipseLink)与数据库交互是微不足道的。并非每个第三方工具都会像MySQL那样对“日期时间”有同样的理解。想要尝试在Hibernate中弄清楚,如果您使用这些自定义数据类型,是否应该使用java.sql.TimeStamp或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美元)密切相关。你应该使用Decimal,还是数据库的Money类型,或者最糟糕的Double?由于上面列出的许多相同原因,所有这三个选项都很糟糕。解决方案是使用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).

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

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

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.

任何最新的前端框架(Angular 1/2,react,Vue,...)都可以轻松自动地将您的UTC日期时间转换为当地时间。

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比TIMESTAMP更快,http://gpshumano.blogs.dri.pt/2009/07/06/mysql-datetime-vs-timestamp-vs-int-performance-and-benchmarking-with -myisam /

• TIMESTAMP is still limited to 1970-2038

TIMESTAMP仍然限于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 querySELECT 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

#12

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.

时间戳字段是日期时间字段的特例。您可以创建时间戳列以具有特殊属性;它可以设置为在创建和/或更新时更新自身。

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

在“更大”的数据库术语中,时间戳上有几个特殊情况触发器。

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

正确的取决于你想做什么。

#13

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.

TIMESTAMP始终为UTC(即1970-01-01以来经过的秒数,以UTC为单位),并且您的MySQL服务器会自动将其转换为服务器时区的日期/时间。从长远来看,TIMESTAMP是最佳选择,因为您知道您的时态数据将始终为UTC。例如,如果您迁移到其他服务器或更改了服务器上的时区设置,则不会将日期搞砸。

#14

Comparison between DATETIME, TIMESTAMP and DATE

DATETIME,TIMESTAMP和DATE之间的比较

What is that [.fraction]?

什么是[.fraction]?

• A DATETIME or TIMESTAMP value can include a trailing fractionalseconds part in up to microseconds (6 digits) precision. Inparticular, any fractional part in a value inserted into a DATETIMEor TIMESTAMP column is stored rather than discarded. This is of course optional.

DATETIME或TIMESTAMP值可以包括高达微秒(6位)精度的尾随小数秒部分。特别是,插入DATETIMEor TIMESTAMP列的值中的任何小数部分都将被存储而不是被丢弃。这当然是可选的。

Sources:

• MySQL Date/Time data types reference

MySQL日期/时间数据类型参考

• MySQL Storage Requirements reference

MySQL存储要求参考

#15

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.

这将更新您修改行的每个实例的时间,有时对存储的最后编辑信息非常有用。这仅适用于时间戳,而不适用于日期时间。

#16

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时间戳,只需执行time();在MySQL中做SELECT UNIX_TIMESTAMP();.

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

根据我的经验,如果您想要一个只插入一次的日期字段,并且您不希望对该特定字段进行任何更新或任何其他操作,请使用日期时间。

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.

例如,考虑具有REGISTRATION DATE字段的用户表。在该用户表中,如果您想知道特定用户的上次登录时间,请使用时间戳类型字段以便更新该字段。

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创建表,则默认设置将在发生行更新时更新时间戳字段。如果您的时间戳字段未使用行更新进行更新,则可以使用以下查询使时间戳字段自动更新。

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那样以当前时区格式存储。当您获取数据时,时间戳再次将其转换为当前时区时间。

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 .

有关详细信息,请阅读博客文章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时间戳,只是为了在处理大量日期时间信息时保持理智,特别是在执行时区调整,添加/减去日期等时。在比较时间戳时,这排除了时区的复杂因素,并允许您在服务器端处理(无论是应用程序代码还是数据库查询)中节省资源,因为您使用轻量级算术而不是更重的日期时间加/减功能。

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语句时要小心时间戳更改。如果您有一个包含列'Name'(varchar),'Age'(int)和'Date_Added'(timestamp)的表,并运行以下DML语句

UPDATE tableSET 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

时间戳上的INDEX - 工作

• a INDEX's on Datetime - Does not work

日期时间的指数 - 不起作用

look at this post to see problems with Datetime indexing

看看这篇文章,看看日期时间索引的问题

#22

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

Timestamp和Datetime之间的另一个区别是在Timestamp中,您不能将默认值设置为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能够在不使用不必要的触发器的情况下根据当前时间自动更新自身的能力无与伦比。这只是我,虽然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.

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

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

TIMESTAMP也受到与TIME ZONE相关的不同设置的影响.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.

TIMESTAMP在内部将当前时区转换为UTC进行存储,并在检索期间将其转换回当前时区.DATETIME无法执行此操作。

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

TIMESTAMP支持的范围:'1970-01-01 00:00:01'UTC到'2038-01-19 03:14:07'UTCDATETIME支持的范围:'1000-01-01 00: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

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.

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

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)

很容易计算差异(只是减去两个时间戳)

• 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和时间戳字段。我避免头痛;)

#27

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

#28

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

TIMESTAMP需要4个字节,而DATETIME需要8个字节。

#29

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

当来自不同国家/地区的访问者具有不同时区时,TIMESTAMP非常有用。您可以轻松地将TIMESTAMP转换为任何国家/地区时区

#30

I merely use unsigned BIGINT while storing UTC ...

我只是在存储UTC时使用无符号BIGINT ...

which then still can be adjusted to local time in PHP.

然后仍然可以在PHP中调整到当地时间。

the DATETIME to be selected with FROM_UNIXTIME( integer_timestamp_column ).

使用FROM_UNIXTIME(integer_timestamp_column)选择的DATETIME。

one obviously should set an index on that column, else there would be no advance.

显然应该在该列上设置索引,否则就没有进展。