| Author |
Message
|
| vasumath |
 Posted: Wed Aug 12, 2009 7:24 am Post subject: TIMESTAMP casting issue in AIX environment |
 |
|
Novice
Joined: 12 Feb 2007
Posts: 15
|
Hi All,
I am getting the timestamp from the input XML of format yyyyMMddhhmmss (for ex 20090807011747).
I am casting the same in ESQL using the below lines
DECLARE TargetDate TIMESTAMP;
SET TargetDate = CAST(SourceDate AS TIMESTAMP FORMAT 'yyyymmddHHmmss');
SET EditTimeStamp = CAST(TargetDate AS CHARACTER FORMAT 'yyyy-MM-dd HH:mm:ss');
In Windows the output is as expected i.e. 2009-08-07 01:17:47.
But in AIX, one hour is incremented with the input value i.e. the output is 2009-08-07 02:17:47.
I am not sure why there is a mismatch in re-formatting the timestamp in Windows and AIX.
I have tried my level best to fix this but I am not able to locate the issue. Please help me out to solve this issue
Thanks
Vasumathi |
|
| Back to top |
|
 |
| Vitor |
| Posted: Wed Aug 12, 2009 7:27 am Post subject: Re: TIMESTAMP casting issue in AIX environment |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| vasumath wrote: |
| I am not sure why there is a mismatch in re-formatting the timestamp in Windows and AIX. |
Is there a difference between the time & regional settings on the 2 servers?
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| vasumath |
| Posted: Fri Aug 14, 2009 5:17 am Post subject: |
|
|
Novice
Joined: 12 Feb 2007
Posts: 15
|
I have tried to change the Regional setting in Windows to see some difference in the casting...
But there was no change....
Can you please let me know if there is another option? |
|
| Back to top |
|
|
| nvenkatesh |
| Posted: Fri Aug 14, 2009 6:28 am Post subject: |
|
|
Apprentice
Joined: 29 Jan 2007
Posts: 45
|
|
| Back to top |
|
|
| chrisgclark |
| Posted: Tue Oct 20, 2009 3:11 am Post subject: |
|
|
Apprentice
Joined: 26 Mar 2009
Posts: 35
|
Hi all,
I also have this issue and can not seem to solve it.
I have the following code and my servers are running BST time. (On AIX the 'date' command shows BST time and BST timezone. I'm running Broker 6.1.0.2.)
| Code: |
| SET orfer.CURRENT_TIMESTAMP = CAST(CURRENT_TIMESTAMP AS CHARACTER FORMAT 'I') |
On windows this shows 2009-10-20T11:31:23.001+01:00 (i.e. correct timezone, correct BST time)
but on AIX it shows 2009-10-20T11:31:23.001+00:00 (i.e. wrong timezone, but correct BST time)
I can't figure out why on AIX the timezone is showing as GMT, not BST. After research I tried changing the AIX TZ environment variable to Europe/London, but this changed the output on AIX to:
2009-10-20T10:31:23.001+01:00 (i.e. correct timezone, but wrong time).
I'm not sure how to implement the following...
| Quote: |
| 'For CAST to calculate the offset correctly, the time passed into CAST must have a time zone associated with it, as a Z parameter. ' |
I also tried the following but still no joy:
| Code: |
| SET orfer.CURRENT_TIMESTAMP = CAST(CURRENT_TIMESTAMP + LOCAL_TIMEZONE AS CHARACTER FORMAT 'I') |
Can anyone help please.
Chris |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Tue Oct 20, 2009 4:34 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
Do not pass CURRENT_TIMESTAMP to your cast.
Pass CURRENT_GMTTIMESTAMP.
This is what the previous posts told you to do... 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| chrisgclark |
| Posted: Tue Oct 20, 2009 6:21 am Post subject: |
|
|
Apprentice
Joined: 26 Mar 2009
Posts: 35
|
fjb_saper, thanks for your comment, however I tried using CURRENT_GMTTIMESTAMP with tz=Europe/London on AIX, however this shows timestamp as:
2009-10-20T10:31:23.001+00:00 - GMT time, GMT timezone.
What I need is BST time, BST timezone.
i.e. I need:
2009-10-20T11:31:23.001+01:00
I can get this on Windows with
| Code: |
| SET orfer.CURRENT_TIMESTAMP = CAST(CURRENT_TIMESTAMP AS CHARACTER FORMAT 'I') |
but not on AIX. I don't understand why this does not work on AIX.
Any other suggestions?
Last edited by chrisgclark on Tue Oct 20, 2009 6:36 am; edited 1 time in total |
|
| Back to top |
|
|
| Gaya3 |
| Posted: Tue Oct 20, 2009 6:25 am Post subject: |
|
|
Jedi
Joined: 12 Sep 2006
Posts: 2493
Location: Boston, US
|
|
| Back to top |
|
|
| chrisgclark |
| Posted: Tue Oct 20, 2009 7:58 am Post subject: |
|
|
Apprentice
Joined: 26 Mar 2009
Posts: 35
|
| Gaya3 - thanks for the link... we've upgraded one of our servers to Broker6.1.0.5 so I'll check if this is still an issue on 6.1.0.5. I'll let you know... |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Wed Oct 21, 2009 1:03 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
| chrisgclark wrote: |
fjb_saper, thanks for your comment, however I tried using CURRENT_GMTTIMESTAMP with tz=Europe/London on AIX, however this shows timestamp as:
2009-10-20T10:31:23.001+00:00 - GMT time, GMT timezone.
What I need is BST time, BST timezone.
i.e. I need:
2009-10-20T11:31:23.001+01:00
I can get this on Windows with
| Code: |
| SET orfer.CURRENT_TIMESTAMP = CAST(CURRENT_TIMESTAMP AS CHARACTER FORMAT 'I') |
but not on AIX. I don't understand why this does not work on AIX.
Any other suggestions? |
So it shows up as the correct time.
Now you can cast this to type TIMESTAMP.
Check that it adds the difference. If not there is a variable that does hold this information (difference of current tz to gmt).
You should then be able to cast your variable from TIMESTAMP to CHAR.
You might have to add the tz info +xhours by appending it to the string.
Note the easier way to do this might well be
cast (CURRENT_TIMESTAMP) to string (don't add tz in format)
append to string the difference due to tz.
Have fun
_________________
MQ & Broker admin |
|
| Back to top |
|
|
|
|
