Convert epoch time to 'human' time in a MySQL query

[benducklow]

I do a fair amount of manual queries directly from the 'jamfsoftware' database utilizing applications like Sequel Pro/MySQL Workbench and I am having trouble converting or getting the epoch time from the `computers_denormalized` table into something that is usable for reporting. I am running the below but am getting NULL values in return:

SELECT `computer_name`,
FROM_UNIXTIME(last_contact_time_epoch,"%Y-%m-%d") AS 'last check-in',FROM_UNIXTIME(last_contact_time_epoch) AS 'last inventory update'
FROM `computers_denormalized`

I know I could use the GUI or manually copy and paste the epoch numbers into some online converter but I am looking for something that I can do right in the sql code. Are there any sql gurus out there that could help?

Thanks!

[iJake]

Divide the epoch time by 1000.

[benducklow]

I'm looking for something in one of these formats:

06/24/2014 1:15pm
Jun 24 2014 13:15

...or something like that. I'm looking for a result that clearly states the date and time, not in seconds or anything like that.

[iJake · Accepted solution]

SELECT `computer_name`,
FROM_UNIXTIME(`last_contact_time_epoch`/1000,"%Y-%m-%d") AS 'last check-in',FROM_UNIXTIME(`last_contact_time_epoch`/1000) AS 'last inventory update'
FROM `computers_denormalized`

EDIT: You can mess with the formatting but this will do what you want. All I did was have the query divide 13 character epoch time by 1000 so to get it to the 10 character that MySQL can handle.

[benducklow]

You are a scholar and a gentleman! Here is what I ended going with, thanks to you!

SELECT `computer_name`, FROM_UNIXTIME(`last_contact_time_epoch`/1000,"%m/%d/%Y  %h:%i") AS 'last check-in',FROM_UNIXTIME(`last_report_date_epoch`/1000,"%m/%d/%Y  %h:%i") AS 'last inventory update'
FROM `computers_denormalized`
ORDER BY `last_contact_time_epoch` ASC

THANK YOU!

[iJake]

Glad to help.

[powellbc]

Here is another option that my DBA assisted me with (this example is just name and last check in):

select computer_name, FROM_UNIXTIME(substring(last_contact_time_epoch,1,10)) AS 'Last Check-in';

: