Use Datetime Functions in Queries

Overview

In this guide, you can learn how to call the Hibernate Query Language (HQL) extract() and format() functions on a datetime field by using the MongoDB Extension for Hibernate ORM. You can use extract() to return part of a datetime value and format() to render a datetime value as a string.

The Hibernate ORM extension translates each call to a MongoDB aggregation expression in the $project stage. You can call either function on a datetime field in a SELECT clause.

Because the Hibernate ORM extension does not support function calls as operands of a computed expression, you cannot combine a datetime function with an arithmetic operator. To learn more, see the Use Computed Expressions section of the Specify a Query guide.

import com.mongodb.hibernate.annotations.ObjectIdGenerator;
import org.bson.types.ObjectId;
import java.time.Instant;
import java.util.List;
import jakarta.persistence.Embedded;
import jakarta.persistence.Entity;
import jakarta.persistence.FetchType;
import jakarta.persistence.Id;
import jakarta.persistence.OneToMany;
import jakarta.persistence.Table;
@Entity
@Table(name = "movies")
public class Movie {
   @Id
   @ObjectIdGenerator
   private ObjectId id;
   private String title;
   private String plot;
   private int year;
   private List<String> cast;
   private List<String> directors;
   private Instant released;
   @Embedded
   private Awards awards;
   @OneToMany(mappedBy = "movie", fetch = FetchType.LAZY)
   private List<Comment> comments;
   public Movie(String title, String plot, int year, List<String> cast, List<String> directors, Instant released, Awards awards) {
      this.title = title;
      this.plot = plot;
      this.year = year;
      this.cast = cast;
      this.directors = directors;
      this.released = released;
      this.awards = awards;
   }
   public Movie() {
   }
   public ObjectId getId() {
      return id;
   }
   public String getTitle() {
      return title;
   }
   public void setTitle(String title) {
      this.title = title;
   }
   public String getPlot() {
      return plot;
   }
   public void setPlot(String plot) {
      this.plot = plot;
   }
   public int getYear() {
      return year;
   }
   public void setYear(int year) {
      this.year = year;
   }
   public List<String> getCast() {
      return cast;
   }
   public void setCast(List<String> cast) {
      this.cast = cast;
   }
   public List<String> getDirectors() {
      return directors;
   }
   public void setDirectors(List<String> directors) {
      this.directors = directors;
   }
   public Awards getAwards() {
      return awards;
   }
   public void setAwards(Awards awards) {
      this.awards = awards;
   }
   public Instant getReleased() {
      return released;
   }
   public void setReleased(Instant released) {
      this.released = released;
   }
   public List<Comment> getComments() {
      return comments;
   }
   public void setComments(List<Comment> comments) {
      this.comments = comments;
   }
}

Extract a Datetime Field

You can use the extract() function to return a specific part of a datetime value, such as the year, month, or day.

The extract(field from x) function returns a single field of a datetime value. The Hibernate ORM extension supports the following values for the field:

The following example returns the title of each "Hairspray" movie in the sample_mflix.movies collection and the year in which the movie was released:

var extractResult = session.createQuery(
                "select title, extract(year from released) as releaseYear from Movie where title = :title",
                Object[].class)
        .setParameter("title", "Hairspray")
        .getResultList();
for (var row : extractResult) {
    System.out.println("Title: " + row[0] + ", Release Year: " + row[1]);
}

Title: Hairspray, Release Year: 1988
Title: Hairspray, Release Year: 2007

var extractResult = entityManager.createQuery(
                "select m.title, extract(year from m.released) as releaseYear from Movie m where m.title = :title",
                Object[].class)
        .setParameter("title", "Hairspray")
        .getResultList();
for (var row : extractResult) {
    System.out.println("Title: " + row[0] + ", Release Year: " + row[1]);
}

Title: Hairspray, Release Year: 1988
Title: Hairspray, Release Year: 2007

The Hibernate ORM extension translates the preceding query to the following $project stage, in which America/New_York is the default time zone of the JVM:

{
  "$project": {
    "title": true,
    "releaseYear": {
      "$year": {
        "date": "$released",
        "timezone": { "$literal": "America/New_York" }
      }
    },
    "_id": 0
  }
}

The Hibernate ORM extension does not support the date, time, offset, timezone_hour, and timezone_minute fields. A query that extracts an unsupported field throws a FeatureNotSupportedException.

Format a Datetime as a String

The format(x as pattern) function renders a datetime value as a string. The Hibernate ORM extension translates the call to the MongoDB $dateToString operator and maps each pattern code to the equivalent MongoDB format specifier. You can also call the function as format(x, pattern) and pass MongoDB format specifiers directly.

The Hibernate ORM extension supports the following pattern codes:

MongoDB returns month and day names in the US locale. Characters that you enclose in single quotation marks pass through to the output without being interpreted as pattern codes.

The following example returns the title of each "Hairspray" movie and its release date as a yyyy-MM-dd string:

var formatResult = session.createQuery(
                "select title, format(released as 'yyyy-MM-dd') as releaseDate from Movie where title = :title",
                Object[].class)
        .setParameter("title", "Hairspray")
        .getResultList();
for (var row : formatResult) {
    System.out.println("Title: " + row[0] + ", Release Date: " + row[1]);
}

Title: Hairspray, Release Date: 1988-02-25
Title: Hairspray, Release Date: 2007-07-19

var formatResult = entityManager.createQuery(
                "select m.title, format(m.released as 'yyyy-MM-dd') as releaseDate from Movie m where m.title = :title",
                Object[].class)
        .setParameter("title", "Hairspray")
        .getResultList();
for (var row : formatResult) {
    System.out.println("Title: " + row[0] + ", Release Date: " + row[1]);
}

Title: Hairspray, Release Date: 1988-02-25
Title: Hairspray, Release Date: 2007-07-19

The Hibernate ORM extension translates the preceding query to the following $project stage:

{
  "$project": {
    "title": true,
    "releaseDate": {
      "$dateToString": {
        "date": "$released",
        "format": { "$literal": "%Y-%m-%d" },
        "timezone": { "$literal": "America/New_York" }
      }
    },
    "_id": 0
  }
}

A query that uses a pattern code outside the preceding table throws a FeatureNotSupportedException. This includes single-character and variable-width codes such as y, M, d, H, h, m, s, and a, because MongoDB has no equivalent specifier for them. A repeated time zone code of more than three characters, such as ZZZZZZZ, is ambiguous and also throws an exception.

Additional Information

To learn more about creating query filters and using operators in your query statements, see the Specify a Query guide.

To learn more about using HQL and JPQL to run queries, see A Guide to Hibernate Query Language in the Hibernate ORM documentation.