unix时间戳应如何存储在int列中？ [英] How should unix timestamps be stored in int columns?

问题描述

由于统计原因，我有一个记录表将包含数百万个写入。所有列都是int外键。我还要为每一行添加一个时间戳列。鉴于DATETIME需要8位，我将使用 int（10）unsigned 将存储空间（该列的索引）削减一半。

I have a logging table that will contain millions of writes for statistical reasons. All the columns are int foreign keys. I am also going to add a timestamp column for each row. Given that DATETIME takes 8bits - I will be using int(10) unsigned to cut the storage space (and index on that column) in half.

但是，我想知道这个列何时不再工作。在2038年1月19日上午3:14:07，值为9,999,999,999将是UNIX时间戳的问题 - 但MySQL中的unsigned int只能保存4,294,967,295，而时间戳4294967295在我的PHP应用程序中显示无效。

However, I'm wondering when this column would no longer work. At 3:14:07AM on 19th January 2038 the value 9,999,999,999 will be a problem for UNIX timestamps - but an unsigned int in MySQL only holds up to 4,294,967,295 and the timestamp 4294967295 is showing an invalid number in my PHP application.

那是什么意思？ MySQL的存储int时间戳的结束是在2021年的某个时候，因为它不能一直到9999999999？

答案：

1. 2147483647是2038（不是9999999999），所以没有问题。


2. unsigned 不需要，因为2147483647适用于已签名的MySQL int。

1. 2147483647 is 2038 (not 9999999999) so there is no problem.
2. unsigned isn't needed since 2147483647 fits fine in a signed MySQL int.

推荐答案

标准UNIX时间戳是一个带符号的32位整数，在MySQL中是一个常规的int列。没有办法可以存储9,999,999,999，因为这种方式超出了表示范围 - 最高的32位int可以是4,294,967,295。签名32位的最高值为2,147,483,647。

Standard UNIX timestamps are a signed 32bit integer, which in MySQL is a regular "int" column. There's no way you could store 9,999,999,999, as that's way outside the representation range - the highest a 32bit int of any sort can go is 4,294,967,295. The highest a signed 32bit in goes is 2,147,483,647.

如果/当UNIX时间戳记转换为64位数据类型时，则必须使用MySQLbigint

If/when UNIX timestamps go to a 64bit data type, then you'll have to use a MySQL "bigint" to store them.

对于 int（10），（10） code>部分仅用于显示目的。 MySQL仍然会在内部使用一个完整的32bit来存储数字，但是只要您在表上进行选择，就会显示10。

As for int(10), the (10) portion is merely for display purposes. MySQL will still use a full 32bit internally to store the number, but only display 10 whenever you do a select on the table.

这篇关于unix时间戳应如何存储在int列中？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！