You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by kirru23 <ki...@gmail.com> on 2010/05/31 04:24:03 UTC
Fetch the hour part from the timestamp value
Hello,
i want to convert a mysql to derby database. So, I want to find an
equvivalent for DATE_FORMAT() function in mysql.
I have a timestamp field in my table and I want to know how we can fetch
only the hour part from the timestamp in derby.
similary, second part, day part etc. I am using PHP as my front end. I saw
this link
http://www.seemoredata.com/en/showthread.php?42-using-Derby-database-Timestamps-intelligently
but the functions there dont work in derby.
Please advice! i really need some input on this..
--
View this message in context: http://old.nabble.com/Fetch-the-hour-part-from-the-timestamp-value-tp28726194p28726194.html
Sent from the Apache Derby Developers mailing list archive at Nabble.com.
Re: Format the timestamp value
Posted by kirru23 <ki...@gmail.com>.
Thank you so much bryan!
Actually I am in EST time zone and the tz gap is 5 hrs actually. So i will
try to set it to GMT and see the results.
Also, i posted in derbt users forum for an alternate solution.
Thanks for ur input!
Bryan Pendleton-3 wrote:
>
>> Hence the difference is around 17970 seconds.
>
> Looks pretty close to 5 hours. (5*3600=18000). Is 5 hours approximately
> your TZ correction from GMT?
>
>> 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'm not sure it's very easy to control the TZ that Derby's
> CURRENT_TIMESTAMP uses.
>
> You could try setting TZ=GMT when running Derby, to see if the answer
> changes,
> and if the new result is closer to what you get with MYSQL.
>
> To really get control over these behaviors, though, you probably will
> need to write your own date/time formatting code. I understand that if
> you are working in PHP this is going to be a challenge; Derby is most
> easily used in a native Java configuration, unfortunately.
>
> You might try asking these questions on derby-users, rather than on
> derby-dev, as derby-dev tends to attract mostly Derby developers, while
> derby-users has lots of people who have dealt with Derby application
> development issues.
>
> thanks,
>
> bryan
>
>
--
View this message in context: http://old.nabble.com/Fetch-the-hour-part-from-the-timestamp-value-tp28726194p28749732.html
Sent from the Apache Derby Developers mailing list archive at Nabble.com.
Re: Format the timestamp value
Posted by Bryan Pendleton <bp...@gmail.com>.
> Hence the difference is around 17970 seconds.
Looks pretty close to 5 hours. (5*3600=18000). Is 5 hours approximately
your TZ correction from GMT?
> 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'm not sure it's very easy to control the TZ that Derby's CURRENT_TIMESTAMP uses.
You could try setting TZ=GMT when running Derby, to see if the answer changes,
and if the new result is closer to what you get with MYSQL.
To really get control over these behaviors, though, you probably will
need to write your own date/time formatting code. I understand that if
you are working in PHP this is going to be a challenge; Derby is most
easily used in a native Java configuration, unfortunately.
You might try asking these questions on derby-users, rather than on
derby-dev, as derby-dev tends to attract mostly Derby developers, while
derby-users has lots of people who have dealt with Derby application
development issues.
thanks,
bryan
Re: Format the timestamp value
Posted by kirru23 <ki...@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.
Re: Format the timestamp value
Posted by Bryan Pendleton <bp...@gmail.com>.
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
Re: Format the timestamp value
Posted by kirru23 <ki...@gmail.com>.
Hello,
Here, I am again stuck with date formatting.
I read that timestamp format is yyyy-mm-dd hh[:mm[:ss[.nnnnnn]]]
Can we change is to other formats? Please advice!
Also , I wanted to know if anyone got some solution for UNIX_TIMESTAMP in
derby.
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.
Is there any other way to find an exact value?
Thanks in advance!
kirru23 wrote:
>
> Thanks a lot Bryan!
> That link was very helpful !
>
>
> Bryan Pendleton-3 wrote:
>>
>>> I have a timestamp field in my table and I want to know how we can fetch
>>> only the hour part from the timestamp in derby.
>>
>> There is an HOUR() function in Derby:
>> http://db.apache.org/derby/docs/10.6/ref/rrefhourfunc.html
>>
>> thanks,
>>
>> bryan
>>
>>
>
>
--
View this message in context: http://old.nabble.com/Fetch-the-hour-part-from-the-timestamp-value-tp28726194p28726926.html
Sent from the Apache Derby Developers mailing list archive at Nabble.com.
Re: Fetch the hour part from the timestamp value
Posted by kirru23 <ki...@gmail.com>.
Thanks a lot Bryan!
That link was very helpful !
Bryan Pendleton-3 wrote:
>
>> I have a timestamp field in my table and I want to know how we can fetch
>> only the hour part from the timestamp in derby.
>
> There is an HOUR() function in Derby:
> http://db.apache.org/derby/docs/10.6/ref/rrefhourfunc.html
>
> thanks,
>
> bryan
>
>
--
View this message in context: http://old.nabble.com/Fetch-the-hour-part-from-the-timestamp-value-tp28726194p28726566.html
Sent from the Apache Derby Developers mailing list archive at Nabble.com.
Re: Fetch the hour part from the timestamp value
Posted by Bryan Pendleton <bp...@gmail.com>.
> I have a timestamp field in my table and I want to know how we can fetch
> only the hour part from the timestamp in derby.
There is an HOUR() function in Derby:
http://db.apache.org/derby/docs/10.6/ref/rrefhourfunc.html
thanks,
bryan