Teneo !!!

Aaron’s blog on Networking, and Enterprise Technology

Cisco Timestamps – Converting

This was so freaking confusing for me! In Java, I would convert my timestamps one way, but then, if I needed to convert them in Excel, I was 70 years off?!? WTF.

Well, here’s how you convert those timestamps to something meaningful, and why you are getting different results with different systems.

First, some terms:

Unix Epoch: number of seconds elapsed since Jan 1, 1970

NTP Epoch: number of seconds elapsed since Jan 1, 1900

I have no idea why the different Epochs, but did you notice that the difference is 70 years? YEP!!!

Okay, so Microsoft Products use the NTP Epoch (i.e. SQL Server, Excel, Access, etc) as a reference to build it’s Date objects. Unix, Macintosh, Java, and C / C++ uses the Unix Epoch as a reference for it’s date objects. Cisco uses Unix Epoch to export its timestamps.

So, basically, we have to add 25,569 days (70 years, approx) to the NTP Epoch, in order to get valid results in NTP Epoch-type systems (Excel, SQL Server, etc).

Convert a timestamp in JAVA:

public static void main(String[] args){

// declare our timestamp in seconds

long timestamp = 1198167416;

// Since timestamp is in seconds, but Java Date works in milliseconds,

// convert to milliseconds
Date mydate = new Date(timestamp*1000);

// Format how the date is displayed to us

SimpleDateFormat formatter = new SimpleDateFormat("dd MMM yyyy HH:mm:ss");

// Print the date to standard out.

System.out.println(formatter.format(mydate));
}

Convert a timestamp in Excel (Assuming your timestamp to convert is in column A1):

A1/86400+25569

Then, just format the cells as “Date”. So, to explain the Excel formula:

1). Divide the timestamp by the number of seconds in a day. This will give you the number of days

2). Add 25569 days to the already converted days, to take into account the difference between Unix Epoch and NTP Epoch shift.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: