Compare timestamp date in Postgresql in java

Question

I am using PostgreSQL database & JPA. Here is my user table

   CREATE TABLE users
        (
          id integer NOT NULL DEFAULT nextval('userseq'::regclass),
          firstname character varying(64) ,
          middlename character varying(64),
          lastname character varying(64),
          birthdate timestamp with time zone,
         )

    Query query = em
                    .createQuery(
    "SELECT users FROM Users users WHERE user.birthdate =:a")
                            .setParameter("a",18)
                            .setParameter("b",25);
            query.getResultList();

I want get all user whose age in between 18-25. Please complete my above JPA query

you don't need to convert existing timestamp of DB. revised the answer, please. — Zaw Than oo
– Zaw Than oo, Commented Oct 23, 2012 at 10:08

Zaw Than oo · Accepted Answer · 2012-10-23 10:05:33Z

2

Use Between operator, fix to user.birthdate to users.birthdate at first.

    Calendar cal_1 = Calendar.getInstance();
    cal_1.add(Calendar.YEAR, -18);
    Date a = cal_1.getTime();
    Calendar cal_2 = Calendar.getInstance();
    cal_2.add(Calendar.YEAR, -25);
    Date b = cal_2.getTime();

    query.setParameter("a", a);
    query.setParameter("b", b);

    SELECT users FROM Users users WHERE users.eventsDate BETWEEN :a AND :b

Example : x BETWEEN :min AND :max. Reference here

edited Oct 23, 2012 at 10:05

answered Oct 23, 2012 at 9:46

Zaw Than oo

9,94513 gold badges87 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Craig Ringer · Accepted Answer · 2012-10-23 10:05:56Z

2

JPQL is lacking in date operators. In SQL you'd write something like:

SELECT age(TIMESTAMPTZ '1999-01-01') BETWEEN INTERVAL '18' YEAR AND INTERVAL '25' YEAR;

but you can't do that in JPQL as far as I can tell. BETWEEN is supported in JPQL, so I'd convert the date parameters a and b to java.util.Date using java.util.Calendar, then use:

SELECT users FROM Users users WHERE user.birthdate BETWEEN :a AND :b

edited Oct 23, 2012 at 10:05

answered Oct 23, 2012 at 9:41

Craig Ringer

329k84 gold badges742 silver badges820 bronze badges

Collectives™ on Stack Overflow

Compare timestamp date in Postgresql in java

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related