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
.
See Question&Answers more detail:
os 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…