DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
This topic has been marked "Resolved."
Author Message
Vrisha



Group memberships:
Premium Members

Joined: 15 Jul 2017
Posts: 55
Location: Texas,USA
Points: 650

Post Posted: Thu Sep 14, 2017 1:09 pm Reply with quote    Back to top    

DataStage® Release: 11x
Job Type: Parallel
OS: Windows
I have a requirement to subtract two timestamps and store the value in output field

Output
Process_duration - Varchar(20)

Formula-
CurrentTimestamp()-DsJobStartTimestamp

CurrentTimestamp() - 2017-09-14 08:37:59
DsJobStartTimestamp=2017-09-14 08:37:42

Output should be 00:00:17

I tried this option
TimeToString (TimestampToTime(CurrentTimestamp())-TimestampToTime(DSJobStartTimestamp),"%hh:%nn:%ss") ===> but compilation is throwing error of '. Invalid conversion requested from a dfloat to a time'

What is the mistake I am doing? Please let me know.

_________________
Suja
qt_ky



Group memberships:
Premium Members

Joined: 03 Aug 2011
Posts: 2653
Location: USA
Points: 19683

Post Posted: Thu Sep 14, 2017 1:59 pm Reply with quote    Back to top    

Have you tried using the SecondsSinceFromTimestamp2 function?

SecondsSinceFromTimestamp2 returns the number of seconds between two time stamp objects.

You might try taking the output from the above and feeding it into the TimeFromMidnightSeconds function.

Code:
TimeFromMidnightSeconds(SecondsSinceFromTimestamp2(ts1, ts2))

_________________
Choose a job you love, and you will never have to work a day in your life. - Confucius
Rate this response:  
Not yet rated
Vrisha



Group memberships:
Premium Members

Joined: 15 Jul 2017
Posts: 55
Location: Texas,USA
Points: 650

Post Posted: Thu Sep 14, 2017 2:40 pm Reply with quote    Back to top    

thank you. I will try and get back to you

_________________
Suja
Rate this response:  
Not yet rated
Vrisha



Group memberships:
Premium Members

Joined: 15 Jul 2017
Posts: 55
Location: Texas,USA
Points: 650

Post Posted: Fri Sep 15, 2017 6:57 am Reply with quote    Back to top    

The problem got resolved.

As you said, I tried

TimeFromMidnightSeconds(SecondsSinceFromTimestamp2(CurrentTimestamp(),DSJobStartTimestamp)) = PROCESS_DURATION in transformer

Input -

9/15/2017 7:49:57 AM---DSJobStartTimestamp
9/15/2017 7:50:09 AM--CurrentTimestamp()


Output is 00:00:12

Thank you so much, qt.

_________________
Suja
Rate this response:  
Not yet rated
qt_ky



Group memberships:
Premium Members

Joined: 03 Aug 2011
Posts: 2653
Location: USA
Points: 19683

Post Posted: Fri Sep 15, 2017 8:59 am Reply with quote    Back to top    

Excellent! Good way to start the weekend...

_________________
Choose a job you love, and you will never have to work a day in your life. - Confucius
Rate this response:  
Not yet rated
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours