Please don’t ever use time stamps for synchronization. You will have a bad time.
Clock skew is an insidious problem: things may look like they’re working, but they’re not. Given all of the clock bugs out there it’s sometimes not even safe to rely on the clock on a single machine. Across multiple machines it’s hopeless.
Please note that this doesn’t apply if you have a fancy time source like Google uses in their fancy cloud DB (whose name escapes me at the moment).
While I agree that 99% of the time you are right. There are edge cases where a time stamp is best.
I’m looking for a system using SQLite that gives you the same eventual consistency guarantees as CouchDB/PouchDB on consumer devices. You need to be able to have nodes disconnect from each other and the internet for long periods of time before syncing. Hours or days not seconds or milliseconds.
If two nodes overwrite the same row while disconnected you need a deterministic way to decide which is the winner. This could be though a hash of the values in the row (lowest value wins) or using a timestamp. Pros and cons for each and it depends on use case. So for example if you are building a notebook application you almost certainly want the most recent update to win. The transaction rate would be low and devices with an internet connection will be “relatively” accurate on time.
Obviously this could be open to abuse, someone changing their clock to force their edit to win. But again pros and cons. The use case I’m looking for is within a trusted group where this wouldn’t be a problem.
This is nice if you can afford to have atomic clocks in your data centers and whole infrastructure teams working on keeping your clocks in sync (read: if you are Google) but otherwise you're probably gonna have a bad time.
I think those clocks are coming down in price but the bigger challenge is the logistics - for whatever reason shared data centers don’t run in a GPS signal from the outside that all their clients can share.
Well within the price range of smaller data centers, ~$1500 to build the open source reference card published by the open compute project: Open Time Server
Clock skew is an insidious problem: things may look like they’re working, but they’re not. Given all of the clock bugs out there it’s sometimes not even safe to rely on the clock on a single machine. Across multiple machines it’s hopeless.
Please note that this doesn’t apply if you have a fancy time source like Google uses in their fancy cloud DB (whose name escapes me at the moment).