CustoSec:Time Zones in csv

From CustosecWiki
Jump to navigation Jump to search

There are several ways of exporting or extracting check outputs into a CSV-file for further processing. These files always contain a time stamp for a check. In the file the column with the time stamp might look like this:

LASTSERVICECHECK
1370037628
1370037628
1370037631
1370037631
1370037656

Background

The time stamp provided here is a UNIX time stamp which is a quite big number (integer). In MS EXCEL or Open Office CALC (or similar programs), these time stamps cannot be formatted with the standard "Date/Time" format of these programs. The reason is that the UNIX time stamp works different from the one, WINDOWS uses.

In WINDOWS, the time stamp is calculated as of the 1st of January 1900, given as a decimal with the days passed since this date before the comma and the hours, minutes and seconds after the comma.

In UNIX and Unix-like operating systems like LINUX this is different. In these systems the time is defined as the number of seconds that have elapsed since Thursday, 1 January 1970, 00:00:00 Coordinated Universal Time (UTC), not counting leap seconds. This moment is called "the Epoch".

Since the time stamp in UNIX/LINUX is encoded a signed integer, traditionally of 32 bits. This means it can cover a range of 136 years in total. The minimum time that can be represented is Friday, 13th of December 1901 and the maximum representable time is Tuesday, 19th of January 2038 03:14:08 UTC (This milestone being called the "Year 2038" problem - by then all systems should be on 64 bit any ways, which will give us another couple of thousand years...).

There is a lot of theory behind these concepts (think about leap seconds, days, etc.). For the purposes discussed here, it is enough to know, how to format the time stamp in the EXCEL/CALC File.

How to convert

To convert the UNIX-time stamp into a value, that can be formatted in EXCEL or Open Office, all that needs to be done is:

=> Divide the time stamp-value by the amount of seconds that make one day (24hrs * 60 minutes * 60 seconds = 86.400 seconds)
=> add the number of days between January 1st 1900 to January 1st 1970 (25.569 days)
The result is the time stamp that can be formatted by WINDOWS or Open Office.

Example

Let's assume the export-CSV from ARANSEC or CustoSec is opened in OO CALC. The first column (A) contains the time stamp as shown above and the report data starts as of line 1. Just insert another column behind column A and enter the following formula into cell B1:

  =A1/86400+25569

The result is another cryptic value, but this one can be formatted as "Date/Time" within OO CALC (or EXCEL). Then just copy down B1 into all other cells of column B.

The Time Zone Problem

It has to be kept in mind, that UNIX time stamps always represent the time in UTC (Coordinated Universal Time), which is a kind of standard "world time". If the time stamp in the EXCEL/CALC file should be shown in CET (Central European Time or in German "MEZ = Mitteleuropäische Zeit"), 1 hour has to be added (or 2 hours for summer time!).

In this case the formula from above would look like this (Adding 60 minutes + 60 seconds = 3.600 to UTC):

  =(A1+3600)/86400+25569

Adapt for other time zones accordingly.

Additional resources

There is a lot of information available on that topic. For a start, here is a Wikipedia article.