Seconds from timestamp

[SUMAN CHILUVURI]

I need seconds from timestamp i am using the function

SecondsSinceFromTimestamp(svCSOUTTSA, svCSINVTSA) but i am not getting the currect result.

when the difference between the two is very high

ie : 2011-07-01 15:27:00 --- 2011-08-23 17:41:00 ---- 4587240 i am getting

4500840 when i use the db2 function (timestampdiff(2,truck_leave_tsp - invoice_print_tsp)) i get this result which is correct .

If the two dates are in same month i am getting the currect result when i use the datastage function.

is there any function which i can use to get the currect result in datastage.

[SUMAN CHILUVURI]

Sorry i got the answer.


Datastage is right and db2 is wrong.

Datastage is giving the correct result.

[jwiles]

The two functions are different in execution and can give different answers when crossing month boundaries.

The results you are seeing from the DataStage function SecondsSinceFromTimestamp() are correct for the timestamps you provided. I performed a manual calculation and received the same results.

DB2's timestampdiff() is an approximation based on 30-day months. Therefore, the value it returns is not guaranteed to be the actual value when crossing month boundaries. The results you give indicate a 1-day discrepancy in the result (86,400 seconds = 24 hours = 1 day), which is expected as July has 31 days instead of 30.

There is no function built-in to the parallel canvas to replicate the DB2 timestampdiff() function. Someone with more Server experience could let us know if there's an equivalent function/transform in Server. Worst-case is that you need to code the logic yourself. See <a href="http://www.ibm.com/developerworks/data/ ... html">this article</a> in IBM Developerworks for more information.

Regards,

[ray.wurlod]

Server:

Code: Select all

(Oconv(Field(timestamp2," ",1,1),"DYMD") * 86400 + Oconv(Field(timestamp2," ",2,1),"MT")) - (OConv(Field(timestamp1," ",1,1),"DYMD") * 86400 + Oconv(Field(timestamp1," ",2,1),"MT))

Parallel:

Code: Select all

SecondsSinceFromTimestamp(timestamp2,"1970-01-01 00:00:00") - SecondsSinceFromTimestamp(timestamp1,"1970-01-01 00:00:00")