Cohesive Web

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.

Most of us are familiar with calendars in web pages. Small calendar showing date and year are really good to see and useful too. While in case of forms we have seen calendars attached with date fields. In some of my earlier projects I have designed date fields using simple drop down menu containing the data, month and year menu. It’s good but not very user friendly as user need to select 3 menus to choose a certain date. I had to stick with that format as I mostly use PEAR HTML_QuickForm to design forms in the web pages. Among many reasons the most unavoidable one is that it automatically generates client side validation JavaScript code. With time I realized that I should find some options to integrate calendar object with the QuickForm.

After giving a thorough search I selected jscalendar to incorporate with my form elements. The reasons behind selecting jscalendar are:

• It has a nice interface and wide variety of themes.
• Multiple integration options along with selectable date format to put the date in the text field.
• Support for integrating the calendar object with HTML_QuickForm.

Prior to integrate the jscalendar I need to upgrade HTML_QuickFom version to 3.2.10 as I found some code added in this version to support jscalendar. I also need to upgrade my PEAR DB package to version 1.7.13 and DB_Table to version 1.5.5. In my projects most of the cases I generate forms using DB_Table instead of using QuickForm directly. This gives an extra benefit for data validation and storing them in database.

After the up-gradating the required pear packages I worked upon integrating the jscalendar code. After a spending a moderate amount of time I was able to work jscalender with the QuickForm. But unfortunately when I was trying generate forms using DB_Table jscalendar component was not working. I was getting javascript errors.
I was following the method described by Philippe Jausions of 11abacus in jscalendar.php file. It was working fine with the QuickForm. In that case I was using addElements function to add jscalendar with proper options using the $option array like this –

[code lang=”php-brief”]
$options = array(
‘baseURL’ => ‘../js/jscalendar/’,
‘styleCss’ => ‘calendar-win2k-1.css’,
‘language’ => ‘en’,
‘button’ => ”,
‘setup’ => array(
‘inputField’ => ‘datefield1’,
‘ifFormat’ => ‘%d.%m.%Y %H:%M’,
‘showsTime’ => true,
‘time24’ => true,
‘weekNumbers’ => false,
‘showOthers’ => true
),
);
$form->addElement(‘jscalendar’, ‘ datefield1’, ‘My Date’, $options);
[/code]

I used this same option array in the column definition for the same field, as DB_Table uses the column definition for the attributes of the form elements of the form. I written like

Continue reading ‘QuickForm and Jscalendar’ »