Microseconds extraction

vivekreddy · Post by **vivekreddy** » Tue Jan 16, 2007 1:54 am

Hi.

I have a BIGINT field of length 18 and containing data in format YYMMDDHHNNSSssssss, basically timestamp.
I need to split this field into two fields, one a date field YY-MM-DD, and time field HH:NN:SS.ssssss.
I am able to generate the date part properly by first converting the number to character using stage variable and then using the StringToDate method, but am unable to generate the time part with the microseconds using the StringToTime method.
To say more clearly, the time field generated is of format HH:NN:SS, and does not display the microseconds portion.

What should I do?

kumar_s · Post by **kumar_s** » Tue Jan 16, 2007 2:02 am

What is the syntax you use to convert? Try %HH%NN%SS.xxxxxx.
Alternatively you can convert it into microseconds, and can use time_from_microseconds() function.

vivekreddy · Post by **vivekreddy** » Tue Jan 16, 2007 2:54 am

kumar_s wrote:What is the syntax you use to convert? Try %HH%NN%SS.xxxxxx.
Alternatively you can convert it into microseconds, and can use time_from_microseconds() function.

I use the syntax for conversion as follows. This step is preceded by converting the BIGINT field value to character value.
StringToTime(((svToCharAccessID)[7,2]:":":(svToCharAccessID)[9,2]:":":((svToCharAccessID)[11,2]:".":(svToCharAccessID)[13,6])),"%hh:%nn:%ss.xxxxxx")

This isn't working. Also could u explain in a little more detail as to how I can use the time_from_microseconds() function?

kumar_s · Post by **kumar_s** » Tue Jan 16, 2007 5:03 am

You dont need to concatenate with ":" and then mention the format as %HH:%MI... You can straight away give %hh%nn%ss.xxxxx.
The other method is convert all hh, nn, ss that you found using svToCharAccessID)[7,2]:":":(svToCharAccessID)[9,2],Sad(svToCharAccessID)[11,2],(svToCharAccessID)[13,6] to microsecond and then convert it back into time using the function.

vivekreddy · Post by **vivekreddy** » Tue Jan 16, 2007 5:36 am

kumar_s wrote:You dont need to concatenate with ":" and then mention the format as %HH:%MI... You can straight away give %hh%nn%ss.xxxxx.
The other method is convert all hh, nn, ss that you found using svToCharAccessID)[7,2]:":":(svToCharAccessID)[9,2],Sad(svToCharAccessID)[11,2],(svToCharAccessID)[13,6] to microsecond and then convert it back into time using the function.

I am unable to understand how I can convert the string to Microsecond. Also, I have tried the first method that you suggested viz. explicitly giving the desired format without concatenation of special characters, and it isn't working.

Any other suggestions that I could try out?

kumar_s · Post by **kumar_s** » Tue Jan 16, 2007 5:40 am

Try mentioning the extension of micro seconds in the datatype of that field using field properties.
Converting seconds into micro second is by multiplying by 1000000 and adding with the microsecond part of the exisiting values. Converting minute into micro second would be 60*1000000 and so on...

vivekreddy · Post by **vivekreddy** » Tue Jan 16, 2007 5:52 am

kumar_s wrote:Try mentioning the extension of micro seconds in the datatype of that field using field properties.
Converting seconds into micro second is by multiplying by 1000000 and adding with the microsecond part of the exisiting values. Converting minute into micro second would be 60*1000000 and so on...

Although I have understood the part of conversion to microseconds, I don't understand how to convert the said thing back to the desired format, or rather to time, considering that I am unable to find the time_from_microseconds() function. I can only find the microseconds_from_time() function.

kumar_s · Post by **kumar_s** » Tue Jan 16, 2007 4:46 pm

Iam sorry I dont know where I got this from

.
You should be looking at the format of the data type of that field with the other method.

vivekreddy · Post by **vivekreddy** » Tue Jan 16, 2007 10:35 pm

kumar_s wrote:Iam sorry I dont know where I got this from .
You should be looking at the format of the data type of that field with the other method.

Really no problem. You must be working with DataStage so much that you must have forgotten more than what you can remember. At least that way, I was compelled to go though the entire developer's guide to find out all and any information about microseconds conversion/extraction, which I found to be a educative, if not entirely rewarding experience.
The argument passed to the MicrosecondsFromTime() method is of data type Time, and it returns an int32 value as the microseconds. However, since the Time field itself is not returning the microseconds portion, I doubt that this would be of much help.

Could you suggest some other thing?

kumar_s · Post by **kumar_s** » Wed Jan 17, 2007 4:51 pm

I dont have access to PX now. I am expecting that there should be a option if you tweak the data type defaults of that field, which I already said.
BTW: Why do you need to convert it into Time data type, why can you keep as varchar as is.
Since many version including some earliar version of database use as Date and time.

vivekreddy · Post by **vivekreddy** » Thu Jan 18, 2007 12:12 am

kumar_s wrote:I dont have access to PX now. I am expecting that there should be a option if you tweak the data type defaults of that field, which I already said.
BTW: Why do you need to convert it into Time data type, why can you keep as varchar as is.
Since many version including some earliar version of database use as Date and time.

System requirements dictate the conversion. Can't really help it.