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
