使用MySQL的TIMESTAMP直接存储时间戳 [英] Using MySQL's TIMESTAMP vs storing timestamps directly
问题描述
我在使用MySQL的TIMESTAMP格式vs以自定义的UNSIGNED INT格式保存日期和时间值的困境。这里的主要考虑因素是检索速度，PHP中适当的范围计算和偶尔格式化为人类可读取的值。
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.
每种类型及其范围所需的存储空间： p>
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)
我不需要DATETIME的范围。我在TIMESTAMP和UNSIGNED INT之间撕裂。
I dont need the range of DATETIME at all. I'm torn between TIMESTAMP and UNSIGNED INT.
有利于UNSIGNED INT的参数：
Arguments in favor of UNSIGNED INT:
- UNIX的时间戳为4294967295转换为Sun，07 Feb 2106 06:28:15 GMT，超过TIMESTAMP，对我来说足够好
- 直接在PHP中比较这些时间戳更快，而不是通过strtotime（）转换TIMESTAMPs，然后比较它们
TIMESTAMP给我的唯一优势是当我阅读在mysql表中的值是手动的，需要看他们。
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而不是UNSIGNED INT？
Is there any compelling reason to use TIMESTAMP and not an UNSIGNED INT?
推荐答案
TIMESTAMP的参数
Arguments for TIMESTAMP
- 它隐式地存储数据GMT时区。不管你的会话时区是什么。您可以使用
DEFAULT CURRENT_TIMESTAMP或ON自动添加时间戳列更新CURRENT_TIMESTAMP（每个表只有一列到MySQL 5.6.5） - 您可以使用datetime函数进行日期比较，加法，减法，范围查找等，需要使用
FROM_UNIXTIME（）函数 - 这将使编写更易于编写可以使用索引的查询 -
在PHP中
- It implicitly stores data in GMT time zone. No matter what your session time-zone is. Useful if you need to use different timezones.
- 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 comparision, addition, substraction, 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
- 您仍然可以使用UNIX_TIMESTAMP（）函数检索整数unix时间戳，无需额外的开销： http://dev.mysql.com/doc/refman/5.5 /en/date-and-time-functions.html#function_unix-timestamp
- 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
当在TIMESTAMP列上使用UNIX_TIMESTAMP（）时，函数
直接返回内部时间戳值，没有隐式
string-to-Unix-时间戳转换
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
这篇关于使用MySQL的TIMESTAMP直接存储时间戳的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
