在MySQL的DATETIME列中选择TIMESTAMP的好理由是什么？ [英] What are good reasons to choose TIMESTAMP over DATETIME columns in MySQL?

问题描述

免责声明：这可能与 datetime vs. timestamp？重复，但我觉得我是不相信答案：

我的问题是：

在MySQL的DATETIME列中选择TIMESTAMP的好理由是什么？ / strong>

我很确定我不知道这两者之间的差异，所以我将尝试列出DATETIME的主要优势（据我所知，与TIMESTAMP（ TS ）相比， DT ）

• DT 是可读的（ TS 不是不使用 TO_DATE ）


• DT 的时间长度为8999年（ 1000-01-01 00:00:00 至 9999-12-31 23:59:59 ）
  
  （ TS 仅约68年， 1970-01-01 至 2038-01-19 ）


• DT 字段似乎表现更好（accordi转到此博文）


• DT可以用于高级日期计算（ SELECT NOW（）+ INTERVAL 2 DAY ）



反之亦然：

• TS 只需要4个字节（ DT 使用8）


• TS 存储为UTC值，并根据客户端的时区设置更改


• TS 列可以作为一个日志，用于监视行何时更改

我看到使用它的唯一原因将是行监视，但是查看 TIMESTAMP 的范围，这将以仅28年结束*，我宁愿使用插入或更新触发器。 / p>

那么，我还缺少什么？我仍然看不到为了任何目的选择时间戳的 的原因。

_{*我知道，很长一段时间，但是在六十年代，一些工程师也决定削减2字节的年度字段，因为他们的计算机系统永远不会运行直到2000年.....}

解决方案

DT列也可以作为一个日志，用于监视行何时发生变化，我使用它们和触发器来做到这一点。 / p>

我通常在使用大量PHP的东西时使用时间戳记来减少太多的日期解析。如果有需要，我可以更新我的软件以使用datetime。

Disclaimer: This might be a duplicate of datetime vs. timestamp?, but I feel I am not convinced of the answers:

My question is:
What are the good reasons to choose TIMESTAMP over DATETIME columns in MySQL?

I'm pretty sure I don't know everything there is about the differences between those two, so I will try to list the main advantages of DATETIME (DT) compared to TIMESTAMP (TS) to my knowledge:

• DT is human readable (TS is not without using TO_DATE)
• DT has a possble timespan of 8999 Years (1000-01-01 00:00:00 to 9999-12-31 23:59:59)
  (TS only about 68 years, 1970-01-01 to 2038-01-19)
• DT fields seem to perform better (according to this blog post)
• DT can be used for advanced date calculation (SELECT NOW() + INTERVAL 2 DAY)

And vice versa:

• TS only needs 4 bytes (DT uses 8)
• TS are stored as UTC values and changed according to the client's timezone setting
• TS columns can serve as a "log" for monitoring when a row has changes

The only reason I see for using it then would be the row monitoring, but looking at the range of TIMESTAMP, which will end in "only" 28 years*, I would rather go with an insert or update trigger.

So, what am I missing? I still don't see a really good reason for choosing timestamp for any purpose?

_{* I know, this is appears quite long, but back in the 60s some engineers also decided to shave off 2 bytes of the year field, because their computer systems would never run until the year 2000 .....}

解决方案

DT columns can also serve as a "log" for monitoring when a row changes, I use them and triggers to do that.

I usually use timestamp when working with a lot of PHP stuff as to cut down on too much date parsing. If the need ever arises, I can update my software to use datetime.

这篇关于在MySQL的DATETIME列中选择TIMESTAMP的好理由是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！