Elapsed time between timestamps

javydreamercsw · #1 (**permalink**) 06-08-06, 11:55

A query like this one:

Code:

SELECT TIMESTAMPA-TIMESTAMPB FROM MYTABLE

Gives something like:
2006-02-13 13:32:28.0 - 2005-11-09 15:05:36.0 =
303222749

which I know converts to:

95 Days 22 Hours and 31 Min. 52 Secs.

I know timestampdiff and other specific DB2 functions can handle this but the problem is that I'm using a report tool (iReport) that doesn't recognize DB specific functions.

How I can do that from a query?

grofaty · #2 (**permalink**) 06-09-06, 07:09

Hi,
can you create new column in DB2 source database? If yes, then you can create formula with generated always statement.

Sample:
CREATE TABLE DB2ADMIN.TABLE (COL1 TIMESTAMP, COL2 TIMESTAMP)
SET INTEGRITY FOR DB2ADMIN.TABLE OFF
ALTER TABLE DB2ADMIN."TABLE" ADD COLUMN SUB1 INTEGER NOT NULL GENERATED ALWAYS AS (COL1-COL2)
SET INTEGRITY FOR DB2ADMIN.TABLE IMMEDIATE CHECKED FORCE GENERATED

Note: You have to turn off integrity check before alter table definition is applied, just like in sample above.

If above statement is not a solution, try to create exactly the input you need with formula.

Hope this helps,
Grofaty

javydreamercsw · #3 (**permalink**) 06-09-06, 09:34

Sadly adding a column is not possible for 2 reasons, I don't have admin access and is a validated enviroment.

I'm not clear with what you mean by:

"...try to create exactly the input you need with formula."

I'm not really used to DB2. I get this from a Crystal Report's report. Using a viewer it defines this field as @Elapsed time which get the results I showed.

Here's what I've managed to do (from http://www-128.ibm.com/developerwork...211yip3.html):

Code:

CREATE FUNCTION secondsdiff(t1 TIMESTAMP, t2 TIMESTAMP) 
RETURNS INT 
RETURN ( 
(DAYS(t1) - DAYS(t2)) * 86400 + 
(MIDNIGHT_SECONDS(t1) - MIDNIGHT_SECONDS(t2)) 
)

And my new query:

Code:

SELECT secondsdiff(A.DEPT_TIMESTAMP,B.DEPT_TIMESTAMP)/86400 as elapsed_days, 
(secondsdiff(A.DEPT_TIMESTAMP,B.DEPT_TIMESTAMP)-((secondsdiff(A.DEPT_TIMESTAMP,B.DEPT_TIMESTAMP)/86400)*86400))/3600 as elapsed_hours
 FROM BAX_DEPT_SCHED_LG A,BAX_DEPT_SCHED_LG B WHERE 
A.BATCH_ID = B.BATCH_ID AND B.REASON = '' 
AND A.REASON ='MFG VERIFICATION' AND B.BATCH_ID ='UR295881'

And the result:

Code:

ELAPSED_DAYS	ELAPSED_HOURS
95	             22

I went for the mins/sec part but the query became way too complicated and never pulled it of.

Maybe someone out there has a better way or can pull it off in a more elegant way.

Thanks in advance.

Peter.Vanroose · #4 (**permalink**) 06-11-06, 11:58

Quote:

Originally Posted by javydreamercsw

2006-02-13 13:32:28.0 - 2005-11-09 15:05:36.0 =
303222749
which I know converts to:
95 Days 22 Hours and 31 Min. 52 Secs.

Actually, it converts to 3 months, 3 days, 22 hours, 27 minutes, 49 seconds.

Hence you may interpret a timestamp difference with the SUBSTR function to extract the different parts.

javydreamercsw · #5 (**permalink**) 06-12-06, 08:26

Here's the answer:

Code:

SELECT
SUBSTR(CHAR(A.DEPT_TIMESTAMP - B.DEPT_TIMESTAMP),1,2) as elapsed_centuries,
SUBSTR(CHAR(A.DEPT_TIMESTAMP - B.DEPT_TIMESTAMP),3,2) as elapsed_years,
SUBSTR(CHAR(A.DEPT_TIMESTAMP - B.DEPT_TIMESTAMP),5,2) as elapsed_months,
SUBSTR(CHAR(A.DEPT_TIMESTAMP - B.DEPT_TIMESTAMP),7,2) as elapsed_days,
 SUBSTR(CHAR(A.DEPT_TIMESTAMP - B.DEPT_TIMESTAMP),9,2) as elapsed_hours,
SUBSTR(CHAR(A.DEPT_TIMESTAMP - B.DEPT_TIMESTAMP),11,2) as elapsed_mins,
SUBSTR(CHAR(A.DEPT_TIMESTAMP - B.DEPT_TIMESTAMP),13,2) as elapsed_seconds
FROM BAX_DEPT_SCHED_LG A,BAX_DEPT_SCHED_LG B WHERE
A.BATCH_ID = B.BATCH_ID AND B.REASON = '' 
AND A.REASON ='MFG VERIFICATION' AND B.BATCH_ID ='UR295881'

And the result:

Code:

ELAPSED_CENTURIES	ELAPSED_YEARS	ELAPSED_MONTHS	ELAPSED_DAYS	ELAPSED_HOURS	ELAPSED_MINS	ELAPSED_SECONDS
00	00	03	03	22	26	52

I'm guessing the first two fields are centuries and years. Please correct me if I'm wrong.

Thanks for the usefull hint!

Internet Services

Web Development

Digital Marketing

Consumer Tech