Date and Time in JPQL and Criteria Queries

Date and time expressions may appear in JPQL queries:

as date and time literals - e.g. {d '2011-12-31'}, {t '23:59:59'}.
as parameters - when date and time values are assigned as arguments.
as path expressions - in navigation to persistent date and time fields.
as results of predefined JPQL current date and time functions.

This page covers the following topics:

Current Date and Time

JPA defines special JPQL expressions that are evaluated to the date and time on the database server when the query is executed:

CURRENT_DATE - is evaluated to the current date (a java.sql.Date instance).
CURRENT_TIME - is evaluated to the current time (a java.sql.Time instance).
CURRENT_TIMESTAMP - is evaluated to the current timestamp, i.e. date and time
(a java.sql.Timestamp instance).

Extracting Date Parts

JPA doesn't define standard methods for extracting date and time parts but some JPA implementations, as well as ObjectDB, support such functions as an extension. ObjectDB supports 6 functions for extracting the YEAR, MONTH, DAY, HOUR, MINUTE and SECOND.

For example:

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

Date and Time in Criteria Queries

The CriteriaBuilderjavax.persistence.criteria.CriteriaBuilder (JPA Interface)Used 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 and/or time:

  // Create current date expression:
  Expressionjavax.persistence.criteria.Expression (JPA Interface)Type for query expressions.<javax.sql.Date> date = cb.currentDate(); // date only

  // Create current time expression:
  Expressionjavax.persistence.criteria.Expression (JPA Interface)Type for query expressions.<javax.sql.Time> time = cb.currentTime(); // time only

  // Create current date & time expression:
  Expressionjavax.persistence.criteria.Expression (JPA Interface)Type for query expressions.<javax.sql.Timestamp> ts = cb.currentTimestamp(); // both

Unlike constant date literals which are built once on the client side, the current date and time expressions are re-evaluated on the server on every query execution to reflect the date and time when the query is run.

Functions for extracting date and time parts are also available in criteria queries by using the generic CriteriaBuilderjavax.persistence.criteria.CriteriaBuilder (JPA Interface)Used to construct criteria queries, compound selections, expressions, predicates, orderings.'s function method, as follow:

  // Create expressions that extract date parts:
  Expressionjavax.persistence.criteria.Expression (JPA Interface)Type for query expressions.<Integer> year = cb.function("year", Integer.class, date);
  Expressionjavax.persistence.criteria.Expression (JPA Interface)Type for query expressions.<Integer> month = cb.function("month", Integer.class, date);
  Expressionjavax.persistence.criteria.Expression (JPA Interface)Type for query expressions.<Integer> day = cb.function("day", Integer.class, ts);

  // Create expressions that extract time parts:
  Expressionjavax.persistence.criteria.Expression (JPA Interface)Type for query expressions.<Integer> hour = cb.function("hour", Integer.class, time);
  Expressionjavax.persistence.criteria.Expression (JPA Interface)Type for query expressions.<Integer> minute =
    cb.function("minute", Integer.class, time);
  Expressionjavax.persistence.criteria.Expression (JPA Interface)Type for query expressions.<Integer> second = cb.function("second", Integer.class, ts);

< Strings in JPQL ^ Query Expressions Collections in JPQL >