Difference between 2 timestamps in minutes?

mczern · Post by **mczern** » Fri Nov 14, 2008 11:27 am

I am attempting to get the difference betwenn 2 timestamps as a result in minutes.

IE: Timestamp1 - Timestamp2 = X minutes (Numeric).

I can do it within the Oracle SQL query, but I'd like to do it within DataStage.

chulett · Post by **chulett** » Fri Nov 14, 2008 11:41 am

Subtracting two timestamps would give you the difference in seconds, so you'd need to multiply the result by 60 and decide how to handle any fractions. What have you tried so far and what happened when you tried it?

mczern · Post by **mczern** » Fri Nov 14, 2008 11:46 am

chulett wrote:Subtracting two timestamps would give you the difference in seconds, so you'd need to multiply the result by 60 and decide how to handle any fractions. What have you tried so far and what happened when you tried it?

Maybe I'm just not declaring the result correctly... I declared it as Numeric 10.
I get a compiler error...
Error in output column derivation expression for column DIFF_DT in link src_hnamdwh2. Invalid conversion requested from a timestamp to a dfloat.

chulett · Post by **chulett** » Fri Nov 14, 2008 12:15 pm

So... you've added a new field in a transformer for the result of this calculation and made it Numeric 10. What is your derivation? What are the datatypes of the fields used?

mczern · Post by **mczern** » Fri Nov 14, 2008 12:22 pm

chulett wrote:So... you've added a new field in a transformer for the result of this calculation and made it Numeric 10. What is your derivation? What are the datatypes of the fields used?

Correct... I'm not sure if the transformer '-' can be overloaded to use 2 timestamps. The calculation I attempted was a simple subtraction of 2 timestamp defined values... Is there a function that can be used? I could not find one.

src1.END_SLOT_DT - src1.SLOT_DT

chulett · Post by **chulett** » Fri Nov 14, 2008 12:28 pm

I honestly don't know how to do timestamp math in PX. I was just hoping to lay the groundwork with some basic questions so that when the PX people who actually use the product and know what they're doing wander by, they can get right to work.

You may end up needing to break the date from the time and handle the differences separately for all I know.

ray.wurlod · Post by **ray.wurlod** » Fri Nov 14, 2008 9:44 pm

... and divide seconds by 60 to get minutes.

chulett · Post by **chulett** » Sat Nov 15, 2008 12:14 am

D'oh!