Convert BigInt timestamp to real date with row aggregation and operations in mySQL

Question

I have a query that takes the latest update date (timestamp, but as a bigint (20) column) as follows:

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 returns the last update date (unix timestamp as 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 format in a date format and naively, although I can just format the external timestamp using FROM_UNIXTIME as follows:

 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 he gives

 id last update ------------------------- 1001 null 1002 null 1003 null 1004 null 1005 null ... 

I tried to include the conversion in internal queries, but this is the same.

I also tried to find answers to SO, mySQL documentation and Google, but did not find why the conversion does not work when I do group by .

Answer 1

Your timestamp is executed in milliseconds:

 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>