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:
[code lang=”php-brief”]
$DayDifference = 25569; //Day difference between 1 January 1900 to 1 January 1970
$Day2Seconds = 86400; // no. of seconds in a day
$ExcelTime //integer value stored in the Excel column
$UnixTime = ($ExcelTime – $DayDifference)*$Day2Seconds;
[/code]
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.
