356 words

Date and Time in JPQL and Criteria Queries

  • You must login to access this part of the site.

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:

Unlike constant date literals which are built once on the client side, the current date and time expressions are reevaluated 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);

This documentation explains how to use JPA in the context of the ObjectDB Object Database but mostly relevant
also for ORM JPA implementations, such as Hibernate (and HQL), EclipseLink, TopLink, OpenJPA and DataNucleus.
ObjectDB is not an ORM JPA implementation but an Object Database (ODBMS) for Java with built in JPA 2 support.