Skip to main content

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!

Divide the epoch time by 1000.

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.

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.

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!

Glad to help.

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';


:

Terms & ConditionsCookie settings