Filtering Dates in MySQL

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

• ALLOW_INVALID_DATES
• NO_ZERO_DATE
• NO_ZERO_IN_DATE

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;