I ran into a strange error with ColdFusion and CFGrid recently. It seems that when using timestamps, loaded from a database, within the CFGrid, CFGrid will modify the time so that it displays based on the time zone of the computer that is running the application (e.g. if the time is saved as 08:00 on a database in the Mountain Standard Timezone (MST) or (UTC-6), it will display as 07:00 to a user on a computer in the Pacific Timezone (UTC-7).
I came across this while working on an application being used by some folks in India. The application runs ColdFusion (version 7) and makes heavy use of flash-based forms.
In a certain part of the application, users must enter the dates and times of specific tests that they run. They do this with a cfform that separates the date and time into individual fields (e.g. there is a numeric field for day, month, year, hours and minutes). Then, I use a custom ColdFusion component to convert my value into an Oracle timesamp; why does working with dates and times always take so many steps.
When they post the form, all of these values are combined so they can be entered into an Oracle database:
valueForDatabase = CreateODBCDateTime(form.MONITORMONTH & '/' & form.MONITORDAY &'/' & form.MONITORYEAR & ' ' & form.MONITORHOUR & ':' & form.MONITORMINUTE); valueForDatabase = application.databaseObject.toOracleTimestamp(valueForDatabase );
So, that part was working just fine. However, below the data entry form I had installed a cfgrid with format=flash as one of the grid attributes. The grid would display each of the tests; users can enter as many tests into the form as they want. the code for the grid looks something like this:
<cfgrid format="flash" query="myColdfusionQuery" name="testGrid" delete="yes" selectmode="browse" deleteButton="Delete" colheaderbold="yes" rowHeaders="no" hrefKey="patientId" height="300"> <cfgridcolumn header="MONITERED ID" display="no" name="KEYFIELD" /> <cfgridcolumn header="MONITERED TIME" name="MONITORTIME" width="150" mask="MMMM DD YYYY, JJ:NN" type="date" /> </cfgrid>
When the users would see the test in the grid, the time would be completely different than what they had entered into the database. From what I can tell, this is because they save it into the database, based on the time in India. The database records the exact time entered and returns it via a cfquery. The cfquery results are all correct. However, when loaded into the cfgrid, the time is modified to display based on the users machine.
I know that the data from the database is correct. When I run a cfdump of it to the screen, all the dates and times are correct. They only change after rendering within the cfgrid.
<!-- dump query values --> < cfdump var="#myQueryResult#" />
Solution
After some searching, I couldn’t find a good/easy solution to this problem. It seems to be an error with the way Flash and ColdFusion work together and I didn’t want to spend a lot of time debugging that. So, I decided to abandon using the cfgrid and use the jquery jqgrid instead.