Close

JPQL Datetime Functions

[Updated: Jun 15, 2018, Created: Jun 15, 2018]

JPQL supports following datetime expressions:

  • CURRENT_DATE

    Returns current date as java.sql.Date.

  • CURRENT_TIME

    Returns current time as java.sql.Time.

  • CURRENT_TIMESTAMP

    Returns current datetime as java.sql.Timestamp.

Example

Entity

@Entity
public class ScheduledEvent {
  @Id
  @GeneratedValue
  private long id;
  private String eventName;
  private Date date;
  private Time start;
  private Time end;
  private Timestamp eventCreated;
    .............
}

Using datetime functions

public class ExampleMain {
  private static EntityManagerFactory entityManagerFactory =
          Persistence.createEntityManagerFactory("example-unit");

  public static void main(String[] args) {
      try {
          persistEvent();
          findCurrentEvent();
          findTodayEvent();
          findEventCreatedNow();
      } finally {
          entityManagerFactory.close();
      }
  }

  private static void persistEvent() {
      ScheduledEvent e1 = ScheduledEvent.create("Test event",
              LocalDate.of(2018, 6, 1),
              LocalTime.of(9, 30, 45),
              LocalTime.of(11, 45, 30),
              LocalDateTime.now().minusDays(10));

      ScheduledEvent e2 = ScheduledEvent.create("Test event2",
              LocalDate.now(),
              LocalTime.of(9, 30, 45),
              LocalTime.of(20, 45, 30),
              LocalDateTime.now().minusMonths(1));

      ScheduledEvent e3 = ScheduledEvent.create("Test event3",
              LocalDate.now(),
              LocalTime.of(18, 0, 0),
              LocalTime.of(20, 0, 0),
              LocalDateTime.now());

      EntityManager em = entityManagerFactory.createEntityManager();
      em.getTransaction().begin();
      em.persist(e1);
      em.persist(e2);
      em.persist(e3);
      em.getTransaction().commit();

      System.out.println("-- all persisted entities --");
      em.createQuery("SELECT e FROM ScheduledEvent e")
        .getResultList()
        .forEach(System.out::println);
      em.close();
  }

  private static void findCurrentEvent() {
      System.out.println("-- current ongoing event  --");
      EntityManager em = entityManagerFactory.createEntityManager();
      List list = em
              .createQuery("SELECT e FROM ScheduledEvent e WHERE CURRENT_DATE = e.date AND "
                      + "CURRENT_TIME BETWEEN e.start AND e.end")
              .getResultList();
      list.forEach(System.out::println);
      em.close();
  }

  private static void findTodayEvent() {
      System.out.println("-- today event  --");
      EntityManager em = entityManagerFactory.createEntityManager();
      List list = em
              .createQuery("SELECT e FROM ScheduledEvent e WHERE  CURRENT_DATE = e.date")
              .getResultList();
      list.forEach(System.out::println);
      em.close();
  }

  private static void findEventCreatedNow() {
      System.out.println("-- event created within 10 sec --");
      EntityManager em = entityManagerFactory.createEntityManager();
      List list = em
              .createQuery("SELECT e FROM ScheduledEvent e WHERE e.eventCreated BETWEEN :timestampStart "
                      + "AND CURRENT_TIMESTAMP")
              .setParameter("timestampStart", Timestamp.valueOf(LocalDateTime.now().minusSeconds(10)))
              .getResultList();
      list.forEach(System.out::println);
      em.close();
  }
}
-- all persisted entities --
ScheduledEvent{id=1, eventName='Test event', date=2018-06-01, start=09:30:45, end=11:45:30, eventCreated=2018-06-05 16:12:02.369}
ScheduledEvent{id=2, eventName='Test event2', date=2018-06-15, start=09:30:45, end=20:45:30, eventCreated=2018-05-15 16:12:02.371}
ScheduledEvent{id=3, eventName='Test event3', date=2018-06-15, start=18:00:00, end=20:00:00, eventCreated=2018-06-15 16:12:02.371}
-- current ongoing event --
ScheduledEvent{id=2, eventName='Test event2', date=2018-06-15, start=09:30:45, end=20:45:30, eventCreated=2018-05-15 16:12:02.371}
-- today event --
ScheduledEvent{id=2, eventName='Test event2', date=2018-06-15, start=09:30:45, end=20:45:30, eventCreated=2018-05-15 16:12:02.371}
ScheduledEvent{id=3, eventName='Test event3', date=2018-06-15, start=18:00:00, end=20:00:00, eventCreated=2018-06-15 16:12:02.371}
-- event created within 10 sec --
ScheduledEvent{id=3, eventName='Test event3', date=2018-06-15, start=18:00:00, end=20:00:00, eventCreated=2018-06-15 16:12:02.371}

Example Project

Dependencies and Technologies Used:

  • h2 1.4.197: H2 Database Engine.
  • hibernate-core 5.2.13.Final: The core O/RM functionality as provided by Hibernate.
    Implements javax.persistence:javax.persistence-api version 2.1
  • JDK 1.8
  • Maven 3.3.9

JPQL Datetime Functions Select All Download
  • jpql-date-time-functions
    • src
      • main
        • java
          • com
            • logicbig
              • example
                • ExampleMain.java
          • resources
            • META-INF

    See Also