Win a copy of Mesos in Action this week in the Cloud/Virtualizaton forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Group by date weekly/monthly

 
Nisha lakshminaraya
Ranch Hand
Posts: 33
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 33
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Post what you've tried with SQL. It certainly should be doable (and probably much, much easier) in SQL.
 
Nisha lakshminaraya
Ranch Hand
Posts: 33
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I tried :


SELECT count(date) As Count
FROM table

group by date_trunc('month', TIMESTAMP 'now');
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 33
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 33
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
can you please help me to convert same query in hibernate?
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic