aspose file tools*
The moose likes JDBC and the fly likes Group by date weekly/monthly Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Group by date weekly/monthly" Watch "Group by date weekly/monthly" New topic
Author

Group by date weekly/monthly

Nisha lakshminaraya
Ranch Hand

Joined: Feb 23, 2010
Posts: 30
Hi,

I have requirement where my PostgreSQL query will return me result set which contain between the selected time period example Suppose i select Start date as Current date and end date as 25th June 2011, the my query will return data which fall between this date.

Once i get the data i have to count number records on weekly/monthly basis. How to do this in Java? is any framework availble for it?

Thanks,
Nisha
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

It might be easier to do it in SQL actually using GROUP BY and some date/time manipulations (see PostreSQL's date/time functions). Is there a specific reason why you wouldn't want to do so?
Nisha lakshminaraya
Ranch Hand

Joined: Feb 23, 2010
Posts: 30
I tried doing with PostgreSQL date/time function, its working for weekly but not for Monthly. So want to write generic function.
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

Post what you've tried with SQL. It certainly should be doable (and probably much, much easier) in SQL.
Nisha lakshminaraya
Ranch Hand

Joined: Feb 23, 2010
Posts: 30
I tried :


SELECT count(date) As Count
FROM table

group by date_trunc('month', TIMESTAMP 'now');
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

I've moved the discussion to the JDBC forum, as it is better suited to SQL-related questions we have arrived at.

I don't know the PostgreSQL dialect (especially when dealing with dates), but it seems you were grouping by the value derived from 'now', which I suppose is current time. You need to group by value derived from the actual date column. Perhaps something like this:
Nisha lakshminaraya
Ranch Hand

Joined: Feb 23, 2010
Posts: 30
Thank you. But could you please also help me to filter out data using java as this filtering needs to be reused want to write generic code in java which only filter the data based on daily/weekly/monthly?
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

But you wanted not only to filter out some rows, but also group them and compute counts?

That's a lot of work. The scope is too broad. Consider ORM frameworks - even these provide you a query language to express this kind of requests, not an API. The query language is much more flexible.

Also, doing the work in Java generally means doing data manipulations far from the database; all of the data will have to travel over the network just to throw away part of them (maybe even most of them) and just counting the rest. If you let the database to do the work, the network will be much less loaded and the database might even be able to pull some optimizations of its own (eg. reading the data from an index instead of the table).
Nisha lakshminaraya
Ranch Hand

Joined: Feb 23, 2010
Posts: 30
can you please help me to convert same query in hibernate?
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Group by date weekly/monthly