International Replication Timestamps

Question

Hi all,

My scenario is as follows. I have a client and server. The client pulls data from the server by using a value that is stored locally called LastReplication.

This value is passed to the server whenever a request for replication data is made and the server compares it to a field in my database called LastUpdated and returns and rows where LastUpdated > LastReplicated.

This works fine for the most part but the problem is when I start dealing with international dates and times this strategy falls apart.

I have never written any replication ‘engines’ before and am not sure as to how to resolve this problem. I am assuming however that the solution involves either using epoch time or universal timestamps.

What is best practice in such a scenario?

Any advice would be highly appreciated…

Answer 1

Perhaps you could store your LastReplication values in UTC (Coordinated Universal Time), which can be computed as

UTC = (local time) - time_zone

For example, I live in Akron, Ohio, USA, which is currently in the -4 timezone. So to find UTC I take my local time (07:17 currently), subtract the time zone (expressed in hours) which is -4, and thus current UTC is 11:17. Your various servers would have to know which timezone they're in, and the timezone of a given location can change throughout the year (for example, in the US we use Daylight Savings Time during the summer which causes the timezone to change from -5 to -4 for my location, and then we shift back to Standard Time in the fall so the timezone changes from -4 to -5) but I'd think that most modern operating systems should be able to supply this value accurately.

I hope this helps.