Timestamp out of range error on Postgres with a java.util.Date input

Question

I am creating a Date object from a String in which the year is 2015123 and then trying to save it to Postgres. It's creating a Date object properly in the Java code but giving error timestamp out of range while saving it to the database.

SimpleDateFormat sdf = new SimpleDateFormat("MM/dd/yyyy");
Date date = sdf.parse("06/09/2015123"); // Here I inserted year 2015123.

It's converting a date object like this.

2015123-06-09 00:00:00.0

While I am trying to save it in Postgres it gives me an error.

timestamp out of range

I want to validate the Date object in java. Is there any possibility to validate in Java in spite of the Postgres error?

Answer 1

Out Of Range

PostgreSQL doesn't support timestamps in that range.

That particular year would be within range of the date type (which has no time part). But neither of the timestamp types can accommodate the year 2,015,123.

For the supported ranges see the doc page, Date/Time Types.

Ranges (in default build)

• Timestamp
  • 4,713 BC to 294,276 AD
• Date
  • 4,713 BC to 5,874,897 AD

Answer 2

The method setLenient(false) will not help. But you can do this:

SimpleDateFormat f = new SimpleDateFormat("MM/dd/yyyy");
f.setLenient(false);
Date date = f.parse("06/09/2015123"); // Here I inserted year 2015123.
System.out.println(date); // Sat Jun 09 00:00:00 CEST 2015123
GregorianCalendar cal = new GregorianCalendar();
cal.setTime(date);
System.out.println("Out of range: " + (cal.get(Calendar.YEAR) > 9999)); // Out of range: true

The SQL-specification only mandates support for years until 9999. PostgreSQL even goes until 294276 AD for timestamp columns but this is still not enough. You should define your own sensible upper limit because even 9999 is probably wrong from a user perspective.