在 mysql 中将纪元数转换为人类可读的日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23994889/
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
Converting epoch number to human readable date in mysql
提问by user3675548
I have a epoch number say 1389422614485. The datatype for the value storing this value is varchar. I want to convert its value to human readable time. How can we do it? Any example for this conversion?
我有一个纪元号，比如 1389422614485。存储这个值的值的数据类型是 varchar。我想将其值转换为人类可读的时间。我们怎么做？这种转换的任何例子？
回答by Abhik Chakraborty
Your epoch value 1389422614485seems like having the millisecond precision. So you need to use some mysql mathematical functions along with from_unixtime()for generating human readable format.
您的纪元值1389422614485似乎具有毫秒精度。因此，您需要使用一些 mysql 数学函数以及from_unixtime()生成人类可读的格式。
mysql> select from_unixtime(floor(1389422614485/1000));
+------------------------------------------+
| from_unixtime(floor(1389422614485/1000)) |
+------------------------------------------+
| 2014-01-11 12:13:34 |
+------------------------------------------+
回答by cristian
Take a look at from-unixtime
mysql> SELECT FROM_UNIXTIME(1196440219);
-> '2007-11-30 10:30:19'
回答by Olli
You can use from_unixtime() as follows:
您可以使用 from_unixtime() 如下：
SELECT from_unixtime(1388618430);
which returns 2014-01-02 00:20:30
返回 2014-01-02 00:20:30
回答by Omari Victor Omosa
This wil work for both +positiveand -negativeepoch, in-case for old birth dates, and also if you want to specify date format
这将适用于+positive和-negative时代，以防旧的出生日期，以及如果您想指定日期格式
select
date_format(DATE_ADD(from_unixtime(0), interval '1389422614485'/1000 second), '%Y-%m-%d %H:%i:%s') as my_new_date;
