Search code examples
sqloraclejpajpql

Hour of a Date using JPA and JPQL (in a NamedQuery)

In my table "creneau" I have a column "dateHeure". "dateHeure" is a date. I want to count the number of lines having "dateHeure" with an hour equal to a number I choose. I want to do the comparison directly in a NamedQuery.

I tried the code below but this one does not work :

@NamedQuery(
    name = "compterCreneauxHeure", 
    query = "SELECT count(cre.creneauId) 
             FROM CreneauEntity cre 
             WHERE extract(HOUR cre.dateHeure) = :heure")

However, the one below works very well :

@NamedQuery(
    name = "compterCreneauxHeure",
    query = "SELECT count(cre.creneauId) 
             FROM CreneauEntity cre 
             WHERE extract(DAY cre.dateHeure) = :heure")

(:heure is my number)

I do not understand where is my error.

Solution

  • JPQL has no such function "extract", so you are seemingly relaying on your JPA provider supporting that. JPQL != SQL. Read a JPA reference document, or the JPA spec.

    Most JPA providers would however support 

    SELECT count(cre.creneauId) FROM CreneauEntity cre WHERE HOUR(cre.dateHeure) = :heure

    even though it is strictly speaking not part of the JPA spec (yet). Similarly most JPA providers would support a function DAY(...). See https://github.com/javaee/jpa-spec/issues/102