Timedifference in seconds??
Dirk Lerner
how can I get the result in seconds in the following sql-statement?:
db2 "select TICKET_ID, (CURRENT TIMESTAMP - CHANGE_TIME) as diff
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
from STTS_TICKET"
CHANGE_TIME is a timestamp in the tanle.
This SQL-Statement gives a result like this:
TICKET_ID DIFF
----------- ----------------------
72 122015629.344565
~~~~~~~~~~~~~~~
but this are no seconds :-(
I didn't find a possibility to convert this dayhourminutessecond format
into seconds.
Can you help me?
Thanks
Dirk
Jan M. Nelken
Dirk,
The answer is "It depends ..." - as usual in the world of databases. If you
can be sure that TICKET_ID and CURRENT TIMESTAMP are within the same day
always you could use construct like this:
midnight_seconds(CURRENT TIMESTAMP) - midnight_seconds(TICKET_ID)
otherwise you may try:
timestampdiff(2, char(CURRENT TIMESTAMP - TICKET_ID))
In any case browsing SQL reference would be beneficial.
--
Jan M. Nelken
-------------------------------------------------------------------------------
Home e-mail: nelkenj at attglobal period net
IBM Certified Solutions Expert DB2 UDB V6.1 for UNIX, Windows & OS/2
- Database Administration,
- Application Development
IBM Certified Advanced Technical Expert DB2 UDB V6.1 for UNIX, Windows &
OS/2
- Distributed Relational Database Architecture (DRDA)
-------------------------------------------------------------------------------
Dirk Lerner
> The answer is "It depends ..." - as usual in the world of databases. If
> you can be sure that TICKET_ID and CURRENT TIMESTAMP are within the same
> day always you could use construct like this:
>
> midnight_seconds(CURRENT TIMESTAMP) - midnight_seconds(TICKET_ID)
>
> otherwise you may try:
>
> timestampdiff(2, char(CURRENT TIMESTAMP - TICKET_ID))
>
Yes, you are right. The problem is, that the range of midnight_seconds is
not wide enough and with timestampdiff there could be a difference of
several days. And in the documentation I didn't find anything to calculate
the seconds correct. It seems a bit strange to me, that db2 cannot
calculate over a year, e.g. , the correct time. Is there a special
internal timeformat, not seconds?
Dirk
Kaarel Truuvert
the TIMESTAMPDIFF function would be of use? It's got limitations - read
about it in the SQL Reference - but maybe it would be appropriate for
your use.
--
Kaarel Truuvert
DB2 UDB Development
Jan M. Nelken
> Yes, you are right. The problem is, that the range of midnight_seconds is
> not wide enough and with timestampdiff there could be a difference of
> several days.
I fail to understand how the difference coud be in days? Format used of
timestampdiff returns number of seconds between two timestamps. It discards
microseconds, however.
I tested it with two timestamps from different years and calculated number of
seconds seems to be correct. I did not test leap years nor dates different by
hundreds of years.
Could you present a case when timestampdiff in seconds between two timestamps
is wrong by days?
I am also curious as to what business case requires calculation in seconds of
difference between timestamps. Perhaps whole probelm could be addressed in
diffeent way?
Dirk Lerner
> I fail to understand how the difference coud be in days? Format used of
> timestampdiff returns number of seconds between two timestamps. It
> discards microseconds, however.
No, like described in the SQL-Reference:
----cuton----
[...]
The following assumptions may be used in estimating the difference:
there are 365 days in a year there are 30 days in a month there are
24 hours in a day there are 60 minutes in an hour there are 60
seconds in a minute
These assumptions are used when converting the information in the second
argument, which is a timestamp duration, to the interval type specified in
the first argument. The returned estimate may vary by a number of days.
For example, if the number of days (interval 16) is requested for a
difference in timestamps for '1997-03-01-00.00.00' and
'1997-02-01-00.00.00', the result is 30. This is because the difference
between the timestamps is 1 month so the assumption of 30 days in a month
applies.
[...]
---cutoff---
And I tried it:
db2 "select TICKET_ID, timestampdiff(2, char(CURRENT TIMESTAMP -
CHANGE_TIME)) as diffseconds, timestampdiff(16, char(CURRENT TIMESTAMP -
CHANGE_TIME)) as diffday ,current timestamp as currentdate, change_time
from STTS_TICKET
TICKET_ID DIFFSECONDS DIFFDAY CURRENTDATE CHANGE_TIME
----------- ----------- ---------------------------------------------------------------
72 4549501 52 2000-12-13-07.47.28.716044 -16.02.26.777402
ok, seconds and days match here! That's true. But if you count the days by
hand, than you will get a different value. From 2000-10-21 until
2000-12-13 I've calculated 53.xx days. In this calculation of DB2 is
missing the 31st october.
> I am also curious as to what business case requires calculation in
> seconds of difference between timestamps. Perhaps whole probelm could be
> addressed in diffeent way?
For example:
We need the timedifference for the escalation of tickets. And there are
different levels of escalation. If it is an old ticket the escalation
level is higher... And you can imagine if this doesn't fit there will be
some problems.
Dirk
Dirk Lerner
> Dirk (I'm assuming you're using DB2 UDB on unix, nt or os/2), perhaps
> the TIMESTAMPDIFF function would be of use? It's got limitations - read
> about it in the SQL Reference - but maybe it would be appropriate for
> your use.
Sorry, I forgot it: IBM UDB V7.1 on SuSE Linux 7.0
Fixpack 1
This limitations are the problem. Look to the other mail please.
Dirk
Jan M. Nelken
> argument, which is a timestamp duration, to the interval type specified in
> the first argument. The returned estimate may vary by a number of days.
> For example, if the number of days (interval 16) is requested for a
> difference in timestamps for '1997-03-01-00.00.00' and
> '1997-02-01-00.00.00', the result is 30. This is because the difference
> between the timestamps is 1 month so the assumption of 30 days in a month
> applies.
Again - I fail to understand why difference in timestamp between dates spanning multiple months must
be calculated in seconds!
If you have two tickets - 60 days "old" and 59 days "old" - what is more important - to find older
or to know that "oldert is 2 seconds older than "newer"?
You could use for example as an indication of "age" of ticket - julian_day function like this:
db2 select julian_day(current timestamp)-julian_day(change_time)
and - if the result is 0 meaning change occured today - use midnight_seconds().
Other approach would be to write UDF to calculate difference in seconds between two timestamps.
David Sharpe
Checkout the JULIAN_DAY() function. On another thread, Serge and I suggested an sql udf to calculate
the seconds difference based on the JULIAN_DAY(). It calculated the whole number of days difference
(* 24 hours * 60 min * 60 sec), then added the partial day difference.
Unfortunatly, I don't have the sent note immeadiatly at hand, maybe you can find it on deja?
Hope this helps,
David.
Dirk Lerner
> Hi Dirk, Checkout the JULIAN_DAY() function. On another thread, Serge
> and I suggested an sql udf to calculate the seconds difference based on
> the JULIAN_DAY(). It calculated the whole number of days difference
> (* 24 hours * 60 min * 60 sec), then added the partial day difference.
Thank you. With this hints I solved the problem. I developed a udf for it.
My developers are happy. ;-)
Dirk