Date time stamp difference in time stamp
Moderators: chulett, rschirm, roy
Date time stamp difference in time stamp
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
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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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
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
-
- Participant
- Posts: 437
- Joined: Fri Oct 15, 2004 6:13 am
- Location: Pune, India
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]
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]
Regards,
S. Kirtikumar.
S. Kirtikumar.
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
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
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,
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,
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.
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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. -- 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
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')
(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