Range COUNT query Based on DATE from epoch timestamp in Hibernate

Question

i have a table in postgres which is indexed by epoch timestamp....now i want to query this table to get count of no of events between particular dates... i have made a query for it in postgres but i am not sure how can i achieve this in hibernate without writing native postgres query in hibernate....

my table structure is

event
-----------------
id,      created,     updated,    event_type,     source_machine         timestamp
1       07.05.2011                  event1                machine1        938970822
2       07.05.2011                  event2                machine2       1332949476
3       07.05.2011             NullPointerException       machine2        648034359
4       06.05.2011                  event2                machine2       1173340611
5       06.05.2011                  event1                machine1       1391797768
6       05.05.2011                  event2                machine1       849970844
7       04.05.2011                  event1                machine2        
*Currently, table has 10k rows.

FYI: i can not use CREATED column for this i have to use timestamp only... so far query i have written in postgres is...

select event_type,to_char(to_timestamp(timestamp),'YYYY-MM-DD') AS date_range,count(*) from event where to_char(to_timestamp(timestamp),'YYYY-MM-DD') between to_char(to_timestamp(549788400),'YYYY-MM-DD') and to_char(to_timestamp(1403247600),'YYYY-MM-DD') and event.machine = 'machine1' group by event_type,to_char(to_timestamp(timestamp),'YYYY-MM-DD');

and i have to use "to_char" if i dont use it for eg. The "2010-03-31 23:59:59." border condition is potentially dangerous: if there was a transaction in the system between 23:59:59 and 00:00:00 i would miss it. any help would be appreciated.

Answer 1

i made a query which supports hibernate.here is my solution if someone is looking for answer for a similar question. above query in HQL.

SELECT new map(E.event_type as name,to_char(to_timestamp(E.timestamp),'MM-DD-YYYY') AS key,count(*) as value) from Event E WHERE E.timestamp BETWEEN " + startTime + " and " + endTime + " AND E.platform = '"+platform+"' AND E.event_type = '"+eventName+"' GROUP BY E.event_type,to_char(to_timestamp(E.timestamp),'MM-DD-YYYY') ORDER BY to_char(to_timestamp(E.timestamp),'MM-DD-YYYY')";