Grouping by date()

#1

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?

#2

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
#3

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.

#4

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
#5

New build works well. Thanks.

#6

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).

 

 

#7

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

ObjectDB Support
#8

Thanks for the fix. New version works well.

Reply