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


你会推荐使用 约会时间 或者a 时间戳 字段,为什么(使用MySQL)?

我在服务器端使用PHP。


2304
2018-01-03 16:14


起源


这有一些相关的信息 codeproject.com/Tips/1215635/MySQL-DATETIME-vs-TIMESTAMP - Waqleh


答案:


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

如果您想要在使用UNIX时间戳或本机MySQL日期时间字段之间做出决定,请使用本机格式。您可以通过这种方式在MySQL中进行计算 ("SELECT DATE_ADD(my_datetime, INTERVAL 1 DAY)") 并且可以很容易地将值的格式更改为UNIX时间戳 ("SELECT UNIX_TIMESTAMP(my_datetime)") 当你查询记录时,如果你想用PHP操作它。


1559
2018-01-03 16:26



一个重要的区别是 DATETIME 表示日期(在日历中找到)和时间(可以在挂钟上观察到),而 TIMESTAMP 代表一个明确定义的时间点。如果您的应用程序处理时区,这可能非常重要。多久以前'2010-09-01 16:31:00'?这取决于你所在的时区。对我而言,就在几秒钟前,对你而言,它可能代表着未来的一段时间。如果我说自1970-01-01 00:00:00 UTC以来的1283351460秒,你就知道我到底在什么时间点谈论。 (见下面Nir的优秀答案)。 [下行:有效范围]。 - MattBianco
另一个区别是:具有“本机”日期时间的查询将不会被缓存,但具有时间戳的查询将是。 - OZ_
“MySQL中的时间戳通常用于跟踪记录的变化”不要认为这是一个很好的答案。时间戳比MattBianco和Nir所说的更加强大和复杂。虽然,答案的第二部分非常好。 blivet说的是真的,这是一个很好的建议。 - santiagobasulto
另外一个重要的注意事项,DATETIME和TIMESTAMP可以使用CURRENT_TIMESTAMP,NOW()作为它的默认值,但DATE例如不能,因为它默认使用'0000-00-00',所以要解决这个问题你应该写您自己触发该表,在DATE mysql类型的字段/ col中插入当前日期(没有时间)。 - Arthur Kushman
@DavidHarkness:文档说“要指定自动属性,请使用DEFAULT CURRENT_TIMESTAMP和ON UPDATE CURRENT_TIMESTAMP子句” dev.mysql.com/doc/refman/5.0/en/timestamp-initialization.html - Plap


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

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


816
2018-03-02 11:49



资源: dev.mysql.com/doc/refman/5.1/en/datetime.html - Andy0708
这个OReilly演示文稿非常适合这个主题(PDF抱歉) cdn.oreillystatic.com/en/assets/1/event/36/... - gcb
它还涉及事件的性质: - 视频会议(TIMESTAMP)。所有参加者都应该参考调整到其时区的绝对时刻。 - 本地任务时间(DATETIME),我应该在2014/03/31 9:00 AM完成这项任务,如果那天我在纽约或巴黎工作,那就无所谓了。我将在当地时间上午8点开始工作,我将在那一天。 - yucer
所以,如果我改变服务器的时区,那么TIMESTAMP的值将保持不变或者也会改变? - Andrew
@Andrew,因为它是UTC,它将保持UTC。然而,向后转换将更改为“新”服务器时区。 - nembleton


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

提到 在MySQL文档中:

当您需要包含日期和时间信息的值时,将使用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模式。

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


430
2018-01-04 04:26



你也可以打 上 如果你在银行或房地产,很容易限制...现在30年抵押贷款超过2038年 - Kip
当然,使用64位Unix时间戳。例如,在Java中, new Date().getTime() 已经给你一个64位的值。 - osa
为DATETIME +1:在我们的数据流中,从物理商店的SQL Server购买和发票,转移到网页上的虚拟商店的MySQL服务器,DATETIME更适合修改日期时间,因为此数据类型也存在于Transact中 - SQL。但TIMESTAMP在Transact-SQL中意味着其他东西,它就像ROWVERSION一样是Binary,尽管MySQL TIMESTAMP与DateTime类似。因此,我们避免使用TIMESTAMP来兼容两个DB。 - jacouh
不知道。这是一个比MySQL更大的问题,并没有简单的解决方法: en.wikipedia.org/wiki/Year_2038_problem 我不相信MySQL只能声明时间戳现在是64位并且假设一切都会好的。它们不控制硬件。 - scronide
如果你知道出生的时间,生日可以是一个DATETIME,就像我对我的一样。 - Kris Craig


以下示例显示了如何 TIMESTAMP 更改后,日期类型更改了值 time-zone to '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 |
+---------------------+---------------------+

我已将我的答案转换成文章,以便更多人发现这有用, MySQL:日期时间与时间戳数据类型


284
2017-08-21 08:45



嗯,实际上是 DATETIME 有效时间随时区变化而变化, TIMESTAMP 没有改变,但人类的表现确实如此。 - flindeberg
似乎这个命令在MySQL 5.6中不起作用 set time_zone="america/new_york"; - Manjunath Reddy
以下是设置time_zone问题的答案。 stackoverflow.com/questions/3451847/mysql-timezone-change - Manjunath Reddy
同意@flindeberg。时区更改后,时间戳表示正确的时间,而不是日期时间 - Nitin Bansal


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

因此,只有当您拥有 - 或者将来可能 - 跨时区同步集群时,这才有意义。

用简单的话说: 如果我在澳大利亚有一个数据库,并且转储该数据库以同步/填充美国的数据库,则TIMESTAMP将更新以反映新时区中事件的实际时间,而DATETIME仍将反映时间在au时区的事件

应该使用TIMESTAMP的DATETIME的一个很好的例子是在Facebook,他们的服务器永远不能确定跨时区发生的时间。一旦我正在进行对话,其中时间说我在实际发送消息之前回复了消息。 (当然,这也可能是由于消息传递软件中的时区转换不正确而导致发布时间而不是同步。)


169
2018-01-14 14:26



我不认为这是一种好的思维方式。我只是以UTC格式存储和处理所有日期,并确保前端根据给定的时区显示它。这种方法简单且可预测。 - Kos
@Kos:是不是正确地存储和处理UTC中的所有日期,而TIMESTAMP正在内部做什么? (然后将其转换为显示您当地的时区?) - carbocation
我的 当地时区?您的数据库如何知道我的时区? ;-)通常在数据库和用户界面之间进行一些处理。我只在整个处理后进行本地化。 - Kos
@Koz:我的数据库不知道你的数据库时区:!但它确实知道时间戳。您的数据库知道自己的时区设置,并在解释/表示时间戳时应用它。 2013年12月11日上午1:01在北京中国与2013年12月11日凌晨1点01分在澳大利亚悉尼举行的时间不同。谷歌:'时区'和'本初子午线'。 - ekerner
区别不仅仅在于群集中其他服务器的时区。相关设置是从任何MySQL客户端连接定义的时区(默认情况下与服务器相同,但默认值可能取决于客户端驱动程序)。 - dolmen


我在语义基础上做出这个决定。

当我需要记录(或多或少)固定时间点时,我使用时间戳。例如,当记录插入数据库或发生某些用户操作时。

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


109
2018-01-03 17:11



时间戳 - 固定时间点,协调世界时间日期时间 - 相对于一个观点,到时间参考(ej时区当地时间),可以是..协调当地时间? - yucer


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

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

但是像scronide说它确实有一个1970年的下限。它对于未来可能发生的任何事情都很好;)


86
2018-01-04 09:00



未来将于2038-01-19 03:14:07 UTC结束。 - MattBianco
那太愚蠢了。我认为TIMESTAMP类型将“未来就绪”,因为它相对较新。每次处理不同时区时,都无法将日期时间转换为UTC并返回。它们应该使TIMESTAMP更大..至少增加1个字节。它将增加68 * 255 = 17340多年...虽然它不会是32位对齐。 - NickSoft
你知道为什么TIMESTAMP会在2038年结束吗?因为它是一个整数,并且整数有限制,即2147483647.我认为这就是原因。可能是如果他们将其类型更改为varchar并改变了如何操作它的方式,它将是“未来就绪”。 - vinsa
@vinsa,我认为那时候它还没有准备好。还记得Y2K的bug吗? 2038年即将到来。 - Pacerier
到2038年,MySQL(如果它仍然存在)将简单地更新TIMESTAMP字段以使用超过4个字节并允许更宽的范围 - the_nuts


  1. 对于DATETIME,TIMESTAMP是四个字节对八个字节。

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

  3. 当您需要包含日期和时间信息的值时,将使用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模式。

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

80
2017-12-21 11:12



您需要澄清#4:存储的时间戳与时区是常量且非相对(完全不可知),而检索时显示的输出受请求会话的时区影响。 DATETIME始终相对于记录的时区,并且必须始终在该上下文中考虑,现在责任归于应用程序。 - Christopher McGowan
很好的答案。要添加到这个答案,如果我们尝试存储超出'2038-01-09 03:14:07'的值,我们要么得到一个错误,要么存储为'0000-00-00 00:00:00'。我为我的数据库收到此错误,因为它具有时间移位值(用于加密目的)。 - Earnest_learner


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

  1. 您可以避免供应商锁定。几乎每个数据库都以相对类似的方式支持整数。假设您要移动到另一个数据库。您是否想要担心MySQL的DATETIME值与Oracle如何定义它们之间的差异?即使在不同版本的MySQL中,TIMESTAMPS也具有不同的精度级别。直到最近,MySQL才支持时间戳中的毫秒数。
  2. 没有时区问题。关于具有不同数据类型的时区会发生什么,这里有一些富有洞察力的评论。但这是常识,你的同事是否都会花时间去学习它?另一方面,将BigINT更改为java.util.Date非常困难。使用BIGINT会导致许多时区问题陷入困境。
  3. 不用担心范围或精度。您不必担心未来的日期范围会缩短什么(TIMESTAMP只会到2038年)。
  4. 第三方工具集成。通过使用整数,第三方工具(例如EclipseLink)与数据库交互是微不足道的。并非每个第三方工具都会像MySQL那样对“日期时间”有同样的理解。想要尝试在Hibernate中弄清楚,如果您使用这些自定义数据类型,是否应该使用java.sql.TimeStamp或java.util.Date对象?使用基本数据类型可以轻松使用第三方工具。

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


75
2018-06-11 23:02



我喜欢这个解决方案。 TIMESTAMP在2038年到期是一个主要问题。那真的不是那么遥远! - Charlie Dalsass
@CharlieDalsass你认为当时的软件会在那里:-P - Habeeb Perwad
算我一个 TIMESTAMP遇到了麻烦。这种BIGINT替代方案似乎更好。 - KcFnMi
如果数据存储为毫秒数,则很难按日期查询数据 - Ali Saeed
如果您关心多个时区的可移植性和处理用户,或者与用户在不同时区的数据库,这确实是最佳解决方案。如果它没有设计缺陷,TIMESTAMP可能是要走的路。太糟糕了,破碎的解决方案获得了更多的选票,因为它们是早期发布的(多年!)。 - German