db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Real Time Java Application <realtimej...@hotmail.com>
Subject RE: Derby SQL statement to retrieve DAILY, WEEKLY, MONTHLY and YEARLY Data from hour based table
Date Tue, 23 Feb 2010 14:45:51 GMT

I have taken a look at Built-in functions, however there is no "Week", "Quarter" functions
in Derby DB.It seems to me that one workaround might be JDBC escape syntax for fn keyword
like "{fn TIMESTAMPDIFF(SQL_TSI_WEEK, proddate, timestamp('2001-01-01-12.00.00.000000'))}"
or re-write java code as Rick 's past post in the list(http://mail-archives.apache.org/mod_mbox/db-derby-user/200510.mbox/%3C43418926.6010407@sun.com%3E).
To write java code is really expensive.

How can I use above given JDBC escape syntax for fn keyword in below listed table in order
to extract weekly and quarterly based data?

Thank you for your help.

Regards,

From: realtimejava@hotmail.com
To: derby-user@db.apache.org
Subject: RE: Derby SQL statement to retrieve DAILY, WEEKLY, MONTHLY and YEARLY Data from hour
based table
Date: Tue, 23 Feb 2010 12:40:33 +0100








Hello Rick, Geoff,

Thank you for your suggestions.

Regards,


Date: Mon, 22 Feb 2010 12:41:51 -0800
From: geoff_hendrey@yahoo.com
Subject: Re: Derby SQL statement to retrieve DAILY, WEEKLY, MONTHLY and YEARLY Data from hour
based table
To: derby-user@db.apache.org

I've used the built in day and month functions and they work fine too:

http://db.apache.org/derby/docs/10.5/ref/rrefdayfunc.html

-geoff


--- On Mon, 2/22/10, Rick Hillegas <Richard.Hillegas@Sun.COM> wrote:

From: Rick Hillegas <Richard.Hillegas@Sun.COM>
Subject: Re: Derby SQL statement to retrieve DAILY, WEEKLY, MONTHLY and YEARLY Data from hour
based table
To: "Derby Discussion" <derby-user@db.apache.org>
Date: Monday, February 22, 2010, 12:37 PM

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>

 		 	   		  
Your E-mail and More On-the-Go. Get Windows Live Hotmail Free. Sign up now. 		 	   		  
_________________________________________________________________
Hotmail: Trusted email with Microsoft’s powerful SPAM protection.
https://signup.live.com/signup.aspx?id=60969
Mime
View raw message