Page 1 of 2

SQl Query to find time diff in minutes from Timestmp

PostPosted: Tue Jun 21, 2011 9:29 pm
by Arun Bv
hi can anyone tell me how to find time diffe in minutes from timestamp...

i tried below sql queries but this is not retrieving correct diff in minutes


SELECT TRUNC((86400*(CURRENT TIMESTAMP - ('2011-06-18-05.03.41.300585')))/60)
-60*(TRUNC(((86400*(CURRENT TIMESTAMP - ('2011-06-18-05.03.41.300585')))/60)
/60))
FROM SYSIBM.SYSDUMMY1



SELECT MINUTE(CURRENT TIMESTAMP) - MINUTE('2011-06-15-02.01.56.178837')
FROM SYSIBM.SYSDUMMY1



05 WS-TIME-DIFF PIC X(04).
05 BDR-BKT-REQ-TIMESTMP PIC X(26) VALUE '2011-06-15-02.01.56.178837' .
EXEC SQL
SET :WS-TIME-DIFF = MINUTE(CURRENT TIMESTAMP) - MINUTE(BDR-BKT-REQ-TIMESTMP)
END-EXEC




SO IF ANYONE KNEW TO FIND TIME DIFFERENCE FROM TIMESTAMP IN MINUTES ...PLS TELL ME AS SOON AS POSSIBLE...ITS URGENT

Re: (URGENT)SQl Query to find time diff in minutes from Time

PostPosted: Tue Jun 21, 2011 9:37 pm
by BillyBoyo
Try google. I have other urgent things.

Re: (URGENT)SQl Query to find time diff in minutes from Time

PostPosted: Tue Jun 21, 2011 9:37 pm
by Robert Sample
This is a forum where responses are voluntary and done at our own time. If your need is that urgent, hire a consultant or pay a vendor for an answer.

Re: (URGENT)SQl Query to find time diff in minutes from Time

PostPosted: Tue Jun 21, 2011 9:47 pm
by Akatsukami
Arun Bv wrote:hi can anyone tell me how to find time diffe in minutes from timestamp...

i tried below sql queries but this is not retrieving correct diff in minutes

As you allege to know COBOL, why not retrieve the timestamps from DB2 and then calculate the difference in the COBOL program in which the queries are embedded?

Re: SQl Query to find time diff in minutes from Timestmp

PostPosted: Wed Jun 22, 2011 12:06 pm
by GuyC
days(ts1) - days(ts2) gives you the difference of days between the date_part of the timestamps , multiply by 24 * 60 => #minutes
midnights_second(ts1) - midnights_second(ts2) gives you difference of seconds between the time_part of the timestamp, divide by 60 => #minutes

Re: SQl Query to find time diff in minutes from Timestmp

PostPosted: Wed Jun 22, 2011 6:19 pm
by BillyBoyo
Arun, what was that PM all about?

Arun doesn't like advice. He pondered upon why I should answer like that if I was short of time. Well, you can take advice, or leave it. The giver can't know beforehand which advice you'd like to hear.

Arun has already had advice from elsewhere. Curiously, the thing I knew was there for him to find on google.

Why don't you take Mr Akatsukami's advice?

Re: SQl Query to find time diff in minutes from Timestmp

PostPosted: Wed Jun 22, 2011 6:29 pm
by Robert Sample
BillyBoyo: yeah, I got a PM as well. Apparently the urgency was such that Google could not be consulted -- but formatting and sending PMs could be done.

Re: SQl Query to find time diff in minutes from Timestmp

PostPosted: Wed Jun 22, 2011 7:01 pm
by BillyBoyo
Robert, the funny thing is, if he'd googled correctly he'd have found the question he asked in the expert's forum with two solutions supplied to him. Now I notice that his comp-3 has reverted back to PIC X(4) as well. He can't google, we can.

Arun, you now have three solutions. None of which involve forests of parentheses.

If you still want help, can you tell us exactly (not just repeating "I want the difference in minutes) what it is you are trying to do.

Re: SQl Query to find time diff in minutes from Timestmp

PostPosted: Wed Jun 22, 2011 7:11 pm
by Akatsukami
Arun didn't send me a PM; I'm devastated :mrgreen:

Re: SQl Query to find time diff in minutes from Timestmp

PostPosted: Wed Jun 22, 2011 7:59 pm
by BillyBoyo
Perhaps he took your good advice?

He didn't thank GuyC, so perhaps he sent him a nice PM?