HQL functions - more of them

In the previous post we saw some common HQL functions. I decided to explore them some more.
JPA provides support for performing arithmetic operations in the query. Consider the below code:

final Session session = sessionFactory.openSession();
//abs()
Query q = session.createQuery("from Entity e where e.id = abs(-2)");
Entity entity = (Entity) q.uniqueResult();
System.out.println("Entity is  " + entity);
//sqrt()
q = session.createQuery("from Entity e where e.id = sqrt(9)");
entity = (Entity) q.uniqueResult();
System.out.println("Entity is  " + entity);
//mod()
q = session.createQuery("from Entity e where e.id = mod(16,14)");
entity = (Entity) q.uniqueResult();
System.out.println("Entity is  " + entity);

All the above queries include some arithmetic based functions. The generated SQL uses the equivalent functions provided by the underlying database, in my case MySQL.
The logs indicate the same:

3421 [main] DEBUG org.hibernate.SQL  - 
    /* 
from
    Entity e 
where
    e.id = abs(-2) */ 

    
    select
        entity0_.ID as ID0_,
        entity0_.NAME as NAME0_,
        entity0_.DATE as DATE0_,
        entity0_.MASTER_ID as MASTER4_0_ 
    from
        ENTITY entity0_ 
    where
        entity0_.ID=abs(-2)
Entity is  [Entity] : ( id 2 , data : entity2 , master.Id : 1 , date : null )]
...
3609 [main] DEBUG org.hibernate.SQL  - 
    /* 
from
    Entity e 
where
    e.id = sqrt(9) */ 

    select
        entity0_.ID as ID0_,
        entity0_.NAME as NAME0_,
        entity0_.DATE as DATE0_,
        entity0_.MASTER_ID as MASTER4_0_ 
    from
        ENTITY entity0_ 
    where
        entity0_.ID=sqrt(9)
Entity is  [Entity] : ( id 3 , data : entity3 , master.Id : 1 , date : null )]
...
3656 [main] DEBUG org.hibernate.SQL  - 
    /* 
from
    Entity e 
where
    e.id = mod(16,14) */ 

    select
        entity0_.ID as ID0_,
        entity0_.NAME as NAME0_,
        entity0_.DATE as DATE0_,
        entity0_.MASTER_ID as MASTER4_0_ 
    from
        ENTITY entity0_ 
    where
        entity0_.ID=mod(16, 14)
Entity is  [Entity] : ( id 2 , data : entity2 , master.Id : 1 , date : null )]

locate function():

final Session session = sessionFactory.openSession();
//searches for occurrence of 2 at 7 location. e.g. entity2
Query q = session.createQuery("from Entity e where locate('2', e.name, 1) = 7");
Entity entity = (Entity) q.uniqueResult();
System.out.println("Entity is  " + entity);

The method is string operation related. It returns the position of the first occurrence of a string('2') within a string( the name column). A third optional argument may be used to specify from which position of the search string searching will start. If this position is not mentioned, searching starts from the beginning. 
The logs indicate the query

2797 [main] DEBUG org.hibernate.SQL  - 
    /* 
from
    Entity e 
where
    locate('2', e.name, 1) = 7 */ 
    
    select
        entity0_.ID as ID0_,
        entity0_.NAME as NAME0_,
        entity0_.DATE as DATE0_,
        entity0_.MASTER_ID as MASTER4_0_ 
    from
        ENTITY entity0_ 
    where
        locate('2', entity0_.NAME, 1)=7
Entity is  [Entity] : ( id 2 , data : entity2 , master.Id : 1 , date : null )]

Hibernate additionally provides functions that are not a part of JPA(JQL) and can be used in our HQL queries. I tested some of them.
BIT_LENGTH()

final Session session = sessionFactory.openSession();
Query q = session.createQuery("select bit_length(e.name) from Entity e where e.id =  2");
Long bitLength = (Long) q.uniqueResult();
System.out.println("Number of bits is  " + bitLength);

The function returns the number of bits. The logs indicate the generated query.

2797 [main] DEBUG org.hibernate.SQL  - 
    /* select
        bit_length(e.name) 
    from
        Entity e 
    where
        e.id =  2 */ 

        select
            bit_length(entity0_.NAME) as col_0_0_ 
        from
            ENTITY entity0_ 
        where
            entity0_.ID=2
Number of bits is  56

HQL also includes data-time related functions. I created a mash-up of all such functions in the below code:

public static void testHqlDbTemporalFn() {
    final Session session = sessionFactory.openSession();
    System.out.println("The current time is " + new Date());
    Query q1 = session.createQuery("from Entity as e where e.date > current_date()");//only date
    Entity entity = (Entity) q1.list().get(0);
    System.out.println("current_date Entity0 :  " + entity);
    Query q2 = session.createQuery("from Entity as e where e.date > current_time()");//gets only time
    entity = (Entity) q2.list().get(0);
    System.out.println("current_time Entity0 :  " + entity);        
    Query q3 = session.createQuery("from Entity as e where e.date < current_timestamp()");//date and time
    entity = (Entity) q3.list().get(0);
    System.out.println("current_timestamp Entity0 :  " + entity);
    Query q4 = session.createQuery("select SECOND(e.date), " +
            "MINUTE(e.date), HOUR(e.date), DAY(e.date), MONTH(e.date), YEAR(e.date) " +
            "from Entity e where e.id = 3");
    
    List<Object[]> result = q4.list();
    for (Object[] rec : result) {
        System.out.println("SECOND(e.date) " + rec[0]);
        System.out.println("MINUTE(e.date) " + rec[1]); 
        System.out.println("HOUR(e.date)   " + rec[2]); 
        System.out.println("DAY(e.date)    " + rec[3]);
        System.out.println("MONTH(e.date)  " + rec[4]);
        System.out.println("YEAR(e.date)   " + rec[5]);
    }
}

The output is as below:

The current time is Sun Jul 01 17:45:39 IST 2012
Hibernate: 
    /* 
from
    Entity as e 
where
    e.date > current_date() */ 
    select
        entity0_.ID as ID0_,
        entity0_.NAME as NAME0_,
        entity0_.DATE as DATE0_,
        entity0_.MASTER_ID as MASTER4_0_ 
    from
        ENTITY entity0_ 
    where
        entity0_.DATE>current_date
current_date Entity0 :  [Entity] : ( id 2 , data : entity2 , master.Id : 1 , dat
e : 2012-10-10 12:20:20.0 )]
Hibernate: 
    /* 
from
    Entity as e 
where
    e.date > current_time() */ 
    select
        entity0_.ID as ID0_,
        entity0_.NAME as NAME0_,
        entity0_.DATE as DATE0_,
        entity0_.MASTER_ID as MASTER4_0_ 
    from
        ENTITY entity0_ 
    where
        entity0_.DATE>current_time
current_time Entity0 :  [Entity] : ( id 1 , data : newOne , master.Id : 1 , date
 : 2010-07-04 16:16:43.0 )]
Hibernate: 
    /* 
from
    Entity as e 
where
    e.date < current_timestamp() */
    select
        entity0_.ID as ID0_,
        entity0_.NAME as NAME0_,
        entity0_.DATE as DATE0_,
        entity0_.MASTER_ID as MASTER4_0_ 
    from
        ENTITY entity0_ 
    where
        entity0_.DATE<current_timestamp
current_timestamp Entity0 :  [Entity] : ( id 1 , data : newOne , master.Id : 1 ,
 date : 2010-07-04 16:16:43.0 )]
Hibernate: 
    /* select
        SECOND(e.date),
        MINUTE(e.date),
        HOUR(e.date),
        DAY(e.date),
        MONTH(e.date),
        YEAR(e.date) 
    from
        Entity e 
    where
        e.id = 3 */ 
        select
            second(entity0_.DATE) as col_0_0_,
            minute(entity0_.DATE) as col_1_0_,
            hour(entity0_.DATE) as col_2_0_,
            day(entity0_.DATE) as col_3_0_,
            month(entity0_.DATE) as col_4_0_,
            year(entity0_.DATE) as col_5_0_ 
        from
            ENTITY entity0_ 
        where
            entity0_.ID=3
SECOND(e.date) 20
MINUTE(e.date) 20
HOUR(e.date)   15
DAY(e.date)    10
MONTH(e.date)  10
YEAR(e.date)   2011

As seen these functions can be used in the where clause and the select clause.