TIME CONVERSION

Post questions here relative to DataStage Server Edition for such areas as Server job design, DS Basic, Routines, Job Sequences, etc.

Moderators: chulett, rschirm, roy

Post Reply
sam334
Premium Member
Premium Member
Posts: 145
Joined: Mon Aug 26, 2013 7:42 pm

TIME CONVERSION

Post by sam334 »

All, Have a question on time conversion.

From DB2 DB we pulled calculated some total call time and columns are like

Tot_Time Tot.Attend
1.22 1.51

I need to have hh:mm:ss format. Is there any way we can convert it in datastage.

It should be,

00:01:22 and 00:01:51

Thanks,
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Your TOT fields are always MM.SS or can they contain an hour value as well?
-craig

"You can never have too many knives" -- Logan Nine Fingers
sam334
Premium Member
Premium Member
Posts: 145
Joined: Mon Aug 26, 2013 7:42 pm

Post by sam334 »

It is always be 1 minute 22 seconds. The way data is right now, it is not going to be hour.

I mean, it will be minute and second.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Are you looking for something... fancy? You could always use Field() to split the minutes and seconds then concat the results together. Left pad zeroes onto the short numbers.
-craig

"You can never have too many knives" -- Logan Nine Fingers
kumar_s
Charter Member
Charter Member
Posts: 5245
Joined: Thu Jun 16, 2005 11:00 pm

Post by kumar_s »

You can use StringToTime as well. With just %nn:%ss to map it to a time field.
But you ll need a padding of zero anyway.
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
qt_ky
Premium Member
Premium Member
Posts: 2895
Joined: Wed Aug 03, 2011 6:16 am
Location: USA

Post by qt_ky »

This looks like a Server job question; StringToTime is a parallel function.

So just to confirm, what does the actual input data look like for call times over an hour? That example makes a difference in your logic.
Choose a job you love, and you will never have to work a day in your life. - Confucius
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

He noted there would never be an hour component, so always minutes and seconds less than one hour is how I read that.
-craig

"You can never have too many knives" -- Logan Nine Fingers
qt_ky
Premium Member
Premium Member
Posts: 2895
Joined: Wed Aug 03, 2011 6:16 am
Location: USA

Post by qt_ky »

Yes it just leaves a little too much up to my imagination. :wink:

A really, really bad call in real life could go on for 50 hours and 30 seconds. Just curious in the data if that would look like 3000.30 or if calls > 59 minutes may be filtered out. If the latter, then the conversion logic is simpler.
Choose a job you love, and you will never have to work a day in your life. - Confucius
sam334
Premium Member
Premium Member
Posts: 145
Joined: Mon Aug 26, 2013 7:42 pm

Post by sam334 »

All,
It is a calculation of time taken to attend a call. So, there are actually all calculations in SQL query which gives the result of this. I found from january the value never crosses minutes border.

So, we believe, it will be always m.ss format.
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

I'd use a Fmt() function. Something like

Code: Select all

Fmt(DIGITS(InLink.TheValue), "R##:##")
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

sam334 wrote:So, we believe, it will be always m.ss format.
It's good to believe... better to be certain. :wink:
-craig

"You can never have too many knives" -- Logan Nine Fingers
sam334
Premium Member
Premium Member
Posts: 145
Joined: Mon Aug 26, 2013 7:42 pm

Post by sam334 »

It will be a concatenation of Certain and Believe :)

I used the FMT code above. The output is 00:00. The input column 1.15 and 1.22 are varchar(1) and varchar(3). And the output data type is same. Do i need to change the output data type to time or anything..

Thanks.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Varchar 1 & 3? How does that work? Seems to me your fields would need to be Varchar 5 to accomodate a full value - double digits for both plus the delimiter. And the target would need to be 8. :?

An alternative to Ray's fancy FMT:

Code: Select all

'00:' : FMT(FIELD(InLink.TheValue,'.',1),'2"0"R')) : ':' : FMT(FIELD(InLink.TheValue,'.',2),'2"0"R'))
All off the top of my head so YMMV. :wink:
sam334 wrote:Do i need to change the output data type to time or anything
You tell us... what is your actual target? I've been assuming a string.
-craig

"You can never have too many knives" -- Logan Nine Fingers
Post Reply