BOPIS, BORIS, and Curbside Pickup offer consumers and retailers the best of both worlds. …
Cloud-native POS platform for seamless omnichannel customer experience.
A single hub for all promotions campaigns.
The most advanced synchronization solution for databases and file systems.
Data configuration and batch automation across different disparate systems and vendors.
BOPIS, BORIS, and Curbside Pickup offer consumers and retailers the best of both worlds. …
Automated Personalization is revolutionizing retail—Discover how a small investment can bring a big impact. Online …
What is the Composable Store? Composable is one of the most common terms being thrown …
Creating a replication of hundreds or even thousands of similar databases and also being able …
One of the many advantages of using SymmetricDS is that you’re able to leverage the …
Overview The cloud is here to stay and getting data there to utilize all it …
Mark Michalek, a highly accomplished professional in the retail technology space, has been promoted to …
After nearly a century in business, the legendary Canadian fashion retailer is retooling to streamline …
Columbus, OH, June 6, 2023 – The Paper Store, a leading specialty retailer offering a wide …
Cloud-native POS platform for seamless omnichannel customer experience.
A single hub for all promotions campaigns.
The most advanced synchronization solution for databases and file systems.
Data configuration and batch automation across different disparate systems and vendors.
Supporting date time and timestamp values in MySQL can be challenging while replicating from a another database that uses different rules. In this article we walk through some issues with syncing date time values into MySQL and how to filter them out so that your replication stay on track.
SQL Modes and Invalid Dates
MySQL uses SQL Modes to determine how date and time’s will be accepted in a MySQL database. Often combinations of these settings can cause issues when replicating to and from MySQL if the other side is not using the same rules.
Some SQL Modes that might affect replication
How to determine what SQL Mode you have running
SELECT @@GLOBAL.sql_mode;
SELECT @@SESSION.sql_mode;
Timestamp Ranges
MIN : ‘1970-01-01 00:00:01’
MAX : ‘2038-01-19 03:14:07’
Datetime Ranges
MIN : ‘1000-01-01 00:00:00’
MAX : ‘9999-12-31 23:59:59’
SymmetricDS Load Filter
Here is a sample load filter that can be configured to clean any invalid TIMESTAMP or DATETIME data values that are out of range.
org.jumpmind.db.model.Table targetTable = engine.getDatabasePlatform().getTableFromCache(table.getCatalog(), table.getSchema(), table.getName(), false);
List timestampColumnIndexes = new ArrayList();
List datetimeColumnIndexes = new ArrayList();
int i = 0;
for (org.jumpmind.db.model.Column column : targetTable.getColumns()) {
if (column.getPlatformColumns() != null) {
for (Map.Entry entry : column.getPlatformColumns().entrySet()) {
if (entry.getValue().getType() != null && entry.getValue().getType().equals("TIMESTAMP")) {
timestampColumnIndexes.add(i);
} else if (entry.getValue().getType() != null && entry.getValue().getType().equals("DATETIME")) {
datetimeColumnIndexes.add(i);
}
}
}
i++;
}
String[] values = new String[0];
if (timestampColumnIndexes.size() > 0 || datetimeColumnIndexes > 0) {
values = data.getParsedData(org.jumpmind.symmetric.io.data.CsvData.ROW_DATA);
}
if (values != null) {
for (int j : timestampColumnIndexes) {
if (values[j] != null && (values[j].compareTo("1970-01-01 00:00:01") < 0 || values[j].compareTo("2038-01-19 03:14:07") > 0)) {
values[j] = null;
}
}
for (int j : datetimeColumnIndexes) {
if (values[j] != null && (values[j].compareTo("1000-01-01 00:00:00") < 0 || values[j].compareTo("9999-12-31 23:59:59") > 0)) {
values[j] = null;
}
}
}
return true;
Josh has worked in the IT industry for over 20 years as a developer, tech lead, and sales engineer. He has worked across a variety of businesses including retail, telecommunications, education, and healthcare. When he is not developing he enjoys whatever sporting event might be taking place.