1

How to get the month and year of a postgresql date field using jpa2 criteria query or jpql?

Has anybody done this?

3 Answers 3

2

Use this: FUNC('MyFunc', a, b, c)

Example: SELECT FUNC('to_char', current_date, 'month')

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

Comments

1

With JPA criteria API (tested only with Hibernate) :

// Create expressions that extract date parts:
Expression<Integer> year = cb.function("year", Integer.class, date);
Expression<Integer> month = cb.function("month", Integer.class, date);
Expression<Integer> day = cb.function("day", Integer.class, ts);

// Create expressions that extract time parts:
Expression<Integer> hour = cb.function("hour", Integer.class, time);
Expression<Integer> minute = cb.function("minute", Integer.class, time);
Expression<Integer> second = cb.function("second", Integer.class, ts); 

Source : http://www.objectdb.com/java/jpa/query/jpql/date#Date_and_Time_in_Criteria_Queries_

With jpql (tested only with Hibernate) :

YEAR(date)
MONTH(date)
DAY(date) 
HOUR(date)
MINUTE(date)
SECOND(date)

Comments

0

A few examples, how to extract the month from a date or timestamp in PostgreSQL:

select to_char(now(), 'month');
select to_char(now(), 'MM');
select extract('month' from now());
select date_part('month', now());

Just read the manual.

1 Comment

how to do criteria query JPA of JEE6

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.