db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From kirru23 <kiran.nellima...@gmail.com>
Subject Re: Unix_timestamp value and date formatting issue
Date Wed, 02 Jun 2010 15:58:33 GMT

I have PHP as my front end and so I am using db2 runtime client for conecting
to the derby database.


Kristian Waagan-4 wrote:
> 
> 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
>>
>>    
> 
> 
> 

-- 
View this message in context: http://old.nabble.com/Unix_timestamp-value-and-date-formatting-issue-tp28749721p28756533.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.


Mime
View raw message