将BigInt时间戳转换为mySQL中的行聚合和操作的真实日期 [英] Convert BigInt timestamp to a real Date with row aggregation and operations in mySQL

查看:934
本文介绍了将BigInt时间戳转换为mySQL中的行聚合和操作的真实日期的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有一个查询取最后更新日期(时间戳,但作为一个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

,它返回最后更新日期(unix时间戳作为一个bigint(20))对于任何种类的日志(a或b):

  id last update 
- ------------------------
1001 1376750476349
1002 1376753690861
1003 1378122801986
1004 1377764414858
1005 1377847226096
...

现在我想格式化日期格式的返回而我天真地,虽然我可以格式化外部e时间戳与 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

但它给了

  id最后更新
---------------------- ---
1001 null
1002 null
1003 null
1004 null
1005 null
...

我尝试将内部查询中的转换也是一样。



我也试图在SO,mySQL文档和Google上找到答案,但是当我创建一个 gro时,没有找到为什么转换不起作用由

解决方案

您的时间戳以毫秒为单位尝试:

  SELECT a.id_workorder 
FROM_UNIXTIME(MAX(b.update_date / 1000))AS udpate_date
FROM main_log a,...

(即将时间除以1000得到秒)

  mysql>选择FROM_UNIXTIME(1376750476349); 
+ ------------------------------ +
| FROM_UNIXTIME(1376750476349)|
+ ------------------------------ +
| NULL |
+ ------------------------------ +
1行集(0.06秒)

mysql>选择FROM_UNIXTIME(1376750476349/1000);
+ ----------------------------------- +
| FROM_UNIXTIME(1376750476349/1000)|
+ ----------------------------------- +
| 2013-08-17 15:41:16 |
+ ----------------------------------- +
1行集( 0.02秒)

mysql>


I have a query which takes the last update date (timestamp but as a bigint(20) column) like this:

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):

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_UNIXTIME like this:

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.

解决方案

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)

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>

这篇关于将BigInt时间戳转换为mySQL中的行聚合和操作的真实日期的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆