| Author |
Message
|
| rekarm01 |
 Posted: Tue Oct 28, 2008 6:07 pm Post subject: WMB 6.0.0.5: DST problem when casting CHARACTER to TIMESTAMP |
 |
|
Grand Master
Joined: 25 Jun 2008
Posts: 1415
|
We are trying to convert a CHARACTER string representing the local time (format 'yyyyMMddHHmm') to a TIMESTAMP, and the resulting CASTs do not work as expected for dates on or after Oct. 26.
The broker version is WMB 6.0.0.5 (on AIX 5.3.0.0).
The broker timezone is "PST8PDT", as reported by "mqsiservice -t".
Here is some sample ESQL to illustrate the problem:
| Code: |
DECLARE ts TIMESTAMP;
SET OutputRoot = InputRoot;
SET ts = CAST(InputRoot.XMLNSC.Data.Timestamp.Input AS TIMESTAMP FORMAT 'yyyyMMddHHmm');
SET OutputRoot.XMLNSC.Data.Timestamp.Local = CAST(ts AS CHARACTER);
SET OutputRoot.XMLNSC.Data.Timestamp.GMT = CAST(CAST(ts as GMTTIMESTAMP) AS CHARACTER); |
For dates before Oct. 26, the CASTs work as expected:
| Code: |
<Timestamp>
<Input>200810011200</Input>
<Local>TIMESTAMP '2008-10-01 12:00:01.752627'</Local>
<GMT>GMTTIMESTAMP '2008-10-01 19:00:01.752627'</GMT>
</Timestamp>
<Timestamp>
<Input>200810251200</Input>
<Local>TIMESTAMP '2008-10-25 12:00:43.277775'</Local>
<GMT>GMTTIMESTAMP '2008-10-25 19:00:43.277775'</GMT>
</Timestamp> |
For dates between Oct. 26 and Nov. 1, the local TIMESTAMP is off by +1 hour; the interval between local timezone and GMT is correct:
| Code: |
<Timestamp>
<Input>200810261200</Input>
<Local>TIMESTAMP '2008-10-26 13:00:47.675915'</Local>
<GMT>GMTTIMESTAMP '2008-10-26 20:00:47.675915'</GMT>
</Timestamp>
<Timestamp>
<Input>200810281200</Input>
<Local>TIMESTAMP '2008-10-28 13:00:17.750379'</Local>
<GMT>GMTTIMESTAMP '2008-10-28 20:00:17.750379'</GMT>
</Timestamp>
<Timestamp>
<Input>200811011200</Input>
<Local>TIMESTAMP '2008-11-01 13:00:05.166753'</Local>
<GMT>GMTTIMESTAMP '2008-11-01 20:00:05.166753'</GMT>
</Timestamp> |
For dates after Nov. 1, local TIMESTAMP is off by +1 hour; the interval between local timezone and GMT is off by -1 hour:
| Code: |
<Timestamp>
<Input>200811021200</Input>
<Local>TIMESTAMP '2008-11-02 13:00:29.177216'</Local>
<GMT>GMTTIMESTAMP '2008-11-02 20:00:29.177216'</GMT>
</Timestamp>
<Timestamp>
<Input>200812011200</Input>
<Local>TIMESTAMP '2008-12-01 13:00:08.662185'</Local>
<GMT>GMTTIMESTAMP '2008-12-01 20:00:08.662185'</GMT>
</Timestamp> |
Additionally, the seconds and fractional seconds are not specified in the Input string, nor the Format string. They seem to be populated from the current time; that's also unexpected. |
|
| Back to top |
|
 |
| fjb_saper |
| Posted: Tue Oct 28, 2008 8:44 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
Looks like may be your OS and java have different ideas as to when DST ends? I'd say hardly a broker problem... Have you tried to check the DST box on the message set? 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| rekarm01 |
| Posted: Tue Oct 28, 2008 9:41 pm Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 1415
|
| fjb_saber wrote: |
| Looks like may be your OS and java have different ideas as to when DST ends? I'd say hardly a broker problem... Have you tried to check the DST box on the message set? |
My OS seems to know that DST hasn't ended yet:
| Code: |
$ TZ=PST8PDT /bin/date
Tue Oct 28 22:23:59 PDT 2008
$ TZ=GMT /bin/date
Wed Oct 29 05:24:12 GMT 2008 |
My java comes bundled with my broker:
| Code: |
$ type java
java is /opt/IBM/mqsi/6.0/jre/bin/java |
XMLNSC does not come with a DST box, or a message set.
I haven't ruled out "broker problem" yet. |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Wed Oct 29, 2008 6:07 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
Did you ever upgrade the java part for the new DST regulations? Your java might think DST ended last Sunday...
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| rekarm01 |
| Posted: Wed Oct 29, 2008 9:42 pm Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 1415
|
| fjb_saper wrote: |
| Did you ever upgrade the java part for the new DST regulations? Your java might think DST ended last Sunday... |
We applied all the recommended patches that IBM told us to apply, early last year. I'm told that the new DST regulations are accounted for in both AIX 5.3 and WMB 6.0.0.5.
I wrote a little test app using the broker JRE; it seems to know when DST ends:
| Code: |
public class TestDST
{
public static void main(String[] args) throws java.text.ParseException
{
for (int i=0; i < args.length; i++)
System.out.println(new java.util.Date(args[i]));
}
}
// Output:
Sat Oct 25 12:00:00 PDT 2008
Sun Oct 26 12:00:00 PDT 2008
Mon Oct 27 12:00:00 PDT 2008
Tue Oct 28 12:00:00 PDT 2008
Wed Oct 29 12:00:00 PDT 2008
Thu Oct 30 12:00:00 PDT 2008
Fri Oct 31 12:00:00 PDT 2008
Sat Nov 01 12:00:00 PDT 2008
Sun Nov 02 12:00:00 PST 2008
Mon Nov 03 12:00:00 PST 2008 |
The ESQL current date/time functions seem to work correctly:
| Code: |
// Output
<Current>
<LOCAL_TIMEZONE>INTERVAL '-420' MINUTE</LOCAL_TIMEZONE>
<CURRENT_DATE>DATE '2008-10-29'</CURRENT_DATE>
<CURRENT_TIME>TIME '20:13:35.976118'</CURRENT_TIME>
<CURRENT_TIMESTAMP>TIMESTAMP '2008-10-29 20:13:35.976118'</CURRENT_TIMESTAMP>
<CURRENT_GMTDATE>DATE '2008-10-30'</CURRENT_GMTDATE>
<CURRENT_GMTTIME>GMTTIME '03:13:35.976118'</CURRENT_GMTTIME>
<CURRENT_GMTTIMESTAMP>GMTTIMESTAMP '2008-10-30 03:13:35.976118'</CURRENT_GMTTIMESTAMP>
</Current> |
The only thing that doesn't seem to work is CASTing a CHARACTER string AS a TIMESTAMP.
| Code: |
DECLARE ts TIMESTAMP;
SET OutputRoot = InputRoot;
SET ts = CAST(InputRoot.XMLNSC.Timestamp.Input AS TIMESTAMP FORMAT 'yyyyMMddHHmm');
SET OutputRoot.XMLNSC.Timestamp.ToLocal = CAST(ts AS CHARACTER);
SET OutputRoot.XMLNSC.Timestamp.ToGMT = CAST(CAST(ts as GMTTIMESTAMP) AS CHARACTER);
// Output:
<Timestamp>
<Input>200810011200</Input>
<ToLocal>TIMESTAMP '2008-10-01 12:00:35.976118'</ToLocal>
<ToGMT>GMTTIMESTAMP '2008-10-01 19:00:35.976118'</ToGMT>
</Timestamp>
<Timestamp>
<Input>200811011200</Input>
<ToLocal>TIMESTAMP '2008-11-01 13:00:36.447716'</ToLocal>
<ToGMT>GMTTIMESTAMP '2008-11-01 20:00:36.447716'</ToGMT>
</Timestamp>
<Timestamp>
<Input>200812011200</Input>
<ToLocal>TIMESTAMP '2008-12-01 13:00:36.454812'</ToLocal>
<ToGMT>GMTTIMESTAMP '2008-12-01 20:00:36.454812'</ToGMT>
</Timestamp> |
The local TIMESTAMP times should all be '12:00' (I'm still not sure what's going on with the seconds there, but that's another matter).
CASTs for dates before Oct. 26 seem to work. CASTs for dates after that, don't. 
By the way, the date in the last example is for Dec. 1; I would have expected that to work under either the old or new DST regulations. |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Thu Oct 30, 2008 4:34 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
Open a PMR 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| rekarm01 |
| Posted: Thu Oct 30, 2008 6:43 pm Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 1415
|
I opened up a PMR ... waiting to hear back from IBM.
In the meantime, I tested my message flow on different brokers. Some of the brokers treat timestamps for this week as expected, while other do not. Even brokers on the same server behave differently. That's peculiar. |
|
| Back to top |
|
|
| mborowicki |
| Posted: Wed May 13, 2009 7:11 am Post subject: |
|
|
Newbie
Joined: 13 May 2009
Posts: 7
|
Have you got any response for your PMR?
I have a similar problem with DST for some past dates.
I do a normalization of date by parsing it and converting back to string and the results are:
<Input>1962-06-23T12:00:00.000+00:00</Input>
<Normal>1962-06-23T14:00:00.000+02:00</Normal>
--> this is OK
<Input>1963-06-23T12:00:00.000+00:00</Input>
<Normal>1963-06-23T14:00:00.000+01:00</Normal>
--> the date is changed, because the last one means 13:00 in GMT which is different from original 12:00.
The correct date in the last example should be 1963-06-23T14:00:00.000+[b]02:00[/b] but it is another issue.
Have you tried to run your code but with ToLocal set to CAST(ts AS CHARACTER [b]FORMAT 'I'[/b]) to see which timezone is assumed? |
|
| Back to top |
|
|
| napier |
| Posted: Wed May 13, 2009 7:48 am Post subject: |
|
|
Apprentice
Joined: 09 Oct 2007
Posts: 48
Location: USA
|
| Append the local time zone to the incoming timestamp value and then cast as a timestamp you will get the expected result. |
|
| Back to top |
|
|
| rekarm01 |
| Posted: Thu May 14, 2009 7:38 pm Post subject: Re: WMB 6.0.0.5: DST problem casting CHARACTER to TIMESTAMP |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 1415
|
I had intended to follow up with a response sooner, but I wanted to do some additional testing first. I needed to put this on hold for a while, due to other priorities, but the problem has been at least partially resolved.
| mborowicki wrote: |
| Have you got any response for your PMR? |
There were two separate issues.
The first issue was that broker timestamps were still observing old rules for DST start/end dates, despite the latest DST patches. This turned out to occur only for brokers with WTX installed. The broker was using WTX ICU libraries instead of WMB ICU libraries, to calculate DST. The suggested fix was to either reorder the broker LIBPATH, or apply a DST patch to WTX, (or both).
That issue is resolved.
The second issue was that casting from CHARACTER to TIMESTAMP (or maybe from TIMESTAMP to CHARACTER) produces timestamps that seem to be off by an hour, when DST is in effect for current date but not the timestamp date (or vice versa). IBM's response was, in part:
| Quote: |
| An important point to bear in mind is that "simple" timestamps (that is those outside MRM) are not timezone aware. They are aware of the broker locale's current offset from UTC (including DST when it is in effect), but they are not aware of the broker locale's DST rules. However casting of timestamps is carried out using ICU routines and those are aware of both locales and rules. |
This was followed up with a more detailed explanation, that the broker is working as designed, but that my expectation might need adjusting.
| mborowicki wrote: |
I have a similar problem with DST for some past dates.
...
Have you tried to run your code but with ToLocal set to CAST(ts AS CHARACTER FORMAT 'I') to see which timezone is assumed? |
Local timezone is assumed. I just ran another test, with TZ=PST8PDT, and the following ESQL:
| Code: |
DECLARE ts TIMESTAMP;
SET OutputRoot = InputRoot;
SET ts = CAST(OutputRoot.XMLNSC.Timestamp.Input AS TIMESTAMP FORMAT 'yyyy/MM/dd HH:mm:ss');
SET OutputRoot.XMLNSC.Timestamp.ToLocal = CAST(ts AS CHARACTER FORMAT 'I');
SET OutputRoot.XMLNSC.Timestamp.ToGMT = CAST(CAST(ts as GMTTIMESTAMP) AS CHARACTER FORMAT 'I'); |
The broker's current timezone is PDT (GMT-07:00).
The generated output fields are:
| Code: |
Input: '2008/10/01 12:00:00'
ToLocal: '2008-10-01T12:00:00.756-07:00'
ToGMT: '2008-10-01T19:00:00.756+00:00'
Input: '2008/12/01 12:00:00'
ToLocal: '2008-12-01T13:00:00.379-08:00' (expected 12:00)
ToGMT: '2008-12-01T20:00:00.379+00:00' |
All of the fields in the output timestamps are as expected, except for the ToLocal hour in the second example; I expected '12:00', not '13:00'.
| napier wrote: |
| Append the local time zone to the incoming timestamp value and then cast as a timestamp you will get the expected result. |
No. That does not change the result:
| Code: |
-- SET ts = CAST(OutputRoot.XMLNSC.Timestamp.Input AS TIMESTAMP FORMAT 'I');
Input: '2008-10-01T12:00:00.000-07:00'
ToLocal: '2008-10-01T12:00:00.000-07:00'
ToGMT: '2008-10-01T19:00:00.000+00:00'
Input: '2008-12-01T12:00:00.000-08:00'
ToLocal: '2008-12-01T13:00:00.000-08:00' (expected 12:00)
ToGMT: '2008-12-01T20:00:00.000+00:00' |
|
|
| Back to top |
|
|
| mborowicki |
| Posted: Tue May 19, 2009 12:21 am Post subject: Re: WMB 6.0.0.5: DST problem casting CHARACTER to TIMESTAMP |
|
|
Newbie
Joined: 13 May 2009
Posts: 7
|
I have received partial response for my PMR (36455 820 820).
There are two issues:
1. Incorrect DST offset for tables some years in our timezone is probably the result of error in DST tables, but IBM is still investigating.
2. Invalid time after parsing and formating timestamp.
I got similar answer as rekarm01: when parsing - current timezone offset is used, but when formating - the right offset for the timestamp that is casted.
This is far from satisfactory and frankly surprising.
If TIMESTAMP is not timezone-aware then why it does use right offset when casting to string? This is not coherent and makes simple operation on datetime fields unstable.
Please consider the following set of operation on datetime, which is common for broker:
1. parse datetime string
2. make some operations on TIMESTAMP (eg. add INTERVAL)
3. format it back to string
If I use TIMESTAMP and CASTs for such transformation I will get expected results half year and corrupted time during the other half.
The workaround I see is either to use Java functions for datetime operations or to use GMTTIMESTAMP (instead of TIMESTAMP) and manually alter the result using right DST offset... |
|
| Back to top |
|
|
|
|
