You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by kirru23 <ki...@gmail.com> on 2010/06/02 05:20:37 UTC
Unix_timestamp value and date formatting issue
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.
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.
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-tp28749721p28749721.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: Unix_timestamp value and date formatting issue
Posted by Kathey Marsden <km...@sbcglobal.net>.
On 6/2/2010 11:45 AM, kirru23 wrote:
> I dont think I completely understand what you mean. But my project is an open
> source one and I have to connect php with derby.
> Is there any other way of connecting both without db2?
>
There are ODBC drivers listed on the UsesOfDerby page on the Wiki [1] ,
but I don't know if any users have had success with them. Perhaps it
would be good to start another thread asking about ODBC connectivity
with Derby.
> Assuming that this is the only way I have to continue, can u suggest any
> method of formatting the timestamp values?
>
>
I should not have hijacked your timestamp thread which I don't think was
particular to the client. I don't have suggestions on timestamp, beyond
those offered already, but saw the DB2 bit and wanted to save you a trip
down a dead end road. Please start another thread if you would like to
discuss ODBC connectivity and/or DB2 clients.
Thanks
Kathey
[1] http://wiki.apache.org/db-derby/UsesOfDerby
Re: Unix_timestamp value and date formatting issue
Posted by kirru23 <ki...@gmail.com>.
I dont think I completely understand what you mean. But my project is an open
source one and I have to connect php with derby.
Is there any other way of connecting both without db2?
Assuming that this is the only way I have to continue, can u suggest any
method of formatting the timestamp values?
thanks!
Kathey Marsden wrote:
>
> On 6/2/2010 8:58 AM, kirru23 wrote:
>> I have PHP as my front end and so I am using db2 runtime client for
>> conecting
>> to the derby database.
>>
> The highest versions where this configuration was supported was Derby.
> 10.1 with DB2 runtime client 8.2. If later versions happened to work
> it was chance as it wasn't tested or supported by anyone. I am fairly
> confident the configuration will completely break with upcoming DRDA
> changes in both products as there will be no effort to make it work, so
> want to make sure you are aware of that before investing any more of
> your own time into this configuration.
>
> Thanks
>
> Kathey
>
>
>
>
>
>
--
View this message in context: http://old.nabble.com/Unix_timestamp-value-and-date-formatting-issue-tp28749721p28758440.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: Unix_timestamp value and date formatting issue
Posted by Kathey Marsden <km...@sbcglobal.net>.
On 6/2/2010 8:58 AM, kirru23 wrote:
> I have PHP as my front end and so I am using db2 runtime client for conecting
> to the derby database.
>
The highest versions where this configuration was supported was Derby.
10.1 with DB2 runtime client 8.2. If later versions happened to work
it was chance as it wasn't tested or supported by anyone. I am fairly
confident the configuration will completely break with upcoming DRDA
changes in both products as there will be no effort to make it work, so
want to make sure you are aware of that before investing any more of
your own time into this configuration.
Thanks
Kathey
Re: Unix_timestamp value and date formatting issue
Posted by kirru23 <ki...@gmail.com>.
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.
Re: Unix_timestamp value and date formatting issue
Posted by Kristian Waagan <Kr...@Sun.COM>.
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
>
>