| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| casting CHARACTER as TIMESTAMP doesnt't preserve timezone |
« View previous topic :: View next topic » |
| Author |
Message
|
| cynik |
 Posted: Wed Jun 01, 2011 1:24 am Post subject: casting CHARACTER as TIMESTAMP doesnt't preserve timezone |
 |
|
Newbie
Joined: 31 Mar 2009
Posts: 9
|
Could someone please help me with following problem?
Setup:
Operating system: Microsoft Windows Server 2003, Standard Edition, SP2
OS timezone is set to: UTC+01:00 (Belegrade, Bratislava, Budapest,...)
Websphere MQ: 7.0.1.2
Websphere Message Broker: 7.0.0.2
Problem Description:
DST transition in OS specified timezone is from 2011-10-30T03:00:00+02:00 to 2011-10-30T02:00:00+01:00. Casting CHARACTER (containing time from this transition period) as a TIMESTAMP doesn’t preserve specified timezone.
ESQL example:
DECLARE inputDatetime CHARACTER '2011-10-30T02:30:00+02:00';
DECLARE datetime TIMESTAMP CAST(inputDatetime AS TIMESTAMP FORMAT 'I');
DECLARE outputDatetime CHARACTER CAST(datetime AS CHARACTER FORMAT 'I');
Resulting outputDatetime contains '2011-10-30T02:30:00+01:00' instead of expected '2011-10-30T02:30:00+02:00' |
|
| Back to top |
|
 |
| lancelotlinc |
| Posted: Wed Jun 01, 2011 4:23 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
You should not be using a TZ offset in stored time values anyway. If you store or manipulate time values, do so in GMT. Convert only to local time when and if displayed to a human. Any other use of time value should only be in GMT and therefore no TZ offset is needed to be stored with that value.
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Wed Jun 01, 2011 12:52 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
Time and time again I am amazed by what is assumed and taken as matter of fact when dealing with timestamps, date and time values.
Here is my short list of answers
- If you need to look at time intervals and date and time values across time zones, use only a GMT notation. Your time will always be exact.
- The broker's time system with DST used to have a flaw in that that it did only take into account the offset at time of processing (now) and not at time of date (future or past). Although time handling has been made much better I have not verified yet if this has been fixed.
- This is a reason to use java only (with the latest TZ fixpack) when converting from one TZ to the other using a text format, or a long.
Intervals should only be displayed in GMT format.
Finally @ cynik USE GMTTIMESTAMP and do not use TIMESTAMP.
Enjoy one and all. 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
|
|
|
| |
|
Page 1 of 1 |
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
|
|
