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/%[email protected]%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: [email protected]
To: [email protected]
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: [email protected]
Subject: Re: Derby SQL statement to retrieve DAILY, WEEKLY, MONTHLY and YEARLY
Data from hour based table
To: [email protected]
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 <[email protected]> wrote:
From: Rick Hillegas <[email protected]>
Subject: Re: Derby SQL statement to retrieve DAILY, WEEKLY, MONTHLY and YEARLY
Data from hour based table
To: "Derby Discussion" <[email protected]>
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
