SQl Query to find time diff in minutes from Timestmp



IBM's flagship relational database management system

SQl Query to find time diff in minutes from Timestmp

Postby Arun Bv » Tue Jun 21, 2011 9:29 pm

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
Arun Bv
 
Posts: 5
Joined: Tue Jun 21, 2011 9:19 pm
Has thanked: 3 times
Been thanked: 0 time

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

 

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

Postby BillyBoyo » Tue Jun 21, 2011 9:37 pm

Try google. I have other urgent things.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 264 times

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

Postby Robert Sample » Tue Jun 21, 2011 9:37 pm

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.
Robert Sample
Global moderator
 
Posts: 3367
Joined: Sat Dec 19, 2009 8:32 pm
Location: Dubuque, Iowa, USA
Has thanked: 1 time
Been thanked: 222 times

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

Postby Akatsukami » Tue Jun 21, 2011 9:47 pm

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?
"You have sat too long for any good you have been doing lately ... Depart, I say; and let us have done with you. In the name of God, go!" -- what I say to a junior programmer at least once a day
User avatar
Akatsukami
Global moderator
 
Posts: 1053
Joined: Sat Oct 16, 2010 2:31 am
Location: Bloomington, IL
Has thanked: 6 times
Been thanked: 51 times

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

Postby GuyC » Wed Jun 22, 2011 12:06 pm

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
I can explain it to you, but i can not understand it for you.
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times

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

Postby BillyBoyo » Wed Jun 22, 2011 6:19 pm

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?
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 264 times

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

Postby Robert Sample » Wed Jun 22, 2011 6:29 pm

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.
Robert Sample
Global moderator
 
Posts: 3367
Joined: Sat Dec 19, 2009 8:32 pm
Location: Dubuque, Iowa, USA
Has thanked: 1 time
Been thanked: 222 times

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

Postby BillyBoyo » Wed Jun 22, 2011 7:01 pm

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.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 264 times

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

Postby Akatsukami » Wed Jun 22, 2011 7:11 pm

Arun didn't send me a PM; I'm devastated :mrgreen:
"You have sat too long for any good you have been doing lately ... Depart, I say; and let us have done with you. In the name of God, go!" -- what I say to a junior programmer at least once a day
User avatar
Akatsukami
Global moderator
 
Posts: 1053
Joined: Sat Oct 16, 2010 2:31 am
Location: Bloomington, IL
Has thanked: 6 times
Been thanked: 51 times

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

Postby BillyBoyo » Wed Jun 22, 2011 7:59 pm

Perhaps he took your good advice?

He didn't thank GuyC, so perhaps he sent him a nice PM?
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 264 times

Next

Return to DB2

 


  • Related topics
    Replies
    Views
    Last post