| Author |
Message
|
| ynatr |
 Posted: Fri Feb 19, 2010 10:29 am Post subject: cast datetime and daylight saving |
 |
|
Newbie
Joined: 19 Feb 2010
Posts: 2
|
Platform: WMB 6.1.0.5, Win Server 2003 SP2
We receive XML notification message about a future event.
Need to send a e-mail with the event date/time
Current broker time zone is CST
The event date/time will be in CDT
The input date string: '2010-04-12T13:30:00-05:00'
The result we get after parse/format is '2010-04-12T12:30:00-05:00'
Here is the test code
| Code: |
DECLARE dT TIMESTAMP;
set OutputRoot = InputRoot;
set dT = CAST(OutputRoot.XMLNSC.Test.In as TIMESTAMP FORMAT 'IU');
set OutputRoot.XMLNSC.Test.CurrentTimestamp = CAST(CURRENT_TIMESTAMP as CHAR FORMAT 'IU');
set OutputRoot.XMLNSC.Test.Out1 = CAST(dT as CHAR FORMAT 'IU');
set OutputRoot.XMLNSC.Test.Out2 = CAST(dT as CHAR FORMAT 'MM/dd/yyyy hh:mm a zzzz');
|
The input test message:
| Code: |
<Test>
<In>2010-04-12T13:30:00-05:00</In>
</Test>
|
The output:
| Code: |
<Test>
<In>2010-04-12T13:30:00-05:00</In>
<CurrentTimestamp>2010-02-19T11:04:35.506-06:00</CurrentTimestamp>
<Out1>2010-04-12T12:30:00.000-05:00</Out1>
<Out2>04/12/2010 12:30 PM Central Daylight Time</Out2>
</Test>
|
I expected the Out1 = In. It looks like the input string is parsed using the broker local time zone.
Is that a correct approach, and what am I missing?
Appreciate any suggestions
Thank you
Last edited by ynatr on Mon Feb 22, 2010 8:40 am; edited 1 time in total |
|
| Back to top |
|
 |
| smdavies99 |
| Posted: Fri Feb 19, 2010 11:16 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
Have a look at using GMTTIMESTAMP with LOCAL_TIMEZONE to get your answer.
_________________
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 |
|
|
| ynatr |
| Posted: Mon Feb 22, 2010 8:49 am Post subject: |
|
|
Newbie
Joined: 19 Feb 2010
Posts: 2
|
Thank you for the reply. Unfortunately the TIMESTAMP timezone deficiency is not very well documented. The only place I found it is in the Java API for MbTimestamp class where it says, the the time zone is not supported outside of GMT. I would guess it's is true for the ESQL TIMESTAMP too.
This makes CAST to TIMESTAMP from an arbitrary date/time string, other than one representing a local date/time, unsafe. Working with GMTTIMESTAMP is also not always feasible, as in our case where it should be shown in a user friendly format.
I was able to work around the issue using Java to convert from GMTTIMESTAMP to TIMESTAMP without losing the input timezone shift. |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Mon Feb 22, 2010 11:41 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
The code
| Code: |
SET wmq_time = CAST(( PutDate || ' ' || PutTime ) AS TIMESTAMP) + LOCAL_TIMEZONE;
|
Is used to convert the Date/Time of an WMQ message (which is always GMT) into a local time. the LOCAL_TIMEZONE is the magic bit. This in my location is either 0 or +1 hour (GMT/BST)
Perhaps something like this might solve your problem without the need to use Java?
_________________
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 |
|
|
| AndreasMartens |
| Posted: Tue Feb 23, 2010 5:20 am Post subject: Existing 6.1 limitation, v7 better. |
|
|
Acolyte
Joined: 30 Jan 2006
Posts: 65
Location: Hursley, UK
|
This is because ESQL TIMESTAMPS are not timezone aware.
They are aware of the broker locale's current offset from UCT (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.
Let's consider what happens when a string is cast as a timestamp and subsequently cast back to a string.
Assume that today's date is 12 February 2010, that our broker is running in GMT and our input string is 2010-02-12T13:30:00-05:00.
When we cast that using format 'IU', the result is an ESQL timestamp containing 2010-02-12 18:30:00.
The reason that the hour has changed is that it has been advanced 5 hours to make it match the broker's current locale.
When that timestamp is cast back to string using format 'MM/dd/yyyy hh:mm a zzzz' the result is 02/12/2010 06:30 PM Greenwich Mean Time.
This is because the timezone string appended is what would be in effect in the broker's locale on that date.
Now suppose the input string is 2010-04-12T13:30:00-05:00 - a date on which daylight saving time is in effect in the broker's locale.
The cast from string to timestamp results in 2010-04-12 18:30:00 because the broker's current locale is GMT and so the hour is advanced by 5 hours to match that.
When that timestamp is cast back to string the result is 04/12/2010 06:30 PM British Daylight Time.
This is because the timezone component used is what is in effect in the broker's locale on that date.
But what would happen if we used the same input values when our broker is running in April when DST is in effect?
In the case of the February date, the cast to timestamp produces 2010-02-12 19:30:00 because the broker locale is currently 1 hour ahead of GMT therefore the hour has to be advanced by 6 hours instead of 5 hours.
When the timestamp is cast back to string the result is 02/12/2010 07:30 PM Greenwich Mean Time because once again
the timezone component used is what is in effect in the broker's locale on that date.
In the case of the April date the output is 04/12/2010 07:30 PM British Daylight Time for the same reason.
One way to achieve consistent results with v6.1 is to DECLARE dT as GMTTIMESTAMP instead of a simple TIMESTAMP.
The outputs would then be 06:30 PM GMT+00:00 regardless of the date in the input value and the current date in the broker locale.
Of course in v7.0 TIMESTAMPS are timezone aware and so there is no problem ! |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Tue Feb 23, 2010 11:43 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
| Quote: |
In the case of the February date, the cast to timestamp produces 2010-02-12 19:30:00 because the broker locale is currently 1 hour ahead of GMT therefore the hour has to be advanced by 6 hours instead of 5 hours.
When the timestamp is cast back to string the result is 02/12/2010 07:30 PM Greenwich Mean Time because once again
the timezone component used is what is in effect in the broker's locale on that date. |
It is refreshing to see how little people, including and especially me, understand about TimeZones.
Can you please explain why you advance by 6 hours?
The -05:00 is a reference to GMT and thus this should not change. So the time in February should still only be advanced by 5 hours to get to GMT.
Whatever happens on casting back is really dependent on the time routines... and they should be aware of the difference between standard time and daylight savings time... right?
So the time stays 6:30 PM with an offset of 00:00 or becomes 07:30+01:00 with the local time... (CET)
Do you mean to say that the broker will display the time with the current offset (DST) regardless of the date in the timestamp?
Have fun 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
|
|
