Maximum possible date in JPA

Question

Is there a way to represent the maximum (and minimum) possible date in JPA without being database specific? The JPA implementation should be then be able to translate it to the correct value according to the used dialect.

If not JPA an Hibernate specific solution would also be OK.

Edit

In an application we are using there are several named queries which test if a date is in a given range

SELECT ... FROM ... WHERE somedate BETWEEN startdate AND end date

If the range is open (only startdate, or only enddate) it would be easier to use the maximum and minimum date that to have different queries with just a single comparison (this would require several changes in the existing application that I would like to avoid).

Edit 2

For the not a real question voters: the question is:

It is possible to query for the maximum possible date in a database agnostic way?

Edit 3

The application is storing dates as java.util.Date as follows

@Temporal( TemporalType.TIMESTAMP )
private java.util.Date someDate;

A comment on the close vote could be useful. Not pertinent? Duplicate? If it is a duplicate it would be not bad to mention a link. — Matteo, Oct 08 '12 at 05:53
I'm not the one who voted down nor voted to close, but why would you need this info, will you use it in a real application? — Luiggi Mendoza, Oct 08 '12 at 05:54
If that's the case, why don't use `end` as '3000-01-01'? I know it's not the best answer, but I doubt the same application would be used for 1000 years. — Luiggi Mendoza, Oct 08 '12 at 06:00
@LuiggiMendoza It is what I am doing now but I was wandering if there was a *cleaner* solution — Matteo, Oct 08 '12 at 06:23
obviously a date can be stored as many things ... Long, String, DATETIME (or equiv) — DataNucleus, Oct 08 '12 at 08:30

score 4 · Accepted Answer · answered Oct 08 '12 at 10:05

I believe there is no such feature (yet) to get the max/min date in DB neutral manner.

I'd suggest you to, base on the DBMS you are going to use, define corresponding constants in both Java and DB stored functions.

Therefore you can do something like:

Query query = session.createQuery("from Foo where date between :fromDate and :toDate");
query.setParameter("fromDate", DateConstants.MIN_DATE);
query.setParameter("toDate", DateConstants.MAX_DATE);

(I used to call that FOREVER and EPOCH :P )

or even have HQL like this (if you have create stored function in DB)

from Foo where date between MaxDate() and MinDate()

Ok I will then have a function returning the max value according to the DB: according to the current DB I return the corresponding date (and if the DB is not known/supported I return something like 9999) — Matteo, Oct 08 '12 at 10:17

score 1 · Answer 2 · answered Oct 08 '12 at 17:21

1

One option is to define a database function that gives you that information. Define it for each database vendor you plan to use. Then use that function to obtain the max date for the "currently used" db.

answered Oct 08 '12 at 17:21

Steve Ebersole

9,339
2
48
46

Also a good idea, I don't know if it will be easier or not as the per product constants. – Matteo Oct 08 '12 at 18:12
I think it depends how you define easy. – Steve Ebersole Oct 08 '12 at 18:19
Exactly :-) In any case I noticed that working with `Date`s and JPA is not really an ideal world (too many DB-specific stuff as the handling of something smaller than a second on MySQL, the differences in the maximum and minimum values, ...) – Matteo Oct 08 '12 at 19:08

score 0 · Answer 3 · edited May 23 '17 at 12:04

0

Take a look at Best way to get maximum Date value in java?. There should be no difference when using JPA or Hibernate as long as you use Date as java type. Exemple:

import java.util.Date;

@Entity
public class Foo {

   @Id
   @GeneratedValue
   long id;

   Date Bar;

   public void  setBar(Date bar) {
      this.bar = bar;
   }

   public Date getBar() {
      return bar;
   }
}

...

// An Entity
Foo foo;

// set minimum date
foo.setBar(new Date(0));

// set maximum date
foo.setBar(new Date(Long.MAX_VALUE));

edited May 23 '17 at 12:04

Community

1
1

answered Oct 08 '12 at 06:06

Thobias Bergqvist

111
1
8

Really? What do you get if you try to save it? – Thobias Bergqvist Oct 08 '12 at 12:26
Simply because if it is for example mapped to a timestamp defined as 8 bytes counting in microseconds it will be 1000 bigger (Date(long values) is in milliseconds). But even if we divide by 1000 it will work for example on PostreSQL but a timestamp on Oracle cannot be larger than a date (and the maximum is 9999) – Matteo Oct 08 '12 at 15:28
That is not an answer to my question. Your question was "Maximum possible date in JPA" right, so if my answer is not correct, (`new Date(Long.MAX_VALUE)` is NOT the maximum date), then I would like to know how you create a Date that is larger? – Thobias Bergqvist Oct 09 '12 at 07:09
You don't create it since the DB will not support it. Of course I can create a larger date in Java but what for if I cannot persist it? I explicitly stated JPA in the question. If you want to use JPA and persist your Date objects you have constraints (which are sadly based on the DB implementation). – Matteo Oct 09 '12 at 07:24

Maximum possible date in JPA

3 Answers3