What causes "Invalid %s, datetime pattern should be '%s', value is '%s'." in apache/shardingsphere?

Make datetime-lower and datetime-upper strings conform exactly to datetime-pattern (including separators and time part)

apache/shardingsphere · error · InvalidDatetimeFormatException

Invalid %s, datetime pattern should be '%s', value is '%s'.

Error message

Invalid %s, datetime pattern should be '%s', value is '%s'.

What it means

Thrown by IntervalShardingAlgorithm.getDateTime when the datetime-lower (or datetime-upper) value fails to parse with the configured datetime-pattern (default yyyy-MM-dd HH:mm:ss). Interval-based table sharding derives table suffixes from these dates, so a malformed anchor aborts algorithm init.

Source

Thrown at features/sharding/core/src/main/java/org/apache/shardingsphere/sharding/algorithm/sharding/datetime/IntervalShardingAlgorithm.java:108

    private String getDateTimePattern(final Properties props) {
        ShardingSpherePreconditions.checkContainsKey(props, DATE_TIME_PATTERN_KEY, () -> new AlgorithmInitializationException(this, String.format("%s can not be null", DATE_TIME_PATTERN_KEY)));
        return props.getProperty(DATE_TIME_PATTERN_KEY);
    }
    
    private TemporalAccessor getDateTimeLower(final Properties props, final String dateTimePattern) {
        ShardingSpherePreconditions.checkContainsKey(props, DATE_TIME_LOWER_KEY, () -> new AlgorithmInitializationException(this, String.format("%s can not be null.", DATE_TIME_LOWER_KEY)));
        return getDateTime(DATE_TIME_LOWER_KEY, props.getProperty(DATE_TIME_LOWER_KEY), dateTimePattern);
    }
    
    private TemporalAccessor getDateTimeUpper(final Properties props, final String dateTimePattern) {
        return props.containsKey(DATE_TIME_UPPER_KEY) ? getDateTime(DATE_TIME_UPPER_KEY, props.getProperty(DATE_TIME_UPPER_KEY), dateTimePattern) : LocalDateTime.now();
    }
    
    private TemporalAccessor getDateTime(final String dateTimeKey, final String dateTimeValue, final String dateTimePattern) {
        try {
            return dateTimeFormatter.parse(dateTimeValue);
        } catch (final DateTimeParseException ignored) {
            throw new InvalidDatetimeFormatException(dateTimeKey, dateTimeValue, dateTimePattern);
        }
    }
    
    private DateTimeFormatter getTableSuffixPattern(final Properties props) {
        String suffix = props.getProperty(SHARDING_SUFFIX_FORMAT_KEY);
        ShardingSpherePreconditions.checkNotEmpty(suffix, () -> new AlgorithmInitializationException(this, String.format("%s can not be null or empty.", SHARDING_SUFFIX_FORMAT_KEY)));
        return DateTimeFormatter.ofPattern(suffix);
    }
    
    private ChronoUnit getStepUnit(final String stepUnit) {
        for (ChronoUnit each : ChronoUnit.values()) {
            if (each.toString().equalsIgnoreCase(stepUnit)) {
                return each;
            }
        }
        throw new UnsupportedSQLOperationException(String.format("Cannot find step unit for specified %s property: `%s`", INTERVAL_UNIT_KEY, stepUnit));
    }

View on GitHub (pinned to e952770a21)

Solutions

Make datetime-lower and datetime-upper strings conform exactly to datetime-pattern (including separators and time part)
If you want date-only values, set datetime-pattern: 'yyyy-MM-dd' explicitly
Quote the values in YAML to preserve leading zeros or special separators
Validate the pattern letters against java.time DateTimeFormatter syntax

Example fix

# before
- !SHARDING_ALGORITHM
  type: INTERVAL
  props:
    datetime-lower: 2020/01/01
    datetime-pattern: yyyy/MM/dd HH:mm:ss
# after
- !SHARDING_ALGORITHM
  type: INTERVAL
  props:
    datetime-lower: '2020/01/01 00:00:00'
    datetime-pattern: yyyy/MM/dd HH:mm:ss

Defensive patterns

Strategy: validation

Validate before calling

DateTimeFormatter f = DateTimeFormatter.ofPattern(props.getProperty("datetime-pattern", "yyyy-MM-dd HH:mm:ss"));
try { f.parse(props.getProperty("datetime-lower")); } catch (DateTimeParseException ex) { throw new IllegalArgumentException("datetime-lower does not match datetime-pattern"); }

Prevention

Keep datetime-pattern adjacent to every datetime value in reviewed config files
Unit-test rule YAML by constructing the algorithm in a config test

When it happens

Trigger: Configuring an INTERVAL algorithm where datetime-lower/datetime-upper do not match the datetime-pattern prop; e.g. pattern 'yyyy-MM-dd' but value '2020/01/01', or pattern omitted (default) while value is date-only.

Common situations: Custom datetime-pattern that does not exactly match the anchor values; date-only values with the default pattern; switching between AUTO_INTERVAL examples and INTERVAL props; locale-dependent separators.

Related errors

AI-assisted analysis of apache/shardingsphere@e952770a21 (2026-08-14). Data as JSON: /api/errors/680a9aedbcea2ca2. Report an issue: GitHub.