Date and Time in JPQL and Criteria Queries

Date and time expressions can appear in JPQL queries in the following ways:

• As date and time literals--for example, {d '2011-12-31'} and {t '23:59:59'}.
• As parameters--when date and time values are passed as arguments.
• As path expressions--to navigate to persistent date and time fields.
• As the results of predefined JPQL functions that return the current date and time.

Current date and time

JPA defines special JPQL expressions that return the date and time from the database server when the query is executed:

• CURRENT_DATE: Returns the current date as a java.sql.Date instance.
• CURRENT_TIME: Returns the current time as a java.sql.Time instance.
• CURRENT_TIMESTAMP: Returns the current timestamp (date and time) as a java.sql.Timestamp instance.

Extracting date parts

JPA does not define standard methods for extracting date and time parts, but some JPA implementations, including ObjectDB, support these functions as extensions. ObjectDB supports six functions for extracting the YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND parts from a date or time value.

For example:

• YEAR({d '2011-12-31'}) returns 2011.
• MONTH({d '2011-12-31'}) returns 12.
• DAY({d '2011-12-31'}) returns 31.
• HOUR({t '23:59:00'}) returns 23.
• MINUTE({t '23:59:00'}) returns 59.
• SECOND({t '23:59:00'}) returns 0.

Date and time in criteria queries

The CriteriaBuilderjakarta.persistence.criteria.CriteriaBuilderUsed to construct criteria queries, compound selections, expressions, predicates, orderings. interface provides three factory methods for building date and time expressions that represent the current date or time:

  // Create current date expression:
  Expressionjakarta.persistence.criteria.ExpressionType for query expressions.<javax.sql.Date> date = cb.currentDatejakarta.persistence.criteria.CriteriaBuilder.currentDate()Create expression to return current date.(); // date only
  // Create current time expression:
  Expressionjakarta.persistence.criteria.ExpressionType for query expressions.<javax.sql.Time> time = cb.currentTimejakarta.persistence.criteria.CriteriaBuilder.currentTime()Create expression to return current time.(); // time only
  // Create current date & time expression:
  Expressionjakarta.persistence.criteria.ExpressionType for query expressions.<javax.sql.Timestamp> ts = cb.currentTimestampjakarta.persistence.criteria.CriteriaBuilder.currentTimestamp()Create expression to return current timestamp.(); // both

Unlike constant date literals, which are built once on the client side, these expressions are re-evaluated on the server each time the query is executed to reflect the current date and time.

Functions for extracting date and time parts are also available in criteria queries by using the generic CriteriaBuilderjakarta.persistence.criteria.CriteriaBuilderUsed to construct criteria queries, compound selections, expressions, predicates, orderings.'s functionjakarta.persistence.criteria.CriteriaBuilder.function(String,Class,Expression...)Create an expression for the execution of a database function. method, as follows:

  // Create expressions that extract date parts:
  Expressionjakarta.persistence.criteria.ExpressionType for query expressions.<Integer> year = cb.functionjakarta.persistence.criteria.CriteriaBuilder.function(String,Class,Expression...)Create an expression for the execution of a database function.("year", Integer.class, date);
  Expressionjakarta.persistence.criteria.ExpressionType for query expressions.<Integer> month = cb.functionjakarta.persistence.criteria.CriteriaBuilder.function(String,Class,Expression...)Create an expression for the execution of a database function.("month", Integer.class, date);
  Expressionjakarta.persistence.criteria.ExpressionType for query expressions.<Integer> day = cb.functionjakarta.persistence.criteria.CriteriaBuilder.function(String,Class,Expression...)Create an expression for the execution of a database function.("day", Integer.class, ts);

  // Create expressions that extract time parts:
  Expressionjakarta.persistence.criteria.ExpressionType for query expressions.<Integer> hour = cb.functionjakarta.persistence.criteria.CriteriaBuilder.function(String,Class,Expression...)Create an expression for the execution of a database function.("hour", Integer.class, time);
  Expressionjakarta.persistence.criteria.ExpressionType for query expressions.<Integer> minute = cb.functionjakarta.persistence.criteria.CriteriaBuilder.function(String,Class,Expression...)Create an expression for the execution of a database function.("minute", Integer.class, time);
  Expressionjakarta.persistence.criteria.ExpressionType for query expressions.<Integer> second = cb.functionjakarta.persistence.criteria.CriteriaBuilder.function(String,Class,Expression...)Create an expression for the execution of a database function.("second", Integer.class, ts);