Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Received-SPF: pass (athena.apache.org: domain of lists@nabble.com designates
 216.139.236.158 as permitted sender)
Message-ID: <28749243.post@talk.nabble.com>
Date: Tue, 1 Jun 2010 18:33:47 -0700 (PDT)
From: kirru23 <kiran.nellimarla@gmail.com>
To: derby-dev@db.apache.org
Subject: Re: Format the timestamp value
In-Reply-To: <4C03C8CE.2040605@gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
References: <28726194.post@talk.nabble.com> <4C032ABE.1040603@gmail.com>
 <28726566.post@talk.nabble.com> <28726926.post@talk.nabble.com>
 <4C03C8CE.2040605@gmail.com>


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.

Regarding the 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.

If anyone got a solution for above isues, pls suggest

Thanks!
 

Bryan Pendleton-3 wrote:
> 
> On 05/30/2010 10:44 PM, kirru23 wrote:
>> I tried using {fn timestampdiff(SQL_TSI_SECOND,
>> current_timestamp,timestamp('1970-01-01-00.00.00')}
>> But this doesnt give me the exact result as fetched by UNIX_TIMESTAMP
>> function.
> 
> What was the difference? Is it due to time zone adjustments?
> 
> If the built-in Derby date/time functions are not quite meeting
> your needs, you can write your own functions in Java, and call
> them from your SQL:
> 
> http://wiki.apache.org/db-derby/DerbySQLroutines
> 
> thanks,
> 
> bryan
> 
> 

-- 
View this message in context: http://old.nabble.com/Fetch-the-hour-part-from-the-timestamp-value-tp28726194p28749243.html
Sent from the Apache Derby Developers mailing list archive at Nabble.com.