Pentaho日期格式异常：Excel日期列导入后同步PostgreSQL遇错

Hey there, let's work through this date formatting problem you're facing. It's super common when dealing with mixed date formats in Excel, so here's a step-by-step breakdown to resolve it:

1. Take Control of Date Parsing in Pentaho #

Since your Excel column has two date formats (full YYYY/MM/DD and shorthand yy/MM/dd), Pentaho’s auto-detection will default to string type—totally expected. Instead of letting it guess, we’ll explicitly convert the string to a proper date object:

• Lock in the input type
  In your Excel Input component, manually set the problematic column’s data type to String (don’t let Pentaho auto-detect). This ensures you get the raw, unmodified value from Excel.

• Parse both formats with a Calculator or Java Expression
  Add a Calculator component, then create a new field with data type Date. Use this expression to handle both formats gracefully:

  Date.parse(input_date_field, "yyyy/MM/dd") == null ? Date.parse(input_date_field, "yy/MM/dd") : Date.parse(input_date_field, "yyyy/MM/dd")
  

  This tries to parse the full-year format first; if that fails, it falls back to the two-digit year format.

  Pro tip: If you need to force two-digit years to map to the 2000s (instead of Pentaho’s default 1970-2069 range), use a User Defined Java Expression with custom logic:

  String rawDate = input_date_field;
  if (rawDate.matches("\\d{2}/\\d{2}/\\d{2}")) {
      rawDate = "20" + rawDate;
      return Date.parse(rawDate, "yyyy/MM/dd");
  } else {
      return Date.parse(rawDate, "yyyy/MM/dd");
  }
  

2. Align with PostgreSQL’s Date/Timestamp Types #

Once you have a proper date object in Pentaho, make sure it matches your PostgreSQL table’s schema:

• If your PostgreSQL field is DATE (only stores year/month/day), just map your converted Pentaho date field directly—no extra steps needed.
• If it’s TIMESTAMP, Pentaho will automatically append 00:00:00 to the date, which should work perfectly (this is why your yyyy/MM/dd HH:mm:ss format worked for the full-year dates).

3. Troubleshoot Remaining Issues #

If some rows still fail:

• Log the raw input strings and converted dates using a Text File Output component. This lets you spot exactly which format is slipping through the cracks.
• Check PostgreSQL’s error logs for specific messages (e.g., invalid input syntax for type date: '23/05/10'). This will tell you exactly which value is causing the problem, so you can adjust your parsing logic accordingly.

内容的提问来源于stack exchange，提问作者Akshay