使用 MySQL 将 Unix 时间戳转换为人类可读的日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6267564/
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
Convert Unix timestamp into human readable date using MySQL
提问by King Julien
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.
是否有可用于将 Unix 时间戳转换为人类可读日期的 MySQL 函数？我有一个字段可以保存 Unix 时间，现在我想为人类可读的日期添加另一个字段。
回答by CristiC
Use FROM_UNIXTIME():
SELECT
FROM_UNIXTIME(timestamp)
FROM
your_table;
See also: MySQL documentation on FROM_UNIXTIME().
另请参阅： 有关FROM_UNIXTIME().
回答by User
What's missing from the other answers (as of this writing) and not directly obvious is that from_unixtimecan take a second parameter to specify the format like so:
其他答案（在撰写本文时）缺少的并且不直接明显的是from_unixtime可以使用第二个参数来指定格式，如下所示：
SELECT
from_unixtime(timestamp, '%Y %D %M %H:%i:%s')
FROM
your_table
回答by Ted Hopp
I think what you're looking for is FROM_UNIXTIME()
我想你要找的是 FROM_UNIXTIME()
回答by elbowlobstercowstand
Need a unix timestamp in a specific timezone?
需要特定时区的 Unix 时间戳？
Here's a one liner if you have quick access to the mysql cli:
如果您可以快速访问 mysql cli，这里有一个单行：
mysql> select convert_tz(from_unixtime(1467095851), 'UTC', 'MST') as 'local time';
+---------------------+
| local time |
+---------------------+
| 2016-06-27 23:37:31 |
+---------------------+
Replace 'MST'with your desired timezone. I live in Arizona thus the conversion from UTC to MST.
替换'MST'为您想要的时区。我住在亚利桑那州，因此从 UTC 到 MST 的转换。
回答by josh.trow
Why bother saving the field as readable? Just us AS
为什么要将字段保存为可读？只有我们AS
SELECT theTimeStamp, FROM_UNIXTIME(theTimeStamp) AS readableDate
FROM theTable
WHERE theTable.theField = theValue;
EDIT: Sorry, we store everything in milliseconds not seconds. Fixed it.
编辑：对不起，我们以毫秒而不是秒为单位存储所有内容。修复。
回答by Briguy37
回答by Akash gupta
Easy and simple way:
简单易行的方法：
select from_unixtime(column_name, '%Y-%m-%d') from table_name
select from_unixtime(column_name, '%Y-%m-%d') from table_name
回答by Gunnar Bernstein
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.
由于我发现这个问题没有意识到，mysql 总是在 UTC 的时间戳字段中存储时间，但会在本地时区显示（例如 phpmyadmin），我想添加我的发现。
I have an automatically updated last_modified field, defined as:
我有一个自动更新的 last_modified 字段，定义为：
`last_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
Looking at it with phpmyadmin, it looks like it is in local time, internally it is UTC
用phpmyadmin看，好像是本地时间，内部是UTC
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`
In any constellation, UNIX_TIMESTAMP and 'as UTC' are always displayed in UTC time.
在任何星座中，UNIX_TIMESTAMP 和 'as UTC' 始终以 UTC 时间显示。
Run this twice, first without setting the time_zone.
运行两次，首先不设置 time_zone。
