Problem: Timezone Mismatch Between DB Server and Application Server

Symptoms:

• DB Storage: UTC time is saved in the database (e.g., 2014-01-01 00:00 UTC).
• Web Application: When retrieving this data from the DB and sending it to the client, the time returned is incorrect. The time is offset by the application server's time zone, resulting in an incorrect time (e.g., 2013-12-31 15:00).

Cause:

• The issue arises because when iBatis retrieves a timestamp from the database, the timestamp does not contain any timezone information. iBatis assumes that the time should be adjusted based on the server's time zone.
• iBatis internally reads the server's time zone and applies that offset to the retrieved timestamp, causing the time to be wrongly adjusted. Specifically, when the server is in a time zone like KST (Korean Standard Time, UTC+9), it will mistakenly adjust the time by -9 hours to convert it back to UTC.

Example Breakdown:

• DB Time: The value 2014-01-01 00:00 is stored in UTC in the database.

• Server Timezone: The application server is set to KST (UTC +9).

• iBatis Behavior: When iBatis retrieves this UTC time, it detects the server's time zone (KST, UTC+9) and adjusts the timestamp by subtracting 9 hours. So the time becomes 2013-12-31 15:00.

  • Stored Time in DB (UTC): 2014-01-01 00:00 (UTC)
  • Time Retrieved by iBatis (KST): 2013-12-31 15:00 (KST) — because iBatis adjusted the UTC time for the server's time zone.

Solution:

Best Solution:

• Synchronize Time Zones: The best solution is to ensure that the DB server and the application server are using the same time zone. This way, the time zone conversion issue doesn't occur, and the times will be consistent.

  • Set the application server to use UTC or ensure both the DB and application servers are in the same time zone.

Alternative Solution:

• Custom TimeStamp Handler: If you cannot change the time zone configuration of the servers, you can implement a custom TimeStamp handler in iBatis to manually adjust the time after it is retrieved from the database. The handler would need to:
  • Adjust the retrieved UTC time back to the correct value based on the application server's time zone.
  Steps:
  1. Retrieve the timestamp from the DB (UTC time).
  2. Check the server's time zone.
  3. Adjust the timestamp by adding the server's time zone offset (e.g., +9 for KST) to convert it to the correct local time.
  Example of Time Adjustments:
  • Stored Time in DB: 2014-01-01 00:00 (UTC)
  • Time Retrieved by iBatis: 2013-12-31 15:00 (because iBatis wrongly adjusted it based on KST).
  • Using TimeStamp Handler: The handler will take the time 2013-12-31 15:00 and add 9 hours to convert it back to UTC, resulting in 2014-01-01 00:00 (UTC).

Change in Process:

1. Stored Time in DB (UTC): 2014-01-01 00:00 (UTC)
2. Retrieved Time by iBatis (with KST): 2013-12-31 15:00 (KST) — due to the automatic conversion by iBatis.
3. After TimeStamp Handler Adjustment: 2014-01-01 00:00 (UTC) — adjusted back to the correct value.

Conclusion:

To prevent this issue:

1. Sync the Timezones: Ideally, set both the DB server and the application server to the same time zone (preferably UTC).
2. Use TimeStamp Handler: If time zones cannot be synchronized, use a custom handler to adjust the retrieved time back to UTC or the correct local time.

This approach ensures consistent time handling across different systems, preventing timezone-related issues.