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.

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.CriteriaBuilderJPA interfaceUsed to construct criteria queries, compound selections, expressions, predicates, orderings.See JavaDoc Reference Page... 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.ExpressionJPA interfaceType for query expressions.See JavaDoc Reference Page...<javax.sql.Date> date = cb.currentDatecurrentDate()CriteriaBuilder's methodCreate expression to return current date.See JavaDoc Reference Page...(); // date only

  // Create current time expression:
  Expressionjavax.persistence.criteria.ExpressionJPA interfaceType for query expressions.See JavaDoc Reference Page...<javax.sql.Time> time = cb.currentTimecurrentTime()CriteriaBuilder's methodCreate expression to return current time.See JavaDoc Reference Page...(); // time only

  // Create current date & time expression:
  Expressionjavax.persistence.criteria.ExpressionJPA interfaceType for query expressions.See JavaDoc Reference Page...<javax.sql.Timestamp> ts = cb.currentTimestampcurrentTimestamp()CriteriaBuilder's methodCreate expression to return current timestamp.See JavaDoc Reference Page...(); // 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.CriteriaBuilderJPA interfaceUsed to construct criteria queries, compound selections, expressions, predicates, orderings.See JavaDoc Reference Page...'s functionfunction(name, type, args)CriteriaBuilder's methodCreate an expression for the execution of a database function.See JavaDoc Reference Page... method, as follow:

  // Create expressions that extract date parts:
  Expressionjavax.persistence.criteria.ExpressionJPA interfaceType for query expressions.See JavaDoc Reference Page...<Integer> year = cb.functionfunction(name, type, args)CriteriaBuilder's methodCreate an expression for the execution of a database
 function.See JavaDoc Reference Page...("year", Integer.class, date);
  Expressionjavax.persistence.criteria.ExpressionJPA interfaceType for query expressions.See JavaDoc Reference Page...<Integer> month = cb.functionfunction(name, type, args)CriteriaBuilder's methodCreate an expression for the execution of a database
 function.See JavaDoc Reference Page...("month", Integer.class, date);
  Expressionjavax.persistence.criteria.ExpressionJPA interfaceType for query expressions.See JavaDoc Reference Page...<Integer> day = cb.functionfunction(name, type, args)CriteriaBuilder's methodCreate an expression for the execution of a database
 function.See JavaDoc Reference Page...("day", Integer.class, ts);

  // Create expressions that extract time parts:
  Expressionjavax.persistence.criteria.ExpressionJPA interfaceType for query expressions.See JavaDoc Reference Page...<Integer> hour = cb.functionfunction(name, type, args)CriteriaBuilder's methodCreate an expression for the execution of a database
 function.See JavaDoc Reference Page...("hour", Integer.class, time);
  Expressionjavax.persistence.criteria.ExpressionJPA interfaceType for query expressions.See JavaDoc Reference Page...<Integer> minute =
    cb.functionfunction(name, type, args)CriteriaBuilder's methodCreate an expression for the execution of a database
 function.See JavaDoc Reference Page...("minute", Integer.class, time);
  Expressionjavax.persistence.criteria.ExpressionJPA interfaceType for query expressions.See JavaDoc Reference Page...<Integer> second = cb.functionfunction(name, type, args)CriteriaBuilder's methodCreate an expression for the execution of a database
 function.See JavaDoc Reference Page...("second", Integer.class, ts);