Trouble with Timestamps

The timestamp data type can cause compatibility issues for cross-platform data replication because database platforms have varying support for the fractional second precision. Let’s look at what problems can happen with timestamps and explain how to avoid them.

The timestamp data type can store a date and time, with time precision down to a fraction of a second. The precision is specified in parenthesis when defining the column on a table, such as timestamp(6) for 6 digits of fractional second precision. The number of fractional digits supported by database platforms can vary. For example, PostgreSQL allows up to 6 digits of precision, while Oracle allows up to 9 digits of precision.

Data replication between a database with higher precision (more digits) and a database with lower precision (fewer digits) can lead to data loss and conflicts. Timestamps are a common data type, so there’s a good chance for issues to arise. From a review of 108 of JumpMind’s data replication customers conducted this past year, 53% were using timestamps on their tables and 24% were using timestamps in the primary key.

Low to High

When syncing from a low to high-precision database, the timestamps will sync correctly. If data replication is bi-directional, the timestamps will only sync correctly for changes that originate from the low-precision database.

Step	Low Precision	Direction	High Precision
1	2022-07-01 01:00:00.123	→	2022-07-01 01:00:00.123000
2	2022-07-01 01:00:00.123	←	2022-07-01 01:00:00.123000

As soon as changes to timestamps originate from the high-precision database, issues for high to low precision need to be considered.

High to Low

When syncing timestamps from a high to low-precision database, data replication will probably perform without error, but leave timestamps truncated to a lower precision. If timestamp columns are informational, like a create time column, then there may be little impact to the application.

However, when using timestamps in the primary key, there are some potential problems with replicating data. For rows that originate on the high-precision database, they may collide and merge on the low-precision database.

Step	High Precision	Direction	Low Precision
1	2022-07-01 01:00:00.123111	→	2022-07-01 01:00:00.123
2	2022-07-01 01:00:00.123222	→	Merges into same row

If rows are changed on the low-precision database, it may appear as a duplicate row after replication.

Step	High Precision	Direction	Low Precision
1	2022-07-01 01:00:00.123111	→	2022-07-01 01:00:00.123
2	2022-07-01 01:00:00.123000	←	2022-07-01 01:00:00.123

False Conflicts

For data replication software, the loss of precision can cause a false conflict to be detected, either because of the primary key collision or because the source old data doesn’t match the existing data at the target database. Data replication software like SymmetricDS can automatically resolve the conflicts by choosing the newer row, avoiding a data replication error, but there is still data loss possible with merging of rows and loss of precision. Also, if false conflicts are constantly being detected, the system can experience more overhead from continually evaluating the winner and resolving conflicts.

Inaccuracy of Precision

SQL-Server and Sybase ASE have a special case with a datetime data type that allows for 3-digits of precision (milliseconds), but it can only store numbers in increments of .000, .003, and .007. When data originates from a system that can use the full range of milliseconds, data will be naturally out-of-sync, causing duplicates for a primary key or false conflicts for other columns.

Step	PostgresSQL	Direction	SQL-Server
1	2022-07-01 01:00:00.002	→	2022-07-01 01:00:00.003
2	Duplicate row or false conflict	←	2022-07-01 01:00:00.003

When using data replication, the datetime type should be avoided, and the replacement types of datetime2 (SQL-Server) and bigdatetime (Sybase ASE) that provide accurate precision should be used instead.

Solutions

To avoid trouble with timestamps, be sure to specify the timestamp fractional second precision on both the source and target tables using the lowest common precision between the databases. It’s best to specify precision instead of accepting the database’s default precision.

Another less common approach is to modify the application to carefully truncate the fractional second to a common precision before saving timestamps. But someone could still modify data directly in the database and cause an issue, so it’s better to constrain the data through the data type definition.

If data is replicating in one direction, or there is legacy use of an inaccurate type, then it is most likely acceptable to disable the conflict detection to avoid false conflicts. Data replication software like SymmetricDS allow conflict configuration at the table level with options to disable detection or use less stringent detection like primary key only.

Summary

We’ve seen that a difference in timestamp precision between two databases being replicated can lead to problems. When using timestamps in a table, the following issues are possible:

High to Low: Loss of precision on timestamp columns.
Bi-directional: Loss of precision on timestamp columns on the low-precision database.
False conflicts whenever timestamp columns are changed on the low-precision database, causing overhead to resolve.

When using timestamps in the primary key, the following issues are possible:

High to Low: Collision and merging of rows.
Bi-directional: Collision and merging of rows on the low-precision database.
Duplicate rows on the high-precision database.

Avoiding trouble with timestamps can be as simple as using matching table definitions, modifying the application to truncate precision, or adjusting the conflict management system. When selecting data replication software, consider a solution like SymmetricDS that has strong cross-platform support and features to handle precision in timestamps.