209 words

Group by date / time

#1
2011-07-10 14:01

Hi,

I'm trying to create reporting query that will return entities grouped by year / mont / day. Each entity has it's own field that holds java.lang.Date. There is no function like YEAR(), MONTH() or DAY() in ODB. So, how to build queries similar to something like this:

SELECT COUNT(id) 
FROM stats 
WHERE YEAR(record_date) = 2009 
GROUP BY YEAR(record_date), MONTH(record_date)

or maybe something like this:

SELECT COUNT(id) 
FROM stats 
WHERE YEAR(record_date) = 2009 
GROUP BY DATE_FORMAT(record_date, '%Y%m')

?

lwalkowski
lwalkowski's picture
Joined on 2010-08-25
User Post #40
#2
2011-07-11 09:02

Following your question, and since some JPA implementations support these expressions (as an extension to JPA) - support of date/time methods (YEAR, MONTH, DAY, HOUR, MINUTE, SECOND) was just added to ObjectDB in build 2.2.8_02.

For example, the following test:

import java.util.*;
import java.sql.*;
 
import javax.persistence.*;
 
 
public final class F187 {
 
    public static void main(String[] args) {
        EntityManagerFactory emf =
            Persistence.createEntityManagerFactory("$objectdb/db/test.odb");
        EntityManager em = emf.createEntityManager();
 
        em.getTransaction().begin();
        MyEntity e1 = new MyEntity();
        Calendar c = Calendar.getInstance();
        c.set(2099, 11, 31, 23, 59, 58);
        e1.t = new Timestamp(c.getTime().getTime());
        em.persist(e1);
        em.getTransaction().commit();
 
        Query query = em.createQuery(
            "SELECT YEAR(e.t), MONTH(e.t), DAY(e.t), " +
            "HOUR(e.t), MINUTE(e.t), SECOND(e.t) FROM MyEntity e",
            Tuple.class);
        List resultList = query.getResultList();
        System.out.println(resultList);
 
        em.close();
        emf.close();
    }
 
    @Entity
    public static final class MyEntity {
        private Timestamp t;
    }  
}

should produce this output:

[[2099, 12, 31, 11, 59, 58]]
ObjectDB Support
ObjectDB - Fast Object Database for Java (JPA/JDO)
support
support's picture
Joined on 2010-05-03
User Post #420
#3
2011-07-12 11:31

Thanks a lot! Everything works as expected :)

lwalkowski
lwalkowski's picture
Joined on 2010-08-25
User Post #41

Post Reply

Please read carefully the posting instructions - before posting to the ObjectDB website.

  • You may have to disable pop up blocking in order to use the toolbar (e.g. in Chrome).
  • Use ctrl + right click to open the browser context menu in the editing area (e.g. for using a browser spell checker).
  • To insert formatted lines (e.g. Java code, stack trace) - select a style in the toolbar and then insert the text in the new created block.
  • Avoid overflow of published source code examples by breaking long lines.
  • You may mark in paragraph code words (e.g. class names) with the code style (can be applied by ctrl + D).
  • Long stack traces (> 50 lines) and complex source examples (> 100 lines) should be posted as attachments.
Attachments:
Maximum file size: 32 MB
Cancel