Subtract Timestamp

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
Vrisha
Premium Member
Premium Member
Posts: 60
Joined: Sat Jul 15, 2017 9:32 pm
Location: Texas,USA

Subtract Timestamp

Post by Vrisha »

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
Premium Member
Premium Member
Posts: 2895
Joined: Wed Aug 03, 2011 6:16 am
Location: USA

Post by qt_ky »

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: Select all

TimeFromMidnightSeconds(SecondsSinceFromTimestamp2(ts1, ts2))
Choose a job you love, and you will never have to work a day in your life. - Confucius
Vrisha
Premium Member
Premium Member
Posts: 60
Joined: Sat Jul 15, 2017 9:32 pm
Location: Texas,USA

Post by Vrisha »

thank you. I will try and get back to you
Suja
Vrisha
Premium Member
Premium Member
Posts: 60
Joined: Sat Jul 15, 2017 9:32 pm
Location: Texas,USA

Post by Vrisha »

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
qt_ky
Premium Member
Premium Member
Posts: 2895
Joined: Wed Aug 03, 2011 6:16 am
Location: USA

Post by qt_ky »

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
Post Reply