在 mySQL 中使用行聚合和操作将 BigInt 时间戳转换为真实日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19585245/
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 BigInt timestamp to a real Date with row aggregation and operations in mySQL
提问by рüффп
I have a query which takes the last update date (timestamp but as a bigint(20) column) like this:
我有一个查询，它采用上次更新日期（时间戳，但作为 bigint(20) 列），如下所示：
SELECT a.id_workorder, MAX(b.update_date) AS udpate_date
FROM main_log a,
(
SELECT MAX(log_date) AS update_date, log_id
FROM log_a
GROUP BY log_id
UNION
SELECT MAX(log_date) AS update_date, log_id
FROM log_b
GROUP BY log_id
)b
WHERE a.id_log = b.log_id
GROUP BY b.log_id
and it returns the last update date (unix timestamp as a bigint(20)) for any kind of log (a or b):
它返回任何类型的日志（a 或 b）的最后更新日期（unix 时间戳作为 bigint(20)）：
id last update
-------------------------
1001 1376750476349
1002 1376753690861
1003 1378122801986
1004 1377764414858
1005 1377847226096
...
Now I want to format the return in date format and I naively though I can just format the outside timestamp with FROM_UNIXTIMElike this:
现在我想以日期格式格式化返回，虽然我可以FROM_UNIXTIME像这样格式化外部时间戳，但我很天真：
SELECT
a.id_workorder,
FROM_UNIXTIME(MAX(b.update_date)) AS udpate_date
FROM main_log a,
(
SELECT MAX(log_date) AS update_date, log_id
FROM log_a
GROUP BY log_id
UNION
SELECT MAX(log_date) AS update_date, log_id
FROM log_b
GROUP BY log_id
)b
WHERE a.id_log = b.log_id
GROUP BY b.log_id
but it gives
但它给
id last update
-------------------------
1001 null
1002 null
1003 null
1004 null
1005 null
...
I tried to put the conversion in the inner queries as well but it is the same.
我也尝试将转换放在内部查询中，但它是相同的。
I also tried to find answers on SO, mySQL documentation and Google but did not find why the conversion does not works when I make a group by.
我还尝试在 SO、mySQL 文档和 Google 上找到答案，但没有找到为什么在我创建group by.
回答by ModulusJoe
Your timestamp is in milliseconds try:
您的时间戳以毫秒为单位尝试：
SELECT a.id_workorder,
FROM_UNIXTIME(MAX(b.update_date/1000)) AS udpate_date
FROM main_log a, ...
(i.e. divide the time by 1000 to get seconds)
（即时间除以 1000 得到秒）
mysql> select FROM_UNIXTIME(1376750476349);
+------------------------------+
| FROM_UNIXTIME(1376750476349) |
+------------------------------+
| NULL |
+------------------------------+
1 row in set (0.06 sec)
mysql> select FROM_UNIXTIME(1376750476349/1000);
+-----------------------------------+
| FROM_UNIXTIME(1376750476349/1000) |
+-----------------------------------+
| 2013-08-17 15:41:16 |
+-----------------------------------+
1 row in set (0.02 sec)
mysql>
