| Author |
Message
|
| mattfarney |
 Posted: Tue Nov 22, 2011 1:50 pm Post subject: Problems with CURRENT_TIMESTAMP |
 |
|
Disciple
Joined: 17 Jan 2006
Posts: 167
Location: Ohio
|
I'm seeing odd behavior with this value.
Sample CURRENT_TIMESTAMP
| Code: |
| 2006-02-01 13:13:56.444730 |
I strip off the first 11 characters:
| Code: |
| SUBSTRING(aTimestamp FROM 12 FOR 26) |
and add this to a BLOB that is being returned to another system for logging.
The odd behavior is that the microseconds seems to lose the last three characters if they are all zeroes. As an example:
56.475997
56.475998
56.475999
56.476
56.476001
56.476002
Has anyone else observed this? I haven't found anything detailing this issue nor does the documentation imply that this behavior is correct.
-mf |
|
| Back to top |
|
 |
| mqjeff |
| Posted: Tue Nov 22, 2011 1:53 pm Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
are you sure it's losing the zeros?
That is, have you confirmed that they exist in the string form of your timestamp before you substring? |
|
| Back to top |
|
|
| mattfarney |
| Posted: Tue Nov 22, 2011 2:07 pm Post subject: |
|
|
Disciple
Joined: 17 Jan 2006
Posts: 167
Location: Ohio
|
I don't have enough data to replicate this problem in my local environments. I am basing this on analysis of large samples of production log data (around half a million records).
I isolated the characters in question from the log strings.
I found approximately 1/1000th of the time, the record was non-numeric in that field. When I counted up the records, I found that 001-999 were all present, but not 000.
Example of log problem (sections in <> have been redacted).
| Code: |
RemoteLog.1321956943.txt:<AQueueManagerName>|I| |20111122 051817|20111122 051816|20111122 041803.352797| <ACorrelationID>|<AMessageID>|07799|<XML MessagePayload>|
RemoteLog.1321956943.txt:<AQueueManagerName>|O|AFDS1 |20111122 051817|20111122 051816|20111122 041803.430'<?| <ACorrelationID>|<AMessageID>|07797|xml version="1.0" encoding="UTF-8" ?><XMLContentFollows> |
The third timestamp is the one from MQSI (the only one with microseconds). I believe it replaced 000 with a ' only. The next 2 characters are the start of the actual xml message. The length of the log message is also 2 characters shorter.
-mf |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Tue Nov 22, 2011 2:16 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
Question:
If this is being used for logging purposes why was the timestamp (internal representation) not cast to a character representation with the corresponding format? 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| mattfarney |
| Posted: Tue Nov 22, 2011 2:22 pm Post subject: |
|
|
Disciple
Joined: 17 Jan 2006
Posts: 167
Location: Ohio
|
The logging program already deals with interpreting several date formats from MQ - so that's where architecturally I have assigned that work.
Also, I used the default format from MQSI to be as efficient as possible inside the message flows. The logging program doesn't have to be near real time, while the MQSI broker environment tries to be.
-mf |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Tue Nov 22, 2011 10:04 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
| mattfarney wrote: |
The logging program already deals with interpreting several date formats from MQ - so that's where architecturally I have assigned that work.
Also, I used the default format from MQSI to be as efficient as possible inside the message flows. The logging program doesn't have to be near real time, while the MQSI broker environment tries to be.
-mf |
I hear you. Java being my language of choice ( outside of the broker and SAP), I always try to work with a simple date format... this way I have no surprises and can easily change the time zone of the data being printed.
Have fun
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Tue Nov 22, 2011 11:45 pm Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
I've seen this 'feature' as well.
Actually, I've seen it when using the MQMD.PutTime fields as well.
On a past project (5+ years ago), I wrote a simple ESQL function that returned a CHARACTER value with a consistent number of digits in the fractions of a second part of a timestamp.
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| mattfarney |
| Posted: Fri Nov 25, 2011 10:50 am Post subject: |
|
|
Disciple
Joined: 17 Jan 2006
Posts: 167
Location: Ohio
|
In this case, it's easy enough to code around the issue, but it would be nice if this was addressed in a future release.
What's the appropriate venue to bring this up officially?
-mf |
|
| Back to top |
|
|
| mqjeff |
| Posted: Fri Nov 25, 2011 11:44 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
To be clear, the issue appears to be that it's not reporting trailing zeros when the timestamp is a round number... ?
The only real way to get this dealt with officially is through a PMR. You'll either get a patch or told to raise a requirement. |
|
| Back to top |
|
|
| mattfarney |
| Posted: Fri Nov 25, 2011 12:14 pm Post subject: |
|
|
Disciple
Joined: 17 Jan 2006
Posts: 167
Location: Ohio
|
When I looked at the results more closely after some turkey, it's not just that the trailing zeroes are missing. It looks like the microseconds part is being replaced with a single tick when the value should be 000.
There are 24 characters of my log header info. This is followed by positions 12-37 of the CURRENT_TIMESTAMP. The message follows - beginning at position 00000042.
| Code: |
00000000: 4C4F 4744 4154 4120 4D51 5349 2020 2049 'LOGDATA MQSI I'
00000010: 2020 2020 2020 2020 3230 3131 2D31 312D ' 2011-11-'
00000020: 3235 2031 353A 3036 3A34 392E 3931 3527 '25 15:06:49.915''
00000030: 4430 3335 4149 4E42 4650 5A20 2020 2020 'D035AINBFPZ '
00000040: 5468 6973 2074 7261 6E73 6163 7469 6F6E 'This transaction'
00000050: 2077 6173 2063 7265 6174 6564 206D 616E ' was created man'
00000060: 7561 6C6C 7920 7769 7468 6F75 7420 7265 'ually without re'
00000070: 616C 2064 6174 612E 'al data. |
-mf |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Fri Nov 25, 2011 2:12 pm Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
Hmmm, I think you are missing something.
when you cast a timestamp into a CHAR without a format you get something like
Timestamp '2011-11-23 15:45:67.891020'
Substringing this using a fixed start position & character count you might get this
2011-11-23 15:45:67.891020
When there are zero microseconds,
This
Timestamp '2011-11-23 15:45:67.891'
becomes
2011-11-23 15:45:67.891'
Note the trailing '
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| mattfarney |
| Posted: Fri Nov 25, 2011 2:45 pm Post subject: |
|
|
Disciple
Joined: 17 Jan 2006
Posts: 167
Location: Ohio
|
Fair enough. I'm picking up the trailing apostrophe.
Zeroes are still missing. 
At the very least, the description of CURRENT_TIMESTAMP should mention that the partial seconds are optional if 000.
-mf |
|
| Back to top |
|
|
| mqjeff |
| Posted: Sat Nov 26, 2011 12:09 pm Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| mattfarney wrote: |
| At the very least, the description of CURRENT_TIMESTAMP should mention that the partial seconds are optional if 000. |
They're not optional. CURRENT_TIMESTAMP is a TIMESTAMP value. It might happen that the partial seconds field in it is empty. That's not the same thing as being full of 000.
You can make use, as smdavies99 previously noted, of the format part of the CAST AS CHARACTER to enforce the representation of empty values as '000'.
You appear to be looking for a change in the default format pattern. |
|
| Back to top |
|
|
| mattfarney |
| Posted: Mon Nov 28, 2011 12:01 am Post subject: |
|
|
Disciple
Joined: 17 Jan 2006
Posts: 167
Location: Ohio
|
If the default format changes based on the value, I think that bears mentioning in the documentation at the very least. Especially, if this is something that even a decent amount of testing is unlikely to uncover. This format change occurs on roughly one in a thousand messages. While this is not causing me a huge production issue, it could have.
If I represented midnite January 1st of the upcoming year as
(with the minutes, seconds, and both partial seconds empty) that would certainly raise some eyebrows.
That does raise two more questions.
If there are no partial seconds at all, does the first part of the partial seconds exhibit the same behavior?
| Code: |
3:42:57.999998
3:42:57.999999
3:42:58
3:42:58.000001 |
And second (and more importantly), are there are any other circumstances where CURRENT_TIMESTAMP deviates from the default when cast without a format?
| Code: |
Timestamp '2011-11-23 15:45:67.891020'
|
-mf |
|
| Back to top |
|
|
| kevinobyrne |
| Posted: Fri Oct 19, 2012 4:34 am Post subject: |
|
|
Voyager
Joined: 17 Jul 2007
Posts: 83
Location: Ireland
|
I have been experiencing this same issue in a flow I'm using. I was casting CURRENT_TIMESTAMP as CHAR (without using FORMAT) and then later casting a substring (the 6 characters representing the milliseconds at the end of the string) as an INT.
I was getting infrequent casting errors when it would try and cast a value like 123'
This esql shows how the casting of a timestamp is inconsistent where there are zeros at the end (if you don't specify FORMAT).
| Code: |
DECLARE MyTimeStamp1 TIMESTAMP;
SET MyTimeStamp1 = TIMESTAMP '1999-12-31 23:59:59.111999';
DECLARE MyTimeStamp2 TIMESTAMP;
SET MyTimeStamp2 = TIMESTAMP '1999-12-31 23:59:59.222000';
DECLARE MyTimeStamp3 TIMESTAMP;
SET MyTimeStamp3 = TIMESTAMP '1999-12-31 23:59:59.333990';
DECLARE MyTimeStamp1C CHAR CAST(MyTimeStamp1 AS CHAR); -- MyTimeStamp1C:CHARACTER:TIMESTAMP '1999-12-31 23:59:59.111999'
DECLARE MyTimeStamp2C CHAR CAST(MyTimeStamp2 AS CHAR); -- MyTimeStamp2C:CHARACTER:TIMESTAMP '1999-12-31 23:59:59.222'
DECLARE MyTimeStamp3C CHAR CAST(MyTimeStamp3 AS CHAR); -- MyTimeStamp3C:CHARACTER:TIMESTAMP '1999-12-31 23:59:59.333990'
DECLARE MS1 CHAR SUBSTRING(MyTimeStamp1C FROM 32 FOR 6); -- MS1:CHARACTER:111999
DECLARE MS2 CHAR SUBSTRING(MyTimeStamp2C FROM 32 FOR 6); -- MS2:CHARACTER:222'
DECLARE MS3 CHAR SUBSTRING(MyTimeStamp3C FROM 32 FOR 6); -- MS3:CHARACTER:333990
DECLARE MyTimeStamp2C_Alt CHAR CAST(MyTimeStamp2 AS CHAR FORMAT 'yyyy-MM-dd hh:mm:ss.SSSSSS'); -- MyTimeStamp2C_Alt:CHARACTER:1999-12-31 11:59:59.222000
DECLARE MS2_Alt CHAR SUBSTRING(MyTimeStamp2C_Alt FROM 21 FOR 6); -- MS2_Alt:CHARACTER:222000 |
I'm making sure to explicitly use a FORMAT now but I agree with matt that the inconsistency of the behaviour of CAST is a problem.
I think it's fair to expect CAST to behave the same way 1000 times out of 1000 instead of 999 times out of 1000  |
|
| Back to top |
|
|
|
|
