php 使用 MySQL 的 TIMESTAMP vs 直接存储时间戳
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7029127/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using MySQL's TIMESTAMP vs storing timestamps directly
提问by siliconpi
I'm in a dilemma about saving date and time values in MySQL's TIMESTAMP format vs in a custom UNSIGNED INT format. The main considerations here are speed of retrieval, appropriate range calculations in PHP and occasional formatting into human readable values.
关于以 MySQL 的 TIMESTAMP 格式与自定义 UNSIGNED INT 格式保存日期和时间值，我处于两难境地。这里的主要考虑因素是检索速度、PHP 中的适当范围计算以及偶尔格式化为人类可读的值。
The storage space required for each type and their ranges:
每种类型及其范围所需的存储空间：
DATETIME 8 bytes '1000-01-01 00:00:00' to '9999-12-31 23:59:59'
TIMESTAMP 4 bytes '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC
UNSIGNED INT 4 bytes (Maximum Value 4294967295)
I dont need the range of DATETIME at all. I'm torn between TIMESTAMP and UNSIGNED INT.
我根本不需要 DATETIME 的范围。我在 TIMESTAMP 和 UNSIGNED INT 之间左右为难。
Arguments in favor of UNSIGNED INT:
支持 UNSIGNED INT 的论据：
- A UNIX timestamp of 4294967295 converts to Sun, 07 Feb 2106 06:28:15 GMT which is more than TIMESTAMP and good enough for me
- Comparing these timestamps directly in PHP would be faster rather than converting TIMESTAMPs via strtotime() and then comparing them
- UNIX 时间戳 4294967295 转换为 Sun, 07 Feb 2106 06:28:15 GMT 这比 TIMESTAMP 还多，对我来说已经足够了
- 直接在 PHP 中比较这些时间戳会更快，而不是通过 strtotime() 转换 TIMESTAMP 然后比较它们
The only advantage TIMESTAMP would give me is when I'm reading in the values from the mysql table manually and need to 'see' them.
TIMESTAMP 给我的唯一优势是当我手动读取 mysql 表中的值并需要“查看”它们时。
Is there any compelling reason to use TIMESTAMP and not an UNSIGNED INT?
是否有任何令人信服的理由使用 TIMESTAMP 而不是 UNSIGNED INT？
采纳答案by Mchl
Arguments for TIMESTAMP
TIMESTAMP 的参数
- It implicitly stores data in UTC time zone. No matter what your session time-zone is. Useful if you need to use different time zones.
- You can have automated timestamping columns using
DEFAULT CURRENT_TIMESTAMPorON UPDATE CURRENT_TIMESTAMP(one column per table only until MySQL 5.6.5) - You can use datetime function for date comparison, addition, subtraction, range lookup etc, without the need to use
FROM_UNIXTIME()function - it will make it easier to write queries that can use indexes In PHP
>> date('Y-m-d h:i:s',4294967295); '1969-12-31 11:59:59'so the range is in fact the same
- You can still retrieve integer unix timestamp with no additional overhead using UNIX_TIMESTAMP() function: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_unix-timestamp
- 它以 UTC 时区隐式存储数据。无论您的会话时区是什么。如果您需要使用不同的时区，则很有用。
- 您可以使用
DEFAULT CURRENT_TIMESTAMPor自动时间戳列ON UPDATE CURRENT_TIMESTAMP（在 MySQL 5.6.5 之前，每个表只有一列） - 您可以使用 datetime 函数进行日期比较、加法、减法、范围查找等，而无需使用
FROM_UNIXTIME()函数 - 这将使编写可以使用索引的查询变得更容易 在 PHP 中
>> date('Y-m-d h:i:s',4294967295); '1969-12-31 11:59:59'所以范围实际上是一样的
- 您仍然可以使用 UNIX_TIMESTAMP() 函数在没有额外开销的情况下检索整数 unix 时间戳：http: //dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_unix-timestamp
When UNIX_TIMESTAMP() is used on a TIMESTAMP column, the function returns the internal timestamp value directly, with no implicit “string-to-Unix-timestamp” conversion
当 UNIX_TIMESTAMP() 用于 TIMESTAMP 列时，该函数直接返回内部时间戳值，没有隐式的“字符串到 Unix 时间戳”转换
回答by Tim Fountain
The only real use for TIMESTAMP is when you want that field to be updated automatically when the row is updated (which is the default behaviour for that field), or when data storage requirements are so strict that 4 bytes per row really makes a difference to you.
TIMESTAMP 的唯一真正用途是当您希望在更新行时自动更新该字段（这是该字段的默认行为），或者当数据存储要求如此严格以至于每行 4 个字节确实对你。
Really the comparison should be between DATETIME and UNSIGNED INT, and I'd recommend DATETIME because:
确实应该在 DATETIME 和 UNSIGNED INT 之间进行比较，我推荐 DATETIME，因为：
- You can use MySQL's native date/time functions for selecting by date ranges etc.
- It is trivially easy to select these dates out as UNIX timestamps for easy formatting in PHP:
SELECT UNIX_TIMESTAMP(field) FROM table, no need to select out the raw value and use strtotime - Easier to read and edit the fields in your database directly if you need to (as you pointed out).
- No limitations on date range
- 您可以使用 MySQL 的本机日期/时间函数按日期范围等进行选择。
- 选择这些日期作为 UNIX 时间戳以便在 PHP 中轻松格式化非常容易：
SELECT UNIX_TIMESTAMP(field) FROM table，无需选择原始值并使用 strtotime - 如果需要，可以更轻松地直接读取和编辑数据库中的字段（如您所指出的）。
- 日期范围没有限制
Point two alone really removes any reason to store in integers, in my opinion.
在我看来，仅第二点就真的消除了以整数存储的任何理由。
回答by nobody
This might not be a "scientific" answer but I always find the way MySql handles conversion, arithmetics, comparsion, etc... on TIMESTAMP columns confusing. An UNSIGNED INT column is much more straight forward and I always know what to expect.
这可能不是一个“科学”的答案，但我总是发现 MySql 在 TIMESTAMP 列上处理转换、算术、比较等的方式令人困惑。UNSIGNED INT 列更直接，我总是知道会发生什么。
P.S. Perhaps one other thing in favor of TIMESTAMP column is its ability to be automatically set to current time after each update or insert but that is not something you can't live without.
PS 也许支持 TIMESTAMP 列的另一件事是它能够在每次更新或插入后自动设置为当前时间，但这并不是你不能没有的。
