Comparing timestamps for syncing?

General
1

I know that anytime you talk about syncing, it’s a loaded question, but perhaps this won’t be such a powder keg.

I have two tables that may have new records added to each and I want to sync them with the new records and changes. At this point, I’m not worried about the business rules because that will be the next step. I’m only worried about getting a timestamp in each table so I can determine which one got added or modified.

One of the two tables will always be local (client based) and the other will be remote or server based. Each client can access their own local table as well as the server table. Users can add, modify, and delete records in their local table as well as the server table. If a user deletes a record in the server table, it will not affect anyone’s local tables although it will no longer sync back and forth. That will not create a problem for this application.

I currently put two fields in every record of every table that I create:

Created = local timestamp when record is create
Modified = local timestamp whenever record is modified

I’m thinking that I can add two additional fields:

CreatedGMT = GMT when the record is created
ModifiedGMT = GMT when the record is modified

I then keep track of the last sync performed by the user and determine what to update by using the two GMT fields.

Assuming I don’t need to worry about any business rules at this point and only worry about timestamps, would this work?

What about the effect daylight savings would have on the modification of records? Anyway to keep DST from affecting this or am I overthinking it.

Apologies to anyone offended by another syncing thread.

2

Convert the dates to GMT before you store them.

3

Tim

I want to retain the original date and time stamps because these are displayed on the screen. I would use the CreatedGMT and ModifiedGMT for syncing only.

Does that make sense?

4

No. Convert them to local time on display so they will display correctly when the user changes time zones, such as when traveling.

5

Tim and Thom are both providing excellent advice. Store all dates and times in GMT and then display them in local time.

6

From experience, you really want to store everything as GMT and display in the local time zone. This eliminates all confusion with daylight saving time or time zone differences.

7

OK, you convinced me already!

8

To be fair, I prefer being more explicit. Storing in GMT is an implied behavior and relies on the application layer to know that. I prefer to store the offset in the database, even it is only +00. This allows the database’s conversion functions to operate correctly all the time.

But either is acceptable. I think it depends on how much work you like the database to do for you. I for one load tons of logic (functions, triggers, custom types, etc) into my databases.

9

To confirm my understand - when I write SQL as follows…

as an example, is the sql engine converting that ‘now’ datetime to gmt before writing it?

10

Yes, I believe that is correct.