blocks|key|1557998|text|您需要作为一个多步骤操作来执行此操作：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1557999|向数据库中添加一个新列。|unordered-list-item|1558000|用转换的值填充它。|1558001|删除旧列。|1558002|我强烈建议优先使用DATETIME而不是TIMESTAMP，因为TIMESTAMP列只工作到2038年，然后爆炸。|offset|length|style|CODE|1558003|UNIX_TIMESTAMP()不引用TIMESTAMP列，尽管它们是切分相关的，因为TIMESTAMP类型是time_t类型UNIX“时间戳”值的包装器，因此是2038年限额。|1558004|entityMap|0|LINK|mutability|MUTABLE|url|https://en.wikipedia.org/wiki/Year_2038_problem^0|0|0|0|0|9|8|K|9|W|9|0|0|G|J|9|17|9|1J|6|29|7|0|0^^$0|@$1|2|3|4|5|6|7|Y|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|Z|8|@]|9|@]|A|$]]|$1|E|3|F|5|D|7|10|8|@]|9|@]|A|$]]|$1|G|3|H|5|D|7|11|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|12|8|@$K|13|L|14|M|N]|$K|15|L|16|M|N]|$K|17|L|18|M|N]]|9|@]|A|$]]|$1|O|3|P|5|6|7|19|8|@$K|1A|L|1B|M|N]|$K|1C|L|1D|M|N]|$K|1E|L|1F|M|N]|$K|1G|L|1H|M|N]]|9|@$K|1I|L|1J|1|1K]]|A|$]]|$1|Q|3|-4|5|6|7|1L|8|@]|9|@]|A|$]]]|R|$S|$5|T|U|V|A|$W|X]]]]

You'll need to do this as a multi-step operation:

<ul>
<li>Add a new column to your database.</li>
<li>Populate it with the converted value.</li>
<li>Remove the old column.</li>
</ul>

I'd strongly recommend using <code>DATETIME</code> in preference to <code>TIMESTAMP</code> because the <code>TIMESTAMP</code> column only works until 2038 and then explodes.

<code>UNIX_TIMESTAMP()</code> does not refer to a <code>TIMESTAMP</code> column, though they're tangentially related in that the <code>TIMESTAMP</code> type is a wrapper around a <code>time_t</code> type UNIX "timestamp" value, hence <a href="https://en.wikipedia.org/wiki/Year_2038_problem" rel="nofollow noreferrer">the 2038 limit</a>.

blocks|key|1683495|text|您得到了第一个错误，因为DBMS不能动态地转换列。第二个错误来自于这样一个事实，即UNIX_TIMESTAMP()需要一个DATE、TIME或TIMESTAMP字符串而不是整数，所以您应该使用FROM_UNIXTIME()。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1683496|关于这两个函数之间的区别，UNIX_TIMESTAMP()文档有一个重要的说明：|1683497|如果使用UNIX_TIMESTAMP()和FROM_UNIXTIME()在时间戳值和Unix时间戳值之间进行转换，则转换是有损的，因为映射不是双向一对一的。例如，由于本地时区更改的约定，两个UNIX_TIMESTAMP()可以将两个时间戳值映射到相同的Unix时间戳值。FROM_UNIXTIME()将该值映射回原始时间戳值中的一个。|blockquote|1683498|--|1683499|下面是执行任务所需的步骤(，它可能需要很长时间才能完成，因为所有行都必须重写！)：|BOLD|1683500|向数据库中添加一个新列：
ALTER添加new_column时间戳NULL；--+PS:您可能希望在这里使用默认值而不是NULL。|ordered-list-item|1683501|转换旧值并用它们填充新列：
更新mytable集new_column+=+FROM_UNIXTIME(+old_column+)，其中old_column不是NULL；|1683502|删除旧栏：
更改表mytable删除列old_column；|1683503|将新列重命名为旧名称：
修改表mytable更改new_column+old_column；|1683504|entityMap|0|LINK|mutability|MUTABLE|url|https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_unix-timestamp|1|https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_from-unixtime^0|15|G|1P|4|1U|4|1Z|9|2O|F|15|G|0|2O|F|1|0|D|G|0|0|0|D|Q|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|18|8|@$9|19|A|1A|B|C]|$9|1B|A|1C|B|C]|$9|1D|A|1E|B|C]|$9|1F|A|1G|B|C]|$9|1H|A|1I|B|C]]|D|@$9|1J|A|1K|1|1L]|$9|1M|A|1N|1|1O]]|E|$]]|$1|F|3|G|5|6|7|1P|8|@$9|1Q|A|1R|B|C]]|D|@]|E|$]]|$1|H|3|I|5|J|7|1S|8|@]|D|@]|E|$]]|$1|K|3|L|5|6|7|1T|8|@]|D|@]|E|$]]|$1|M|3|N|5|6|7|1U|8|@$9|1V|A|1W|B|O]]|D|@]|E|$]]|$1|P|3|Q|5|R|7|1X|8|@]|D|@]|E|$]]|$1|S|3|T|5|R|7|1Y|8|@]|D|@]|E|$]]|$1|U|3|V|5|R|7|1Z|8|@]|D|@]|E|$]]|$1|W|3|X|5|R|7|20|8|@]|D|@]|E|$]]|$1|Y|3|-4|5|6|7|21|8|@]|D|@]|E|$]]]|Z|$10|$5|11|12|13|E|$14|15]]|16|$5|11|12|13|E|$14|17]]]]

You got the first error because the DBMS cannot convert the column on the fly. The second error comes from the fact that <a href="https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_unix-timestamp" rel="noreferrer"><code>UNIX_TIMESTAMP()</code></a> expects a <code>DATE</code>, <code>TIME</code> or <code>TIMESTAMP</code> string instead of integer so you should instead use <a href="https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_from-unixtime" rel="noreferrer"><code>FROM_UNIXTIME()</code></a>.

<code>UNIX_TIMESTAMP()</code> documentation has an important note on the difference between the two functions:

<blockquote>
 If you use UNIX_TIMESTAMP() and FROM_UNIXTIME() to convert between TIMESTAMP values and Unix timestamp values, the conversion is lossy because the mapping is not one-to-one in both directions. For example, due to conventions for local time zone changes, it is possible for two UNIX_TIMESTAMP() to map two TIMESTAMP values to the same Unix timestamp value. FROM_UNIXTIME() will map that value back to only one of the original TIMESTAMP values.
</blockquote>

--

Here's the steps you need to do the task (it can take a long time to complete because all rows will have to be rewritten!):

<ol>
<li>Add a new column to your database:

<pre class="lang-sql prettyprint-override"><code>ALTER TABLE mytable ADD new_column TIMESTAMP NULL; 
-- PS: You might want to use a default value instead of NULL here.
</code></pre></li>
<li>Convert the old values and fill the new column with them:

<pre class="lang-sql prettyprint-override"><code>UPDATE mytable SET new_column = FROM_UNIXTIME(old_column) WHERE old_column IS NOT NULL;
</code></pre></li>
<li>Delete the old column:

<pre class="lang-sql prettyprint-override"><code>ALTER TABLE mytable DROP COLUMN old_column;
</code></pre></li>
<li>Rename the new column to the old name:

<pre class="lang-sql prettyprint-override"><code>ALTER TABLE mytable CHANGE new_column old_column;
</code></pre></li>
</ol>

blocks|key|446712|text|例如，假设您有时间戳1376750476349，并且希望将它转换为日期时间。如果时间戳在seconds中，您可以简单地执行以下操作。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|446713|select+FROM_UNIXTIME(timestamp_value);|code-block|syntax|javascript|446714|但是，对于上述函数的许多用户来说，这是一个反复出现的问题，如果您的时间戳是用milliseconds表示的，那么在使用该函数之前，您必须执行所需的第二次转换。因此，在时间戳以毫秒为单位的情况下，您可以执行以下操作以获得日期时间：|446715|select+FROM_UNIXTIME(timestamp_value/1000);|446716|希望这能有所帮助。享受吧！|446717|来源|446718|entityMap|0|LINK|mutability|MUTABLE|url|https://intelligea.wordpress.com/2014/07/22/how-to-convert-big-int-timestamp-into-system-date-in-mysql/^0|A|D|18|7|0|0|12|C|0|0|0|0|2|0|0^^$0|@$1|2|3|4|5|6|7|10|8|@$9|11|A|12|B|C]|$9|13|A|14|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|15|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|16|8|@$9|17|A|18|B|C]]|D|@]|E|$]]|$1|M|3|N|5|H|7|19|8|@]|D|@]|E|$I|J]]|$1|O|3|P|5|6|7|1A|8|@]|D|@]|E|$]]|$1|Q|3|R|5|6|7|1B|8|@]|D|@$9|1C|A|1D|1|1E]]|E|$]]|$1|S|3|-4|5|6|7|1F|8|@]|D|@]|E|$]]]|T|$U|$5|V|W|X|E|$Y|Z]]]]

For instance, say that you have the timestamp <code>1376750476349</code> and you want to convert it into date time. You could simply do the following if your timestamp is in <code>seconds</code>.
<pre><code>select FROM_UNIXTIME(timestamp_value);
</code></pre>
however, and this is a recurrent problem for many users of the above function, if your timestamp is say in <code>milliseconds</code>, you will have to perform the required conversions to seconds` before using the function. So, in the case where the timestamp is in milliseconds you could do the following to get the datetime:
<pre><code>select FROM_UNIXTIME(timestamp_value/1000);
</code></pre>
Hope this helps. Enjoy!
<a href="https://intelligea.wordpress.com/2014/07/22/how-to-convert-big-int-timestamp-into-system-date-in-mysql/" rel="nofollow noreferrer">source</a>

I have a table, in this table I have a column which currently stores a value of type <code>BigInt</code>, I want to alter this column to contain 'timestamp' values instead. 

How can I achieve this. I have tried:

<pre><code>ALTER TABLE t
MODIFY c TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;
</code></pre>

For which I get:

<pre><code>#1292 - Incorrect datetime value: '1524120113' for column 'created_temp' at row 1
</code></pre>

And 

<pre><code>ALTER TABLE share ADD created_temp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;
UPDATE share SET created_temp = UNIX_TIMESTAMP(created);
</code></pre>

For which I get: <code>#1292 - Incorrect datetime value: '1524120113'</code> 

The content of <code>created</code> is created using <code>UNIX_TIMESTAMP()</code>

Converting BigInt to timestamp in MySQL

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我有一个表，在这个表中，我有一个列，它当前存储一个类型为BigInt的值，我想改变这个列以包含‘时间戳’值。我怎样才能做到这一点。我试过：ALTER TABLE tMODIFY c TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;我得到的是：#1292 - Incorrect ...

问在BigInt中将MySQL转换为时间戳
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在BigInt中将MySQL转换为时间戳EN