279 words

Grouping by date()

#1
2011-12-20 15:05

Hi,

For some time now we can use date and time function in queries to extract part of date time (from documentation):

YEAR({d '2011-12-31'}) is evaluated to 2011.
MONTH({d '2011-12-31'}) is evaluated to 12.
DAY({d '2011-12-31'}) is evaluated to 31.
HOUR({t '23:59:00'}) is evaluated to 23.
MINUTE({t '23:59:00'}) is evaluated to 59.
SECOND({t '23:59:00'}) is evaluated to 0.

Quite often I have to group by date(). For few queries I was concatenating 'date' from year()-month()-day() calls, but this method is quite error prone and final queries are terrible to read. My question is - is it possible to add one more method to above list - date() - that will return only date from java.util.Date field?

lwalkowski
lwalkowski's picture
Joined on 2010-08-25
User Post #60
#2
2011-12-20 23:45

Version 2.3.5 supports 2 new date functions that take a Date instance in queries:

  • DATE(ts) - Extracts the date part (year, month, day) as a Date instance.
  • TIME(ts) - Extracts the time part (hour, minute, second, millisecond) as a Date instance.

Please try the new version.

ObjectDB Support
ObjectDB - Fast Object Database for Java (JPA/JDO)
support
support's picture
Joined on 2010-05-03
User Post #815
#3
2011-12-21 13:41

Thanks for quick implementation. There is only one small problem with new version. When you group by date() there are two days per day. I mean group by divides each day for two parts. You can see an example in attached screenshot.

lwalkowski
lwalkowski's picture
Joined on 2010-08-25
User Post #61
#4
2011-12-21 23:17

You are right, Calendar.HOUR was used in resetting the time part instead of Calendar.HOUR_OF_DAY , so AM and PM timestamps got different date values.

Please try build 2.3.5_01.

ObjectDB Support
ObjectDB - Fast Object Database for Java (JPA/JDO)
support
support's picture
Joined on 2010-05-03
User Post #816
#5
2011-12-22 15:01

New build works well. Thanks.

lwalkowski
lwalkowski's picture
Joined on 2010-08-25
User Post #62
#6
2012-01-02 14:13

I think I've found a one more place with this issue. Query:

SELECT hour(ov.created), count(ov) FROM ObjectView ov GROUP BY hour(ov.created) ORDER BY hour(ov.created) ASC

where ov.created is normal Date() field return only hours from 0 to 11 (check attached screenshot).

 

 

lwalkowski
lwalkowski's picture
Joined on 2010-08-25
User Post #65
#7
2012-01-02 20:50

Please try the new build (2.3.5_06) in which HOUR returns a value in the 0-23 range.

ObjectDB Support
ObjectDB - Fast Object Database for Java (JPA/JDO)
support
support's picture
Joined on 2010-05-03
User Post #837
#8
2012-01-03 10:23

Thanks for the fix. New version works well.

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

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