Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (athena.apache.org: local policy)
MIME-version: 1.0
Content-transfer-encoding: 7BIT
Content-type: text/plain; CHARSET=US-ASCII; format=flowed
Date: Wed, 02 Jun 2010 09:03:19 +0200
From: Kristian Waagan <Kristian.Waagan@Sun.COM>
Subject: Re: Unix_timestamp value and date formatting issue
In-reply-to: <28749721.post@talk.nabble.com>
Sender: Kristian.Waagan@Sun.COM
To: derby-user@db.apache.org
Message-id: <4C060237.90305@Sun.COM>
References: <28749721.post@talk.nabble.com>
User-Agent: Mozilla/5.0 (X11; U; SunOS i86pc; en-US;
 rv:1.9.1.8) Gecko/20100412
 Lightning/1.0b1 Thunderbird/3.0.3

On 02.06.10 05:20, kirru23 wrote:
> I am working on converting mysql to derby database. I am having some
> problems:
> in mysql the result of the query:
> mysql>  select unix_timestamp(current_timestamp);
> +-----------------------------------+
> | unix_timestamp(current_timestamp) |
> +-----------------------------------+
> |                        1275441961 |
> +-----------------------------------+
> in derby :
> ij>  select {fn   TIMESTAMPDIFF(
> SQL_TSI_second,timestamp('1970-01-01-00.00.00'),current timestamp)}from
> sysibm.sysdummy1;
> 1
> --------------------
> 1275423991
>
> Hence the difference is around 17970 seconds.
> Does anyone implement any other formula for getting a more accurate value.
> I am new to derby, so pls suggest how do i consider/check the timezone . Is
> this difference due to timeze? if yes then how can I change me query to
> consider that.
>    

Hi,

I believe the value you obtain from Derby takes the timezone of the 
server into account.
Does ~17970 seconds match the difference between GMT/UTC and the server 
timezone?

One way to work around this, is to define a function in Derby:
ij> create function unixtime() returns bigint parameter style java no 
sql language java external name 'java.lang.System.currentTimeMillis';
0 rows inserted/updated/deleted
ij> values unixtime() / 1000;
1
--------------------
1275461033

1 row selected
ij>

You can also code the function yourself in your own class, but then you 
have to include that class on the server classpath.

Of course, the query you used above will also return the unix time if 
you set the server timezone to UTC (is using -Duser.timezone=UTC 
sufficient?).

> Another isue is date formatting.
> I wanted to format it like the year part as yy instead of yyyy, fetchin the
> date as 'd' instead of 'dd' etc. If there is no way of formatting by any
> functions then moslty i wil try to manipulate it using code. Also, since i
> am using PHP code i mite not be able to use the java functions. I heard
> there is a function format_timestamp() in derby for formatting but it doesnt
> work. let me know if anyone tried that out.
>    

Do you mind telling how you're using PHP with Derby?
I.e., are you using something like PHP/Java Bridge, Zend or Quercus?


Regards,
-- 
Kristian

>    

> If anyone got a solution for above isues, pls suggest
>
>