datetime-functions

For the complete MongoDB documentation index, see www.mongodb.com/docs/llms.txt

<!-- Tab options on this page. Append to the .md URL to filter: ?tabs=<id,...> select specific tabs (e.g. ?tabs=nodejs,shell) ?allTabs=true include every tab (no param) default: one tab per tabset

Available tabs: other tabs: session, entitymanager -->

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.

Note: Time Zone

The Hibernate ORM extension resolves each datetime function in the default time zone of the JVM that runs the application. It passes that zone to MongoDB as the timezone argument of the generated operator. The same query can return different values on hosts configured with different time zones.

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](https://www.mongodb.com/docs/languages/java/mongodb-hibernate/current/interact-data/specify-a-query.md#std-label-hibernate-query-expressions section of the Specify a Query guide.

Sample Data

The examples in this guide use the Movie entity, which represents the sample_mflix.movies collection from the Atlas sample datasets. The Movie entity has the following definition:

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;
   }
}

To learn how to create a Java application that uses the MongoDB Extension for Hibernate ORM to interact with this MongoDB sample collection, see the [Get Started](https://www.mongodb.com/docs/languages/java/mongodb-hibernate/current/get-started.md#std-label-hibernate-get-started tutorial.

The examples in this guide use the Movie entity's released field, which stores an Instant value.

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:

Session

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]);
}

Output:

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
  }
}

Important: Day of Week Numbering

The day of week field returns the MongoDB $dayOfWeek value, which numbers days from 1 for Sunday through 7 for Saturday. This differs from the Java DayOfWeek enum, which numbers days from 1 for Monday through 7 for Sunday.

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:

Session

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]);
}

Output:

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

If you want to use MongoDB format specifiers instead, you can call the function as format(released, '%Y-%m-%d').

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](https://www.mongodb.com/docs/languages/java/mongodb-hibernate/current/interact-data/specify-a-query.md#std-label-hibernate-specify-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.