You are viewing a plain text version of this content. The canonical link for it is here.

Posted to derby-user@db.apache.org by manojkumar16 <ma...@gmail.com> on 2013/04/29 08:50:52 UTC

How to INSERT (CURRENT_DATE+7 DAYS) in apache derby

I am writing a default scheduler for my project and it needs to be triggered
7 days later the deployment of project.
Can somebody help me writing an INSERT statement which takes (CURRENT_DATE +
7 days) as a value for DATE field.

INSERT INTO PURGE_ACC (START_DATE, REPEAT_INTERVAL) VALUES ( *(CURRENT_DATE
+ 7 DAYS)*, 604800000);

REPEAT_INTERVAL is of long type and value associated with it is 7 days.
I am having issue in settting [START_DATE+7 days] value.

--
Thanks,
Manoj



--
View this message in context: http://apache-database.10148.n7.nabble.com/How-to-INSERT-CURRENT-DATE-7-DAYS-in-apache-derby-tp129786.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.

Re: How to INSERT (CURRENT_DATE+7 DAYS) in apache derby

Posted by Rick Hillegas <ri...@oracle.com>.

On 4/28/13 11:50 PM, manojkumar16 wrote:
> I am writing a default scheduler for my project and it needs to be triggered
> 7 days later the deployment of project.
> Can somebody help me writing an INSERT statement which takes (CURRENT_DATE +
> 7 days) as a value for DATE field.
>
> INSERT INTO PURGE_ACC (START_DATE, REPEAT_INTERVAL) VALUES ( *(CURRENT_DATE
> + 7 DAYS)*, 604800000);
>
> REPEAT_INTERVAL is of long type and value associated with it is 7 days.
> I am having issue in settting [START_DATE+7 days] value.
>
> --
> Thanks,
> Manoj
>
>
>
> --
> View this message in context: http://apache-database.10148.n7.nabble.com/How-to-INSERT-CURRENT-DATE-7-DAYS-in-apache-derby-tp129786.html
> Sent from the Apache Derby Users mailing list archive at Nabble.com.
>
Hi Manoj,

A user-defined function could help out here. Here's the java function 
you need:

public  class   z
{
     private static  final   long    MILLIS_IN_DAY = 1000 * 60 * 60 * 24;
     public  static  java.sql.Date    sevenDaysLater()
     {
         return new java.sql.Date( System.currentTimeMillis() + 
(MILLIS_IN_DAY * 7) );
     }
}

...and here's a script which declares this function and uses it to 
insert into a table:

connect 'jdbc:derby:memory:db;create=true';

create function sevenDaysLater() returns date
language java parameter style java no sql
external name 'z.sevenDaysLater';

create table t( dateCol date );

insert into t( dateCol ) values ( sevenDaysLater() );

select * from t;

Hope this helps,
-Rick

Re: How to INSERT (CURRENT_DATE+7 DAYS) in apache derby

Posted by manojkumar16 <ma...@gmail.com>.

Thanks, it solved the problem.



--
View this message in context: http://apache-database.10148.n7.nabble.com/How-to-INSERT-CURRENT-DATE-7-DAYS-in-apache-derby-tp129786p129817.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.

Re: How to INSERT (CURRENT_DATE+7 DAYS) in apache derby

Posted by Knut Anders Hatlen <kn...@oracle.com>.

manojkumar16 <ma...@gmail.com> writes:

> I am writing a default scheduler for my project and it needs to be triggered
> 7 days later the deployment of project.
> Can somebody help me writing an INSERT statement which takes (CURRENT_DATE +
> 7 days) as a value for DATE field.
>
> INSERT INTO PURGE_ACC (START_DATE, REPEAT_INTERVAL) VALUES ( *(CURRENT_DATE
> + 7 DAYS)*, 604800000);
>
> REPEAT_INTERVAL is of long type and value associated with it is 7 days.
> I am having issue in settting [START_DATE+7 days] value.

Hi Manoj,

There is a JDBC escape function called TIMESTAMPADD that you could use
for this. It produces a timestamp rather than a date, but you can cast
the resulting timestamp to a date and insert it into the table.

I think something like this should do the trick:

insert into purge_acc(start_date, repeat_interval)
   values (cast({ fn timestampadd(sql_tsi_day, 7, current_date) } as date), 604800000)

The TIMESTAMPADD function is documented here:
http://db.apache.org/derby/docs/10.10/ref/rrefjdbc88908.html

Hope this helps,

-- 
Knut Anders