db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Kristian Waagan <Kristian.Waa...@Sun.COM>
Subject Re: Unix_timestamp value and date formatting issue
Date Wed, 02 Jun 2010 07:03:19 GMT
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.


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 

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 row selected

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 

> 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?



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

View raw message