| Author |
Message
|
| Vgowda |
 Posted: Mon Apr 20, 2009 11:49 pm Post subject: Difference between Timestamp |
 |
|
Acolyte
Joined: 11 Dec 2007
Posts: 61
Location: Bengaluru
|
Hi,
I am trying to find difference between 2 timestamps but the difference is not assigned to the interval which i have declared. Below is my code
DECLARE V2,V3 TIMESTAMP;
DECLARE V1 INTERVAL;
SET V2 = CURRENT_TIMESTAMP;
SET V3 = CAST('2009-04-23 11:14:48.946' AS TIMESTAMP);
SET V1 = (V3 - V2) DAY TO HOUR;
The value of V1 is not set.
My questions are
1) Cant we find the difference between timestamp?
2) Is there any error in the above code?
Please do help  . Thanks in advance
_________________
Regards
Vinay |
|
| Back to top |
|
 |
| Vitor |
| Posted: Mon Apr 20, 2009 11:59 pm Post subject: Re: Difference between Timestamp |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| Vgowda wrote: |
| 1) Cant we find the difference between timestamp? |
Yes you can, as shown here
| Vgowda wrote: |
| 2) Is there any error in the above code? |
Not an obvious one, but it may not be doing what you expect. What result are you expecting? Specifically, what are you expecting to happen if the timestamps are less than an hour apart?
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| Vgowda |
| Posted: Tue Apr 21, 2009 12:23 am Post subject: |
|
|
Acolyte
Joined: 11 Dec 2007
Posts: 61
Location: Bengaluru
|
Actually i saw that url earlier only, But they are doing for Current_Time not for Timestamp. I checked in IBM documentation for MB it as given for Date and Time but not for Timestamp.
i need to get the difference and if the difference is more den 24 hours / 1440 minutes i need to do some operation for that record. For checking what value it returns as of now i have hardcoded those values.
But no values as been assigned to that 
_________________
Regards
Vinay |
|
| Back to top |
|
|
| Vitor |
| Posted: Tue Apr 21, 2009 12:26 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| Vgowda wrote: |
| i need to get the difference and if the difference is more den 24 hours / 1440 minutes i need to do some operation for that record. |
Fair point but doesn't answer my question about what you expect to happen if the difference in timestamps is outside the range.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| Vgowda |
| Posted: Tue Apr 21, 2009 12:38 am Post subject: |
|
|
Acolyte
Joined: 11 Dec 2007
Posts: 61
Location: Bengaluru
|
Whatever the hardcoded value will be retrieved from database. If the record is 2 days earlier compare to Current_Timestamp that record as to be deleted. This logic i have to incorporate, For that i have to check what value is given if timestamp are subtracted but the value is not assigned for that .
I think now ur point is cleared rite. 
Is there any other alternate/better way to do the same??
_________________
Regards
Vinay |
|
| Back to top |
|
|
| WMBDEV1 |
| Posted: Tue Apr 21, 2009 1:23 am Post subject: |
|
|
Sentinel
Joined: 05 Mar 2009
Posts: 888
Location: UK
|
| Vgowda wrote: |
Is there any other alternate/better way to do the same?? |
Could you perform this query in the database (at the time of retrieval)? Would that be a viable alternative?
It may save some work being done in the broker. |
|
| Back to top |
|
|
| Vitor |
| Posted: Tue Apr 21, 2009 1:25 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| Vgowda wrote: |
| Is there any other alternate/better way to do the same?? |
Well I wouldn't have done it with a flow, but then I don't know what other processing is associated with it. It's all about your requirements.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| Vgowda |
| Posted: Tue Apr 21, 2009 2:07 am Post subject: |
|
|
Acolyte
Joined: 11 Dec 2007
Posts: 61
Location: Bengaluru
|
Ok....according to u guys to is better to run a query instead of doing in a flow?
the requirement is a flow, which for every 60 seconds record must be deleted if it is older then 24 hours of current timestamp thats it.
_________________
Regards
Vinay |
|
| Back to top |
|
|
| Vitor |
| Posted: Tue Apr 21, 2009 2:17 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
"Better" is a fuzzy concept.
If all that's happening is that you're deleting rows in a database, I'm unclear what value a flow would add to the process that a query does not, which would make the flow "better".
This is no way alters that fact you could do it with a flow. In the same way you could do it with a custom app.
Again, depends on your specific requirements and conditions.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| Vgowda |
| Posted: Tue Apr 21, 2009 2:23 am Post subject: |
|
|
Acolyte
Joined: 11 Dec 2007
Posts: 61
Location: Bengaluru
|
Thats fine, leave about requirement and conditions. Tell me why the Value(V1) of interval is not assigned in the ESQL. Instead of other ways tell me in ESQL what can be done.
_________________
Regards
Vinay |
|
| Back to top |
|
|
| Vitor |
| Posted: Tue Apr 21, 2009 2:29 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| Vgowda wrote: |
| Tell me why the Value(V1) of interval is not assigned in the ESQL. |
I did. 
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| nab054371 |
| Posted: Tue Apr 21, 2009 3:45 am Post subject: |
|
|
Disciple
Joined: 15 Nov 2006
Posts: 173
|
Hi Vinay-
Please use passthru SQL statement to DB2/Oracle database and pass current_timestamp and V2 timestamp as parameters and let the DB compute this information.
thanks |
|
| Back to top |
|
|
| mgk |
| Posted: Tue Apr 21, 2009 4:38 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
Firstly, this is supported by WMB as documented in topic ac05950, and it works fine. A DB is NOT needed to perform this operation, and using PASSTHRU for this is unnecessary
Secondly, you said "The value of V1 is not set". How do you know this? When I tried your code as it, outputing the data to a message it worked first time, so you code is correct. There must be some other reason why you do not see V1 populated...
For reference here is my code:
| Code: |
DECLARE V2,V3 TIMESTAMP;
DECLARE V1 INTERVAL;
SET V2 = CURRENT_TIMESTAMP;
SET V3 = CAST('2009-04-23 11:14:48.946' AS TIMESTAMP);
SET OutputRoot.XMLNS.Top.Before.V1 = V1;
SET OutputRoot.XMLNS.Top.Before.V2 = V2;
SET OutputRoot.XMLNS.Top.Before.V3 = V3;
SET V1 = (V3 - V2) DAY TO HOUR;
SET OutputRoot.XMLNS.Top.After.V1 = V1; |
The output I see from this is (formatted for clarity):
| Code: |
<Top>
<Before>
<V2>2009-04-21 13:28:09.373266</V2>
<V3>2009-04-23 11:14:48.946</V3>
</Before>
<After>
<V1>INTERVAL '1 22' DAY TO HOUR</V1>
</After>
</Top> |
Kind Regards,
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions.
Last edited by mgk on Tue Apr 21, 2009 9:24 am; edited 1 time in total |
|
| Back to top |
|
|
| Vgowda |
| Posted: Tue Apr 21, 2009 8:06 am Post subject: |
|
|
Acolyte
Joined: 11 Dec 2007
Posts: 61
Location: Bengaluru
|
Hey nab054371,
Thank you dude, it can be done in simple way by using Passthrough statement like this
PASSTHRU('DELETE FROM TEST A
WHERE A.DATETIME < (CURRENT_TIMESTAMP-1 DAY)'
TO Database.USERDB);
_________________
Regards
Vinay |
|
| Back to top |
|
|
| Vitor |
| Posted: Tue Apr 21, 2009 10:24 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| Vgowda wrote: |
| it can be done in simple way by using Passthrough statement |
So you decided to do it with a query rather than a flow after all then? 
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
|
|
