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

问:

您是否推荐使用 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

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

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值