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.
Seconds from timestamp
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 19
- Joined: Fri Oct 15, 2010 1:26 pm
Seconds from timestamp
suman Datastage Developer
-
- Premium Member
- Posts: 19
- Joined: Fri Oct 15, 2010 1:26 pm
Re: Seconds from timestamp
Sorry i got the answer.
Datastage is right and db2 is wrong.
Datastage is giving the correct result.
Datastage is right and db2 is wrong.
Datastage is giving the correct result.
suman Datastage Developer
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,
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,
- james wiles
All generalizations are false, including this one - Mark Twain.
All generalizations are false, including this one - Mark Twain.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Server:
Parallel:
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))
Code: Select all
SecondsSinceFromTimestamp(timestamp2,"1970-01-01 00:00:00") - SecondsSinceFromTimestamp(timestamp1,"1970-01-01 00:00:00")
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.