Forums

by WeiYing >> Tue, 28 Jul 2009 16:57:36 GMT

Hi

We have a modifiedTimeStamp field on all our data classes. This is mainly for Auditing purposes to identify a listing of who and when the data item in question was updated.

We are trying to decide whether this timestamp value should be stored as the UTC or as timestamp directly from the app server which is the local time.

The issue is around when daylight saving changes take place particularly when the clock shifts back an hour.

Say,
- User A puts through an update (Event A) in the UK on Oct 25 at 01:30 (GMT+1).
- Then, at 02:00 (GMT+1) of Oct 25, the clock shifts back one hour and it is now back to 01:00 (GMT) of Oct 25.
- Then, Event B takes place where User B puts through an update on Oct 25 at 01:15 (GMT).

Option 1: storing timestamp as local app server time.
This results in Event B (01:15) incorrectly appearing as it happened before Event A (01:30).

Option 2: storing timestamp as UTC
Event A (Oct 25 00:00 UTC) will correctly appear as it happened before Event B (Oct 25 01:15 UTC). However, on Oct 26 after converting the stored UTC back to the local on the screens, Event B is correctly shown as occuring on Oct 25 at 01:15. However, Event A is now incorrectly shown as occurring on Oct 25 at 00:30 (User A jumping up and down denying that he/she did any update at 00:30 on Oct 25)

Anyone has any thoughts on this? Or come across this problem before?

by BeeJay >> Tue, 28 Jul 2009 22:21:28 GMT

We had a similar problem in our system when adding multiple timezone support and to solve this we now store all timestamps in UTC. When converting back for display purposes we use the timezone offset that would have applied at the timestamp in question not the users current timezone offset to avoid the 1 hour timeshift that occurs after swapping for a DST change.

ie: You can't use the Windows supplied routines for converting the UTC time to local time if you want it to be accurate independent of the current DST setting versus the DST setting that was in force at the timestamp in question.

When the user enters a timestamp in the "skipped" hour when entering DST, you need to reject the timestamp entry and advise them that hour did not exist due to DST change.

When the user enters a timestamp in the "repeated" hour when coming off DST, you need to clarify from them whether it was the for the first or second occurrence of that "timestamp" so that you can calculate the appropriate UTC time.

You'll find this is the subject of considerable debate on the Net and it is not a problem unique to Jade. Have fun!!