blocks|key|657581|text|我想你要找的是FROM_UNIXTIME()|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|657582|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_from-unixtime^0|7|F|7|F|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@$9|O|A|P|B|C]]|D|@$9|Q|A|R|1|S]]|E|$]]|$1|F|3|-4|5|6|7|T|8|@]|D|@]|E|$]]]|G|$H|$5|I|J|K|E|$L|M]]]]

I think what you're looking for is <a href="http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_from-unixtime"><code>FROM_UNIXTIME()</code></a>

blocks|key|874816|text|为什么要费心将字段保存为可读字段？只有我们AS|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|874817|SELECT+theTimeStamp,+FROM_UNIXTIME(theTimeStamp)+AS+readableDate
+++++++++++++++FROM+theTable
+++++++++++++++WHERE+theTable.theField+=+theValue;|code-block|syntax|javascript|874818|编辑:对不起，我们以毫秒而不是秒为单位存储所有内容。修好了。|874819|entityMap^0|L|2|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@$9|P|A|Q|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|R|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|S|8|@]|D|@]|E|$]]|$1|M|3|-4|5|6|7|T|8|@]|D|@]|E|$]]]|N|$]]

Why bother saving the field as readable? Just us <code>AS</code>

<pre><code>SELECT theTimeStamp, FROM_UNIXTIME(theTimeStamp) AS readableDate
 FROM theTable
 WHERE theTable.theField = theValue;
</code></pre>

EDIT: Sorry, we store everything in milliseconds not seconds. Fixed it.

blocks|key|657647|text|您可以使用DATE_FORMAT函数。Here是一个包含示例和模式的页面，您可以使用这些模式来选择不同的日期组件。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|657648|entityMap|0|LINK|mutability|MUTABLE|url|http://davidwalsh.name/format-date-mysql-date_format^0|J|4|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@$A|M|B|N|1|O]]|C|$]]|$1|D|3|-4|5|6|7|P|8|@]|9|@]|C|$]]]|E|$F|$5|G|H|I|C|$J|K]]]]

You can use the DATE_FORMAT function. <a href="http://davidwalsh.name/format-date-mysql-date_format" rel="nofollow">Here</a>'s a page with examples, and the patterns you can use to select different date components.

blocks|key|874891|text|使用FROM_UNIXTIME()|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|874892|SELECT
++FROM_UNIXTIME(timestamp)+
FROM+
++your_table;|code-block|syntax|javascript|874893|另请参阅：MySQL+documentation+on+FROM_UNIXTIME()。|874894|entityMap|0|LINK|mutability|MUTABLE|url|http://forums.mysql.com/read.php?10,214465|1|https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_from-unixtime^0|2|F|2|F|0|0|0|S|F|5|12|1|0^^$0|@$1|2|3|4|5|6|7|W|8|@$9|X|A|Y|B|C]]|D|@$9|Z|A|10|1|11]]|E|$]]|$1|F|3|G|5|H|7|12|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|13|8|@$9|14|A|15|B|C]]|D|@$9|16|A|17|1|18]]|E|$]]|$1|M|3|-4|5|6|7|19|8|@]|D|@]|E|$]]]|N|$O|$5|P|Q|R|E|$S|T]]|U|$5|P|Q|R|E|$S|V]]]]

Use <a href="http://forums.mysql.com/read.php?10,214465" rel="noreferrer"><code>FROM_UNIXTIME()</code></a>:

<pre><code>SELECT
 FROM_UNIXTIME(timestamp) 
FROM 
 your_table;
</code></pre>

See also: <a href="https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_from-unixtime" rel="noreferrer">MySQL documentation on <code>FROM_UNIXTIME()</code></a>.

blocks|key|874954|text|其他答案中缺少的(在撰写本文时)并且不直接明显的是，from_unixtime可以接受第二个参数来指定格式，如下所示：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|874955|SELECT
++from_unixtime(timestamp,+'%25Y+%25D+%25M+%25H:%25i:%25s')
FROM+
++your_table|code-block|syntax|javascript|874956|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_from-unixtime^0|Q|D|Q|D|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@$9|T|A|U|B|C]]|D|@$9|V|A|W|1|X]]|E|$]]|$1|F|3|G|5|H|7|Y|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Z|8|@]|D|@]|E|$]]]|L|$M|$5|N|O|P|E|$Q|R]]]]

What's missing from the other answers (as of this writing) and not directly obvious is that <a href="http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_from-unixtime" rel="noreferrer"><code>from_unixtime</code></a> can take a second parameter to specify the format like so:

<pre><code>SELECT
 from_unixtime(timestamp, '%Y %D %M %H:%i:%s')
FROM 
 your_table
</code></pre>

blocks|key|875019|text|需要特定时区的unix时间戳吗？|type|unstyled|depth|inlineStyleRanges|entityRanges|data|875020|如果您可以快速访问mysql+cli，下面是一行代码：|875021|mysql>+select+convert_tz(from_unixtime(1467095851),+'UTC',+'MST')+as+'local+time';

%2B---------------------%2B
%7C+local+time++++++++++%7C
%2B---------------------%2B
%7C+2016-06-27+23:37:31+%7C
%2B---------------------%2B|code-block|syntax|javascript|875022|将'MST'替换为所需的时区。我住在亚利桑那州，因此从UTC到MST的转换。|offset|length|style|CODE|875023|entityMap^0|0|0|0|1|5|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|R|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|S|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|T|8|@$K|U|L|V|M|N]]|9|@]|A|$]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

<h1>Need a unix timestamp in a specific timezone?</h1>

Here's a one liner if you have quick access to the mysql cli:

<pre><code>mysql&gt; select convert_tz(from_unixtime(1467095851), 'UTC', 'MST') as 'local time';

+---------------------+
| local time |
+---------------------+
| 2016-06-27 23:37:31 |
+---------------------+
</code></pre>

Replace <code>'MST'</code> with your desired timezone. I live in Arizona thus the conversion from UTC to MST.

blocks|key|657822|text|简单易用的方法：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|657823|select+from_unixtime(column_name,+'%25Y-%25m-%25d')+from+table_name|offset|length|style|CODE|657824|entityMap^0|0|0|1P|0^^$0|@$1|2|3|4|5|6|7|J|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|K|8|@$D|L|E|M|F|G]]|9|@]|A|$]]|$1|H|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|I|$]]

Easy and simple way:

<code>select from_unixtime(column_name, '%Y-%m-%d') from table_name</code>

blocks|key|875135|text|由于我发现这个问题没有意识到，mysql总是将时间存储在UTC的时间戳字段中，但将以本地时区显示(例如phpmyadmin)。我想添加我的发现。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|875136|我有一个自动更新的last_modified字段，定义如下：|875137|`last_modified`+timestamp+NOT+NULL+DEFAULT+CURRENT_TIMESTAMP+ON+UPDATE+CURRENT_TIMESTAMP|code-block|syntax|javascript|875138|用phpmyadmin查看，它看起来像是本地时间，内部是UTC|875139|SET+time_zone+=+'%2B04:00';+//+or+'%2B00:00'+to+display+dates+in+UTC+or+'UTC'+if+time+zones+are+installed.
SELECT+last_modified,+UNIX_TIMESTAMP(last_modified),+from_unixtime(UNIX_TIMESTAMP(last_modified),+'%25Y-%25c-%25d+%25H:%25i:%25s'),+CONVERT_TZ(last_modified,@@session.time_zone,'%2B00:00')+as+UTC+FROM+`table_name`|875140|在任何星座中，UNIX_TIMESTAMP和'as+UTC‘总是以协调世界时的时间显示。|875141|运行两次，第一次不设置time_zone。|875142|entityMap^0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|T|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|V|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|W|8|@]|9|@]|A|$G|H]]|$1|M|3|N|5|6|7|X|8|@]|9|@]|A|$]]|$1|O|3|P|5|6|7|Y|8|@]|9|@]|A|$]]|$1|Q|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]]|R|$]]

Since I found this question not being aware, that mysql always stores time in timestamp fields in UTC but will display (e.g. phpmyadmin) in local time zone I would like to add my findings.

I have an automatically updated last_modified field, defined as:

<pre><code>`last_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
</code></pre>

Looking at it with phpmyadmin, it looks like it is in local time, internally it is UTC

<pre><code>SET time_zone = '+04:00'; // or '+00:00' to display dates in UTC or 'UTC' if time zones are installed.
SELECT last_modified, UNIX_TIMESTAMP(last_modified), from_unixtime(UNIX_TIMESTAMP(last_modified), '%Y-%c-%d %H:%i:%s'), CONVERT_TZ(last_modified,@@session.time_zone,'+00:00') as UTC FROM `table_name`
</code></pre>

In any constellation, UNIX_TIMESTAMP and 'as UTC' are always displayed in UTC time.

Run this twice, first without setting the time_zone.

blocks|key|875185|text|如果您希望转换时间并以特定格式显示数据，则可以使用此字符串。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|875186|date_format(convert_tz(from_unixtime(TIMESTAMP),+'UTC',+'DESIRED+TZ'),+'%25m/%25d/%25y')|code-block|syntax|javascript|875187|将convert_tz添加到date_format字符串的位置。%25m/%25d/%25y为月/日/年。你可以在这里找到所有特定的格式https://www.w3schools.com/sql/func_mysql_date_format.asp|offset|length|875188|entityMap|0|LINK|mutability|MUTABLE|url|https://www.w3schools.com/sql/func_mysql_date_format.asp^0|0|0|1Q|1K|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@]|9|@$I|V|J|W|1|X]]|A|$]]|$1|K|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|L|$M|$5|N|O|P|A|$Q|R]]]]

If you would like to convert time AND display the data in a specific format you can use this string.
<pre><code>date_format(convert_tz(from_unixtime(TIMESTAMP), 'UTC', 'DESIRED TZ'), '%m/%d/%y')
</code></pre>
where you add convert_tz to a date_format string. the %m/%d/%y being month/day/year.
you can find all the specific formats here <a href="https://www.w3schools.com/sql/func_mysql_date_format.asp" rel="nofollow noreferrer">https://www.w3schools.com/sql/func_mysql_date_format.asp</a>

Is there a MySQL function which can be used to convert a Unix timestamp into a human readable date? I have one field where I save Unix times and now I want to add another field for human readable dates.

Convert Unix timestamp into human readable date using MySQL

MySQL

Unix

有没有一个MySQL函数可以用来将Unix时间戳转换成人类可读的日期？我有一个保存Unix时间的字段，现在我想为人类可读的日期添加另一个字段。

问使用MySQL将Unix时间戳转换为人类可读的日期
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问使用MySQL将Unix时间戳转换为人类可读的日期EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问使用MySQL将Unix时间戳转换为人类可读的日期
EN