OVERVIEW

This article is to clarify what is epoch time and how to convert to human readable. Some of the DXi logs and data are in epoc and this article will come helpful for log analysis.

What Is UNIX Epoch Time?

UNIX Epoch time, or POSIX time, is a system for describing moments in time, defined as the number of seconds that have elapsed since 00:00:00 Coordinated Universal Time (UTC) on Thursday, 1 January 1970, not counting leap seconds.

 

-- Adapted from www.wikipedia.org 

Why Would I Need to Convert from Epoch Time?

Some logs and reports on DXi systems may use UNIX epoch time (such as the history bash under the DXicollect log or reports generated by the redb_util script). This article gives you some suggestions on how to convert UNIX Epoch time data to a human-readable format.

How to Convert

There are several methods to convert an epoch date to a standard date format. Here are three of them.

 

1. Converting Under UNIX

The following example shows commands you can execute on a DXi system.

 

Note:  If you decide to execute the commands on another UNIX machine, make sure they are supported.

 

# date -d @1338841300

Mon Jun  4 15:21:40 EST 2012

 

# date -d '70-1-1 + 1338841300 sec GMT'

Mon Jun  4 15:21:40 EST 2012

 

2. Formula for Converting in an Excel worksheet

=(A1/86400)+25569+(-5/24) - if you are using the Windows version of Excel, or any version that does not use the "1904 Date System".

=(A1/86400)+24106+(-5/24) - if you are using the Mac version of Excel, or any other version that uses the "1904 Date System".

 

A1 represents the value given in the cell where the epoch data is stored. If you get an error, such as seeing #VALUE! in the cell with the formula, copy and paste the value given in cell A1 (or whatever cell has the epoch date), rather than putting"A1" in the formula (or whatever the coordinates of that cell are).

 

-5 is the GMT offset (here, U.S. Eastern Standard Time, for which the GMT offset is "-5". Change this to the GMT offset for your time zone. Remember to take Daylight Savings Time into account, if your time zone is currently using it!

 

If the conversion for 1338841300 works correctly, you'll get this result:

 

 

This formula is useful if you export reports that use epoch time to Excel.

 

    3. Use Python

 

        Python has some built in modules to convert time. Here is function to utilize the Excel Plugin 'PyXLL' to convert Epoch time in a spread sheet.

 

        Reference to PyXLL https://www.pyxll.com

        

        Function:

 

from pyxll import xl_func

import datetime

 

@xl_func("float posix_time: str")

def pos_convert(posix_time):

newtime = datetime.datetime.fromtimestamp(posix_time).strftime('%Y-%m-%d %H:%M:%S')

return (newtime)

 

            Output:

            

 

 

 

Notes:

 

• Remember to format the cell that contains the formula in the appropriate "date" format. In Excel 2007 for Windows, to format the formula result as shown above, the template given in the Type: area of the Format Cells dialog  is 3/14/01 13:30.

   

• If the formula result you get for 1338841300 is 9/13/16 15:21, your spreadsheet is using the "1904 Date System". If you get this result, do one of the following things to correct it:
  • On a Mac, or if you can't specify a different date system, change 25569 to 24106 in the formula, as shown above.
  • In Excel 2007 on a Windows system, click the "Office Button" and choose Excel Options > Advanced. Scroll down to the When calculating this workbook area and uncheck Use 1904 date system. Click OK.

3. Using Internet Services

Several Web sites can do this conversion, such as http://www.epochconverter.com/

   

 

---