EJB-QL Convert timestamp to date

[mikezzz]

Hi,

I have a enitity with a timestamp field, but would like to query it with a date value. Is there a portable way to truncate a timestamp to a date within an ejb-ql query?

E.g. something that would be functionally equivalent to the following Postgres query.

SELECT * FROM foo WHERE date_trunc('day', foo.timestamp) = date '2007-03-01'

Regards,
Michael Barker.

[jagthedrummer]

Hi Michael, I've asked about this same type of thing before and have never gotten a single response of any kind. I'm guessing that the deafening silence means this type of thing is just not possible.

I'm trying to do something similar to this:

select sum(total),date_part('year',placed) as year from orders group by year;

I'm trying to get a total for all orders placed broken up by year.

I've haven't been able to find any documentation about any sort of functionality like this with regards to either ejb3 or hibernate. If anyone knows anything about this I'd sure love to hear about it.

Thanks,
Jeremy

[fhh]

Have you tried 'cast(foo.timestamp AS date)' and 'extract(year from foo.timestamp)'? For the later one there are also abbraviations like day(xxx) etc. but these are Hibernate specific.

I have never tried these myself but they are definitly in the manual.

Regards

Felix

[kpiis]

"mikezzz" wrote:
Hi,

I have a enitity with a timestamp field, but would like to query it with a date value. Is there a portable way to truncate a timestamp to a date within an ejb-ql query?

E.g. something that would be functionally equivalent to the following Postgres query.

SELECT * FROM foo WHERE date_trunc('day', foo.timestamp) = date '2007-03-01'

Regards,
Michael Barker.

The protable way is using Query.setParameter methods:
The quote from Monson-Haefel book:

If you need to pass java.util.Date or java.util.Calendar parameters into a query, you need to use special setParameter methods:

package javax.persistence;

public enum TemporalType {
DATE, //java.sql.Date
TIME, //java.sql.Time
TIMESTAMP //java.sql.Timestamp
}

public interface Query
{
Query setParameter(String name, java.util.Date value, TemporalType temporalType);
Query setParameter(String name, Calendar value, TemporalType temporalType);

Query setParameter(int position, Date value, TemporalType temporalType);
Query setParameter(int position, Calendar value, TemporalType temporalType);
}

A Date or Calendar object can represent a real date, a time of day, or a numeric timestamp. Because these object types can represent different things at the same time, you need to tell your Query object how it should use these parameters. The javax.persistence.TemporalType passed in as a parameter to the setParameter( ) method tells the Query interface what database type to use when converting the java.util.Date or java.util.Calendar parameter to a native SQL type.


It's working :) - I am using it.

[fhh]

But this only works if
a.) the truncated value is a parameter in the query
b.) you are actually truncing to the day.

It will not work for something like this:

SELECT c, d FROM Cat c, Dog d
WHERE extract(month from c.birthday) = extract(month from d.birthday)# AND extract(year from c.birthday) = extract(year from d.birthday)

which will return all cat/dog tuples which were born in the same month.

Regards

Felix

P.S.: All untested, of course.