Grouping by date()

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?

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.

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.

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.

New build works well. Thanks.

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

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

Thanks for the fix. New version works well.