db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <Richard.Hille...@Sun.COM>
Subject Re: Derby SQL statement to retrieve DAILY, WEEKLY, MONTHLY and YEARLY Data from hour based table
Date Mon, 22 Feb 2010 20:37:37 GMT
Hello,

I think you can solve this problem by writing some functions and then 
using grouped aggregates. There is already a builtin year() function 
which should do what you need. You will want to write other functions to 
extract the day, week, month, and quarter from your date column. Here is 
query which sums up the amount1 column for each year:

select year( dateCol ), sum( amount1 )
from t
group by year( dateCol );

Hope this helps,
-Rick

Real Time Java Application wrote:
>
> Dear Experts,
>
>
> I have been using Derby DB in a Java desktop application. I have one 
> database table which is updated explictly via different information 
> resources as follows:
>
>
> [Date: DATETIME Stamp] [Amount 1: int] [Amount 2: int]
>
> 2009-01-21 16:01:00.000 61 23
>
> 2009-01-21 17:01:00.000 70 25
>
> ...
> 2009-02-12 07:03:00.000 94 31
>
> 2009-02-12 07:04:00.000 64 55
>
> 2009-02-12 07:11:00.000 74 32
>
> ...
>
> I'd ask you, how can I create a Derby SQL statement in order to 
> retrieve DAILY, WEEKLY, MONTHLY,QUARTELY and YEARLY Date/Data from 
> this hour based DB table?
>
>
> Thank you in advance.
>
>
> Best regards,
>
>
>
> Your E-mail and More On-the-Go. Get Windows Live Hotmail Free. Sign up 
> now. <https://signup.live.com/signup.aspx?id=60969>


Mime
View raw message