Date time stamp difference in time stamp

[djdinjan]

Hi All,

I am using Datastage 7.5 and i have to achieve the following in a server job.
I have two timestamps for example '2006-09-10 03:10:35' and '2006-09-10 03:15:10'. I would like to get the difference between the two timestamps in the following format.

'0000-00-00 00:05:25'

Could someone please suggest how i can achieve this.

Thanks a lot,
Dinesh

[ray.wurlod]

We need more in the way of specification. It is unusual to seek to display an interval as a timestamp; it is more usual to use hh:mm:ss with hh being permitted to exceed 24. For instance, what is your rule when the difference is more than 24 hours - how do you wish your output to appear?

The essence of the technique is to convert both timestamps to "seconds since midnight on day zero" using Iconv(), effect the subtraction, then apply Oconv() to generate the required output format.

[djdinjan]

delete

[djdinjan]

Hi Ray,

What i am trying to do here is actually get the difference between the job start time and job end time.
None of the jobs which i have would run for more than 24 hrs thus causing an issue which you have mentioned.

The approach which i am taking in order to get the time elapsed is
by subtracting the DSJobStartTimeStamp passed from the sequencer from the DSJobStartTimeStamp of the server job. But after subtracting them i would like to get the values in the hh:mi:ss format.

I would basically need to capture the total run time of the job in the timestamp format.

I have tried few functions like DSJobGetInfo, DateDiff etc. but none helped.

I shall surely try the Iconv and Oconv method which you have mentioned and let you know.

Please suggest me if there any other better ways to achieve this.

Thank You,
Dinesh

[Kirtikumar]

If you have to do it using basic code / routine you can use
DSGetJobInfo(Handle, DSJ.JOBELAPSED).

It will return the total time taken by the job. If you want it in some format you can use Oconv function as OConv(JonTimeInSec, 'MTS').

The aforesaid function will give you the details for last run. It will be exactly same as the column Elapsed time shown in the DS Director.[/i]

[DSguru2B]

I wrote a script for a friend of mine a while back to get the job elapsed time for all the jobs from a text file. The result is a comma delimitted file with job name and its elapsed time. Its fairly simple. If you are familiar with unix you will be able to figure it out. All you need to do is change the directory paths and the dsenv file location.

Code: Select all

#!/usr/bin/ksh 
SET_ENV() 
{ 
if [ -f /ibmdscode/Ascential/DataStage/DSEngine/dsenv ] 
        then 
                . /ibmdscode/Ascential/DataStage/DSEngine/dsenv 

                if [ $? -ne 0 ] 
                then 
                        echo "/ibmdscode/Ascential/DataStage/DSEngine/dsenv FAILED ...... " 
                        exit 2 
                fi 
        else 
                echo "Can't find /ibmdscode/Ascential/DataStage/DSEngine/dsenv ...... " 
                exit 2 
        fi 
} 
SET_FILE_PATH() 
{ 
export JOBLIST=/cgiproducer_dw/Daily/SHL/JobName.txt 
export DSRPT=/ibmdscode/Ascential/DataStage/DSEngine/bin/dsjob 
export DSPROJ=CGIProducer_DW 
export LOGRPT1=/cgiproducer_dw/Daily/Temp/ 
} 
LOG_FILE() 
{ 
cat ${JOBLIST} | while read job_name 

do 
$DSRPT -report $DSPROJ $job_name | grep 'Job elapsed time' | read status 
echo $job_name','$status >> ${LOGRPT1}OneFile.txt 

done 
} 

SET_ENV 
SET_FILE_PATH 
LOG_FILE 

[killbill1]

Kirti, When i had tried DSGetJobInfo(DSJ.ME,DSJ.JOBELAPSED) the value returned was 0. Even though the job's duration was around 4-5 minutes.

Do you think we have missed any thing.

Thanks,

[chulett]

Does this post apply?

[killbill1]

Hi Chulett,

The post you have suggested says that when the job ran over mid night the function had returned 0 value. But here we are running the job within one day. The job finishes in few minutes and the function returns 0 value.

Thanks for pointing us to that post.

We have tried several times but the function is some how resulting incorrect values.

Suggestions are always welcome.

Thanks,

[chulett]

Just wanted to check to see if your case was the 'crossing midnight' case. I'd suggest you take this to your support provider, see what they have to say... could be a bug in your version.

Or it could just be you are using it in the wrong place. Based of the reference to DSJ.ME, I'm guessing you are using it in an 'after job' routine? If that's the case you will always get a zero as the job hasn't actually finished running yet. You need to check in a separate step, like the next stage down in a Sequence job, for instance.

[djdinjan]

Hi all,

I actually will have to calculate the job end time and the job duration with in the server job and load it to the target table.

I have tried to achieve it in the following manner.

1) Passed DSJobStartTimeStamp from the sequencer to the server job.
2) Used the GetCurrentDateTime routine in the server job, i have tried to get the GetCurrentDateTime after the last record from the source has been selected.
3) The following code has been written to get the difference of the dates.

Code: Select all

IConv(Field(GetCurrentDateTime('x'),' ',2),'MTS') - IConv(Field(JobStartDttm,' ',2),'MTS')

-- assigned this to a variable.
(The above code is used to get only the time part of the timestamp)

Then performed an Oconv of the above value, to achieve the value in MTS format. HH:MI:SS.

Thanks to Ray, Your suggestion has worked.

There is like 1 or 2 seconds difference between the time duration generated in the above manner and the actual job run time. But that is fine.

Thanks a lot to everyone once again.

-Dinesh