Troubleshooting FactoryPMI One-Hour Date/Time Errors

Users enter a date and time, the database retains that value correctly, but FactoryPMI displays it one hour later when the record is retrieved. Follow the packet: the client submits the value, the Gateway transports and converts it, SQL Server stores it, and the return path converts it for display. Because SQL Server Management Studio shows the entered time correctly, begin at the physical hosts and then isolate conversion on the Gateway-to-client return path.

Where does the date/time value change?

Map both directions before changing any setting. On the write path, the client passes the entered date/time through the FactoryPMI Gateway to Microsoft SQL Server. On the read path, SQL Server returns the stored value through the Gateway, and the client renders it. A correct database value paired with a one-hour display error separates storage from presentation.

The date boundary is another discriminator: records dated the 24th or earlier displayed correctly, while newer records did not. A defect that changes across dates but not across stored values points toward daylight-saving rule evaluation rather than a fixed clock error. A fixed one-hour host-clock error would normally affect all dates handled under the same conversion path.

Check: Enter a test value, read the stored column directly in Management Studio, and retrieve the same row in FactoryPMI. Continue only when the database value matches the entry and the application alone is one hour ahead.

Do the client, Gateway, and database hosts agree?

Layer one first. Confirm each computer's clock, selected Windows time zone, and daylight-saving behavior before inspecting application options. Matching labels matter, but the selected label alone does not prove that every runtime has the same transition rules. Windows and Java maintain time-zone data independently.

The symptom occurred on four Windows Vista clients and two Windows XP clients. That breadth makes a single workstation configuration less likely. It directs attention toward a component common to every request, particularly the Gateway runtime, while still requiring the host checks because a mixed Windows estate can carry different time-zone updates.

1. Compare the current wall-clock time on the client, Gateway server, and database server.
2. Record the complete Windows time-zone selection on all three machines.
3. Inspect whether each operating system applies daylight-saving adjustment for that zone.
4. Restart any affected service after changing host time-zone settings; a long-running process may retain cached zone data.
5. Repeat the same record retrieval from more than one client.

Check: All three hosts must show the intended local time and the same zone selection. If multiple clients still show the identical one-hour shift while SQL remains correct, proceed to the Java runtimes.

Which Java runtime is each FactoryPMI process using?

Installing a newer Java package does not make an existing Windows service adopt it automatically. A service can remain bound to the executable configured by its wrapper or service definition. The installed Java version and the Java version actually running the Gateway are therefore separate facts.

In the affected configuration, the FactoryPMI client reported Java 1.6.0_10-b33, while the Gateway Status page reported Sun Microsystems Inc. 1.5.0_04. Updating Java on the Gateway computer and restarting the Gateway did not change that status value. The Gateway was still executing Java 1.5.0_04.

Use the FactoryPMI Gateway Status page as the authority for the active Gateway JVM. Do not infer it from the newest Java entry in the operating system. If the Gateway service points to an older Java installation, either configure the service to use the intended newer runtime or update the time-zone tables inside the runtime it actually uses.

Check: Restart the FactoryPMI service, reopen the Gateway Status page, and verify the displayed Java version. If it still reads 1.5.0_04, the service did not switch runtimes.

Why can matching time zones still produce a one-hour offset?

A time-zone identifier selects a regional rule set, not just a constant UTC offset. That rule set contains date-dependent daylight-saving transitions. Two machines can both display (GMT-08:00) Pacific Time (US & Canada) while their operating systems or Java runtimes apply different transition tables to the same calendar date.

That mechanism explains the date-sensitive split. Earlier records can render correctly because both rule sets agree for those dates. Later records can shift by one hour when one runtime applies daylight-saving time and another applies standard time. The database can still hold the correct wall-clock fields because the mismatch occurs when the application converts the returned value into a display date.

Check: Retrieve controlled timestamps on both sides of the observed date boundary. A shift that begins at the boundary confirms that the conversion depends on date-specific zone rules.

Should FactoryPMI auto-adjust dates for the time zone?

FactoryPMI 3.1.6 or greater provides the Gateway setting Autoadjust Dates for Timezone. For a deployment that expects the Gateway to adjust dates between configured time zones, select this option and restart the Gateway. A restart is part of the test because changing configuration without reloading the service leaves the old runtime state in use.

The affected system ran FactoryPMI 3.2.5, build 2184. Its Autoadjust Dates for Timezone option was initially cleared. Selecting it and restarting the Gateway produced no change. That result is diagnostic: the setting alone cannot correct obsolete time-zone tables in the JVM performing the conversion.

1. Confirm that the FactoryPMI version is 3.1.6 or greater before using this setting as part of the procedure.
2. Open the Gateway configuration and locate Autoadjust Dates for Timezone.
3. Select the option when the application design expects local time-zone adjustment.
4. Restart the Gateway.
5. Retrieve the same controlled database record again.

Avoid toggling this setting repeatedly or adding an application-level one-hour correction. A manual correction can reverse direction when the date crosses another daylight-saving boundary, and it can damage values that already render correctly.

Check: Compare the application display against the unchanged database value after the restart. If the offset remains exactly one hour, repair the active JVM's time-zone data.

How do you update the Gateway time-zone tables?

The corrective path depends on which runtime the Gateway service actually starts. Updating the operating system alone does not replace Java's internal time-zone tables, and installing a second Java version does not alter the first runtime's data.

1. Read the active Java version from the Gateway Status page.
2. Locate the Java installation used by the FactoryPMI service rather than selecting Java by installation date.
3. Choose one path: update that runtime's zone tables with TZUpdater, or reconfigure the service to launch the intended newer Java installation.
4. Restart the FactoryPMI service after applying the time-zone update or changing its Java path.
5. Return to the Gateway Status page and confirm which JVM is now active.

Apply the correction to every Java runtime that participates in conversion. The key missed hop in this installation was the Gateway, whose status continued to show 1.5.0_04 even after newer Java software had been installed on the server.

Check: The status page must identify the runtime you intentionally updated or selected. A successful installer message is not sufficient proof.

How do you protect stored data during diagnosis?

Keep storage and display remediation separate. The raw SQL values matched user entry, so rewriting them or subtracting one hour would convert a presentation defect into persistent data corruption. Do not compensate at the database layer while the write-path comparison remains correct.

The database in this installation was Microsoft SQL Server 2000 8.00.2039 Standard Edition on Windows NT 5.2, build 3790, Service Pack 2. Record the database type and column behavior because date/time types may not carry the same time-zone context that an application runtime applies during retrieval.

1. Select a recently affected row and record its primary key, entered value, raw SQL value, and FactoryPMI display value.
2. Select a row dated the 24th or earlier and capture the same fields.
3. Create new controlled test rows rather than modifying production history.
4. Retest the same rows after each Gateway change.
5. Remove any temporary display offset or query arithmetic used during diagnosis.

Check: Confirm that test writes still land in SQL with the exact entered date and time. Stop and investigate the write path if that comparison changes.

How do you prove the correction end to end?

Verification must exercise the complete path, the date boundary, and more than one client. A correct server clock or a changed JVM version proves only one hop.

1. Restart the FactoryPMI service after the final Java or TZUpdater change.
2. Open the Gateway Status page and record the active Java version.
3. From one client, enter controlled date/time values on both sides of the date boundary that previously separated correct and incorrect records.
4. Read each stored value directly in SQL Server Management Studio.
5. Retrieve each record through FactoryPMI and compare the displayed value with its raw SQL value.
6. Repeat the retrieval from a second client, preferably across the Windows Vista and Windows XP client groups represented in the installation.
7. Confirm that older records still display correctly and that no application, query, or database expression adds or subtracts one hour.

Final check: Pass the commissioning test only when the entered value, raw SQL value, and FactoryPMI display agree for records on both sides of the boundary from multiple clients.

FAQ