| Author |
Message
|
| madi |
 Posted: Thu Mar 30, 2006 8:01 am Post subject: current time |
 |
|
Chevalier
Joined: 17 Jan 2006
Posts: 475
|
Hi All
I am trying to create an ID out of the current date and time. this is the porcedure I use for that:
| Code: |
CREATE PROCEDURE CreateBatchRefId(
OUT batchRefId CHARACTER)
BEGIN
-- Code to create the batchRefId
DECLARE tyear CHAR CAST(EXTRACT(YEAR FROM CURRENT_DATE) AS CHAR);
DECLARE tmonth CHAR CAST(EXTRACT(MONTH FROM CURRENT_DATE) AS CHAR);
WHILE LENGTH(tmonth) < 2 DO
SET tmonth = '0' || tmonth;
END WHILE;
DECLARE tday CHAR CAST(EXTRACT(DAY FROM CURRENT_DATE) AS CHAR);
WHILE LENGTH(tday) < 2 DO
SET tday = '0' || tday;
END WHILE;
DECLARE thour CHAR CAST(EXTRACT(HOUR FROM CURRENT_TIME) AS CHAR);
WHILE LENGTH(thour) < 2 DO
SET thour = '0' || thour;
END WHILE;
DECLARE tmin CHAR CAST(EXTRACT(MINUTE FROM CURRENT_TIME) AS CHAR);
WHILE LENGTH(tmin) < 2 DO
SET tmin = '0' || tmin;
END WHILE;
DECLARE tsec INT CAST(EXTRACT(SECOND FROM CURRENT_TIME) AS INT);
DECLARE tcsec CHAR CAST(tsec AS CHAR);
WHILE LENGTH(tcsec) < 2 DO
SET tcsec = '0' || tcsec;
END WHILE;
DECLARE tmsec CHAR SUBSTRING(CAST(CURRENT_TIMESTAMP AS CHAR) FROM (LENGTH(CAST(CURRENT_TIMESTAMP AS CHAR)) - 6) FOR 3);
SET batchRefId = 'MB' || tyear || tmonth || tday|| thour|| tmin|| tcsec || tmsec;
END;
|
So I am calling this function many times within a compute module and I get the same batchRefId everytime when I expect to get different Id becoz the time is gonna be different.
So my conclusion on this would be the compute node is getting the current time and beginning of the process and not everytime we are calling them.
how can i get the real current time?
--madi |
|
| Back to top |
|
 |
| jefflowrey |
| Posted: Thu Mar 30, 2006 8:21 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
Well, there are still UUIDASCHAR and UUIDASBLOB, so you can use those instead of a timestamp as a unique identifier.
Also, I think you can get better time resolution from Java.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| wschutz |
| Posted: Thu Mar 30, 2006 9:05 am Post subject: |
|
|
Jedi Knight
Joined: 02 Jun 2005
Posts: 3316
Location: IBM (retired)
|
| Quote: |
CURRENT_TIME returns a TIME value representing the current local time. As with all SQL functions that take no parameters, no parentheses are required or accepted. All calls to CURRENT_TIME within the processing of one node are guaranteed to return the same value.
|
_________________
-wayne |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Thu Mar 30, 2006 9:15 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
It is possible to get more granularity from Java.
Here's an evaluate function for a JavaCompute node that adds two different timestamps to the local environment.
public void evaluate(MbMessageAssembly assembly) throws MbException {
MbOutputTerminal out = getOutputTerminal("out");
MbOutputTerminal alt = getOutputTerminal("alternate");
// ----------------------------------------------------------
// Add user code below
MbMessage env = assembly.getLocalEnvironment();
MbMessage newEnv = new MbMessage(env);
MbElement oleRoot = newEnv.getRootElement();
SimpleDateFormat formatter;
formatter = new SimpleDateFormat("EEE MMM dd yyyy hh:mm:ss.SSS z", Locale
.getDefault());
Date today = new Date();
oleRoot.createElementAsLastChild(MbElement.TYPE_NAME_VALUE,"currentTime1", formatter.format(today));
today = new Date();
oleRoot.createElementAsLastChild(MbElement.TYPE_NAME_VALUE,"currentTime2",formatter.format(today));
MbMessageAssembly outAssembly = new MbMessageAssembly(
assembly,
newEnv,
assembly.getExceptionList(),
assembly.getMessage());
// End of user code
// ----------------------------------------------------------
// The following should only be changed
// if not propagating message to the 'out' terminal
out.propagate(outAssembly);
}
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| jbanoop |
| Posted: Sat Apr 01, 2006 12:39 am Post subject: |
|
|
Chevalier
Joined: 17 Sep 2005
Posts: 401
Location: SC
|
Hi,
If you want to get the exact timestamps at different points in an ESQL compute module, you could have a java method called from ESQL (refer calling external java method from ESQL) when ever you need the current timestamp.
We have done it that way to get exact time stamp from ESQL.
Regards,
Anoop |
|
| Back to top |
|
|
| shalabh1976 |
| Posted: Tue Apr 04, 2006 9:53 pm Post subject: |
|
|
Partisan
Joined: 18 Jul 2002
Posts: 381
Location: Gurgaon, India
|
If you want to avoid Java altogether you can try accessing the timestamp functions available with various databases. Running a select clause on those will give you the desired values. This may have a performance issue compared to other methods mentioned in the post.
_________________
Shalabh
IBM Cert. WMB V6.0
IBM Cert. MQ V5.3 App. Prog.
IBM Cert. DB2 9 DB Associate |
|
| Back to top |
|
|
| jbanoop |
| Posted: Wed Apr 05, 2006 6:10 am Post subject: |
|
|
Chevalier
Joined: 17 Sep 2005
Posts: 401
Location: SC
|
| And if the DB is on another machine you may end up with a dfferent time which may lead to inconsistencies. |
|
| Back to top |
|
|
|
|
