Skip to main content
Solved

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

  • June 24, 2014
  • 6 replies
  • 36 views
B
Forum|alt.badge.img+17

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!

Best answer by iJake

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.

    6 replies

    iJake
    Forum|alt.badge.img+23
    • iJake
    • Contributor
    • June 24, 2014

    Divide the epoch time by 1000.

    D
    B
    Forum|alt.badge.img+17
    • benducklow
    • Author
    • Valued Contributor
    • June 24, 2014

    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
    Forum|alt.badge.img+23
    • iJake
    • Contributor
    • Answer
    • June 24, 2014
    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.

    B
    Forum|alt.badge.img+17
    • benducklow
    • Author
    • Valued Contributor
    • June 24, 2014

    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
    Forum|alt.badge.img+23
    • iJake
    • Contributor
    • June 24, 2014

    Glad to help.

    P
    Forum|alt.badge.img+14
    • powellbc
    • Contributor
    • August 18, 2014

    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 settingsAccessibility statement