4

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?

riddle_me_this
  • 8,575
  • 10
  • 55
  • 80
user1791574
  • 1,729
  • 2
  • 16
  • 30

2 Answers2

13

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
Basil Bourque
  • 303,325
  • 100
  • 852
  • 1,154
Eelke
  • 20,897
  • 4
  • 50
  • 76
  • 1
    Is there any way to get this min/max value in Java instead of hardcoding (like LocalDate.MIN and LocalDate.MAX)? – Bakul G Jan 30 '23 at 14:41
5

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.

Meno Hochschild
  • 42,708
  • 7
  • 104
  • 126
  • Thanks for the answer. setLenient(false) did not work for me. I used `cal.get(Calendar.YEAR) > 9999)` to fix the issue. – user1791574 Jul 07 '15 at 08:24
  • Aren't you supposed to do GregorianCalendar.getInstance() reather than new GregorianCalendar()? – Alkanshel Apr 06 '18 at 19:27
  • @Amalgovinus I strongly advise people to use the concrete calendar if ever possible. Here, the OP obviously wants the gregorian calendar, not for example the buddhist calendar in Thailand. The often cited rule to use interfaces or abstract classes does not apply well for date/time-issues, see also the documentation of the newer interface `java.time.chrono.ChronoLocalDate`. – Meno Hochschild Apr 07 '18 at 13:35
  • I wasn't referring to which calendar class, but rather, whether to use "getInstance()" or "new" when making it? – Alkanshel Apr 08 '18 at 19:44
  • @Amalgovinus When using `getInstance()` then you can get a non-gregorian calendar (will practically only be the case if your default locale is Thailand). This has an undesirable and mostly unexpected deep impact on year numbers, for example. When using the constructor then you are guaranteed to get the gregorian calendar else not. Furthermore: `GregorianCalendar.getInstance()` is the same as `Calendar.getInstance()` using static inheritance (the result is NOT bound to the gregorian calendar by design). – Meno Hochschild Apr 09 '18 at 04:32