Archive for August 2008

Converting Excel date/time to Unix timestamp

Timestamp conversion are required when you import information generated by Windows based application to UNIX system. I faced a similar type of problem when I was trying to read data from a MS Excel file and storing it in MySQL database using a php code.

I was using Excel_reader class to read the Excel file. In that file there were two columns for 2 dates. While reading those column value I found they contain some numeric value. Those values are not same as unix timestamp value. As I was trying to store those values as timestamp I need to convert them into the timestamp.

After some searching and reading from internet I found that Windows and Unix system stores timestamp in different manner. Once this is clear to me I have converted them easily. So let us understand how these two system stores timestamp .

In Unix system timestamp is stored as integer in terms of seconds. If we consider the current timestamp value in Unix then it is basically the no. of seconds passed from 1 January 1970 00.00Hrs to till now. One important thing for Unix timestamp is that it is measured with reference to GMT time. That means there is no time-zone adjustment considered in case of Unix timestamp.

In case of Windows time it is stored as floating point/real number but in terms of days. For current timestamp, the no. days spent from 1 January 1900 stored in integer part and fraction of the day stored in the fractional part of the timestamp.

Now comes the conversion process. I made a simple conversion calculation as follows:

  1. $DayDifference = 25569; //Day difference between 1 January 1900 to 1 January 1970
  2. $Day2Seconds = 86400; // no. of seconds in a day
  3. $ExcelTime //integer value stored in the Excel column
  4. $UnixTime = ($ExcelTime $DayDifference)*$Day2Seconds;

Now if you look at the calculation, you may find that the conversion is not absolutely accurate. This is because I haven’t consider the fractional part of the Excel time. The reason behind doing so is that all I need to do is to convert a date from Windows format to Unix format. So fraction part of windows time won’t affect a lot for my requirement. But if you use this calculation for converting a date-time from Windows format to Unix format then conversion of the fractional part should done.
If you like this small tips please let me know about it.

I got my new logo

For me its a celebration time! Wanna know why ?

Well I got my new logo for my website and company. At first take a look at it.

WCE Logo

Designed by my friend Nilkamal. Before he start working on it he asked me a simple question.

Do you dream about it?

I said yes. After 2 days this is the outcome. Simple but caries the senses. I like this guy, not because he is my friend but because of his artistic senses. I also love his professionalism. Together we had lot discussions related to art, culture, trends in visualization jobs and many more. Some day I will try to take his interview and then post it here.

** This is a long waiting post, as I was very busy with my jobs. Also I found some problem with my blog, which needed fresh installation. So I upgrade it with current version and make this post live.