我应该在 MySQL 中使用 datetime 还是 timestamp 数据类型？

短命女 2024-03-30 11:50 84阅读 0赞

## 问： ##

您是否推荐使用 datetime 或 timestamp 字段，为什么（使用 MySQL）？

我在服务器端使用 PHP。

## 答1: ##

> huntsbot.com – 程序员副业首选，一站式外包任务、远程工作、创意产品分享订阅平台。

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

如果您的意思是要在使用 UNIX 时间戳或原生 MySQL 日期时间字段之间做出决定，请使用原生 DATETIME 格式。您可以通过这种方式在 MySQL 中进行计算 (“SELECT DATE\_ADD(my\_datetime, INTERVAL 1 DAY)”)，如果您想使用 PHP 对其进行操作，则在查询记录时可以很简单地将值的格式更改为 UNIX 时间戳 (“SELECT UNIX\_TIMESTAMP(my\_datetime)”)。

> huntsbot.com – 程序员副业首选，一站式外包任务、远程工作、创意产品分享订阅平台。

`一个重要的区别是 DATETIME 表示日期（在日历中）和时间（可以在挂钟上观察到），而 TIMESTAMP 表示明确定义的时间点。如果您的应用程序处理时区，这可能非常重要。 '2010-09-01 16:31:00' 是多久以前的事了？这取决于您所在的时区。对我而言，这只是几秒钟前，对您而言，它可能代表未来的某个时间。如果我说自 '1970-01-01 00:00:00 UTC' 以来的 1283351460 秒，那么您确切知道我所说的时间点。 （请参阅下面 Nir 的出色回答）。 [缺点：有效范围]。`

`另一个区别：带有“本机”日期时间的查询不会被缓存，但带有时间戳的查询会被缓存。`

`“MySQL 中的时间戳通常用于跟踪记录的更改”不要认为这是一个好的答案。时间戳比 MattBianco 和 Nir 所说的要强大和复杂得多。虽然，答案的第二部分非常好。 blivet 说的是真的，是一个很好的建议。`

`我是支持 2000 的人：P`

## 答2: ##

> 与HuntsBot一起，探索全球自由职业机会–huntsbot.com

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

默认情况下，每个连接的当前时区是服务器的时间。如MySQL Server Time Zone Support 中所述，可以基于每个连接设置时区。

`这个 OReilly 演示文稿非常适合这个主题（PDF 抱歉）cdn.oreillystatic.com/en/assets/1/event/36/…`

`它还与事件的性质有关： - 视频会议 (TIMESTAMP)。所有服务员都应该看到对调整到其时区的绝对时间的引用。 - 本地任务时间（DATETIME），我应该在 2014 年 3 月 31 日上午 9:00 做这个任务，不管那天我在纽约还是巴黎工作。我将在当地时间上午 8:00 开始工作，我将在那天。`

`是的，这太可怕了。 DBMS 永远不应该在任何方向上转换时间戳，也不应该考虑当前 DB 的系统时间。它应该按原样保存内部时间戳（自纪元以来的毫秒数）。在呈现值时（在最后一刻），值应该在应用程序的用户时区中呈现（！）。其他一切都只是纯粹的痛苦。如果有的话，DBMS 应该支持本地和绝对时间的显式类型，其中“本地”类似于您的生日或“中午”，“绝对”类似于火箭的开始时间。`

## 答3: ##

> huntsbot.com精选全球7大洲远程工作机会，涵盖各领域，帮助想要远程工作的数字游民们能更精准、更高效的找到对方。

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

如 MySQL 文档中的 mentioned：

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

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

`如果您从事银行业或房地产业，您也可以轻松达到上限... 30年抵押贷款现在超过2038年`

`当然，使用 64 位 Unix 时间戳。例如，在 Java 中，new Date().getTime() 已经为您提供了 64 位值。`

`不知道。这是一个比 MySQL 更大的问题并且没有简单的解决方法：en.wikipedia.org/wiki/Year_2038_problem 我不相信 MySQL 可以仅仅声明时间戳现在是 64 位并假设一切都会好起来的。他们不控制硬件。`

`所以 timestamp 在内部（大概）存储为 Unix 时间戳 - 一个整数。它可以快速插入和检索，但在对其应用日期函数时可能会更慢，因为它需要转换为内部日期时间格式才能执行此操作。因此，将 timestamp 用于简单的日志记录，它会很快，但不太灵活并且范围更小。`

## 答4: ##

> huntsbot.com洞察每一个产品背后的需求与收益，从而捕获灵感

以下示例显示了 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: Datetime Versus Timestamp Data Types。

`好吧，实际上 DATETIME 有效时间随着时区的变化而变化，TIMESTAMP 没有变化，但人类表示却发生了变化。`

## 答5: ##

> huntsbot.com精选全球7大洲远程工作机会，涵盖各领域，帮助想要远程工作的数字游民们能更精准、更高效的找到对方。

主要区别在于 DATETIME 是常量，而 TIMESTAMP 受 time\_zone 设置的影响。

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

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

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

`我不认为这是一种好的思维方式。我只需以 UTC 格式存储和处理所有日期，并确保前端根据给定的时区显示它。这种方法简单且可预测。`

`@Kos：在UTC中存储和处理所有日期不正是TIMESTAMP在内部所做的吗？ （然后将其转换为显示您当地的时区？）`

`我的本地时区？您的数据库如何知道我的时区？ ;-) 数据库和用户界面之间通常有相当多的处理。我只在整个处理之后才进行本地化。`

`@Koz：我的数据库不知道您的数据库时区：！但它确实知道时间戳。您的数据库知道自己的时区设置，并在解释/表示时间戳时应用它。中国北京 2013 年 12 月 11 日凌晨 1:01 与澳大利亚悉尼 2013 年 12 月 11 日凌晨 1:01 的时间不同。谷歌：“时区”和“本初子午线”。`

## 答6: ##

> HuntsBot周刊–不定时分享成功产品案例，学习他们如何成功建立自己的副业–huntsbot.com

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

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

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

> huntsbot.com – 高效赚钱，自由工作

`时间戳 - 固定时间点，协调世界时 datetime - 相对于某个观点，相对于时间参考（ej 时区本地时间），可能是.. 协调本地时间？`

## 答7: ##

> huntsbot.com汇聚了国内外优秀的初创产品创意，可按收入、分类等筛选，希望这些产品与实践经验能给您带来灵感。

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

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

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

`我喜欢这个解决方案。 2038 年到期的 TIMESTAMP 是一个主要问题。那真的不是那么遥远！`

`如果数据存储为毫秒数，则很难按日期查询数据`

`这应该是公认的答案。我鄙视任何一种“原生”日期格式。无尽的时区捕获等。`

`@Merc 不，不应该。数据结构中的语义是有原因的。数据库引擎具有语义类型也是有原因的。如果您根本不关心语义，为什么首先要使用 RDBMS（关系 -> 基于数据之间的语义关系）？`

`@lukasz032 是的，应该是。语义是一件好事，但是当您添加到混合时区、限制（2038 年有人吗？）、凌乱的实现和歧义时，那就不行了——现实世界的项目将使用 bigint 非常感谢`

## 答8: ##

> HuntsBot周刊–不定时分享成功产品案例，学习他们如何成功建立自己的副业–huntsbot.com

TIMESTAMP 是 4 个字节，而 DATETIME 是 8 个字节。数据库上的时间戳也更轻，索引速度更快。当您需要包含日期和时间信息的值时，使用 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 模式。DATETIME 是常量，而 TIMESTAMP 受 time\_zone 设置的影响。

`您需要澄清＃4：存储的时间戳是恒定的并且与时区不相关（完全不可知），而检索时显示的输出受请求会话的时区影响。 DATETIME 始终与记录它的时区相关，并且必须始终在该上下文中考虑，现在属于应用程序的责任。`

## 答9: ##

> huntsbot.com洞察每一个产品背后的需求与收益，从而捕获灵感

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

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

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

`未来在 2038-01-19 03:14:07 UTC 结束。`

## 答10: ##

> HuntsBot周刊–不定时分享成功产品案例，学习他们如何成功建立自己的副业–huntsbot.com

取决于应用程序，真的。

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

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

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

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

`您的应用程序不应依赖服务器的时区。在发出任何查询之前，应用程序应始终在其使用的数据库连接会话中选择时区。如果在多个用户之间共享连接（例如：webapp），请使用 UTC 并在渲染端进行时区转换。`

## 答11: ##

> 与HuntsBot一起，探索全球自由职业机会–huntsbot.com

2016 +：我建议将您的 Mysql 时区设置为 UTC 并使用 DATETIME：

任何最近的前端框架（Angular 1/2、react、Vue 等）都可以轻松自动地将您的 UTC 日期时间转换为本地时间。

此外：

DATETIME 现在可以自动设置为当前时间值 如何为 MySQL Datetime 列设置默认值？

与人们的想法相反，DATETIME 比 TIMESTAMP 更快，http://gpshumano.blogs.dri.pt/2009/07/06/mysql-datetime-vs-timestamp-vs-int-performance-and-benchmarking-with -myisam/

TIMESTAMP 仍然限于 1970-2038

（除非您可能会更改服务器的时区）

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' }}

此处提供的所有本地化时间格式：https://docs.angularjs.org/api/ng/filter/date

`您的数据不应附加到服务器的时区设置。也许，如果你的桌子下面有一个 MySql 盒子，它对你有用`

原文链接：[https://www.huntsbot.com/qa/YlXK/should-i-use-the-datetime-or-timestamp-data-type-in-mysql?lang=zh\_CN&from=csdn][https_www.huntsbot.com_qa_YlXK_should-i-use-the-datetime-or-timestamp-data-type-in-mysql_lang_zh_CN_from_csdn]

> huntsbot.com全球7大洲远程工作机会，探索不一样的工作方式

[https_www.huntsbot.com_qa_YlXK_should-i-use-the-datetime-or-timestamp-data-type-in-mysql_lang_zh_CN_from_csdn]: https://www.huntsbot.com/qa/YlXK/should-i-use-the-datetime-or-timestamp-data-type-in-mysql?lang=zh_CN&from=csdn