| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| Esql Time datatype - cast problem. |
« View previous topic :: View next topic » |
| Author |
Message
|
| paulballard |
 Posted: Mon May 21, 2007 4:52 am Post subject: Esql Time datatype - cast problem. |
 |
|
Newbie
Joined: 06 Feb 2007
Posts: 2
|
Hello,
I am experiencing a problem when casting to a time datatype. The following shows my esql code:
set out.latestExpectedTimeOfArrival = cast(in.expectedTime as time format 'HH:mm:ss');
The value of in.expectedTime is 02:35:14 by the result of the cast operation gives 03:35:14.
I assume this is due to daylight savings but how do I perform the cast with the time being adjusted, ie, such that the result of the cast operation is 02:35:14? |
|
| Back to top |
|
 |
| fat_tony |
| Posted: Sat May 26, 2007 12:48 pm Post subject: |
|
|
Novice
Joined: 02 Dec 2006
Posts: 15
|
I'm pretty sure that daylight savings has nothing to do with a CAST statement.
This doesn't make any sense - how have you proved that the result and the input are as you say? An mqsi trace of the flow? Please post the results of the trace.
You say the input is 02:35:14, which is the expected output Time format. There may be a valid reason for doing this (perhaps some manipulation of the time, or validation?), but why are you performing a CAST when the data is already in the correct format? If this is XML then we're talking about string data, there's no need to cast into types just to set values.
I suspect we're not seeing the whole picture...  |
|
| Back to top |
|
|
| Vitor |
| Posted: Sun May 27, 2007 10:08 am Post subject: Re: Esql Time datatype - cast problem. |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| paulballard wrote: |
| I assume this is due to daylight savings but how do I perform the cast with the time being adjusted, ie, such that the result of the cast operation is 02:35:14? |
You need to check out the LOCALE setting of your box. All MQ is in UTC, but you can generate local timestamps, e.g. TIMESTAMP and GMTTIMESTAMP.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| paulballard |
| Posted: Tue May 29, 2007 12:41 am Post subject: |
|
|
Newbie
Joined: 06 Feb 2007
Posts: 2
|
Hi,
Thanks for your replies. My original post was not 100% accurate.
The problem is that we are receiving the data from a back end system in the format hhmm and wish to convert this into the format representing xs:time.
As mentioned, by doing this, the CAST statement appears to make an adjustment for the daylight savings currently in operation in the UK, ie, it adds an hour to the date returned by the back end service.
The mqsiservice -t operation gives the following output:
| Code: |
mqsiservice -t
BIPv600 en GB
ucnv Console CCSID 819 dft ucnv CCSID 819
ICUW ISO-8859-1 ICUA ISO-8859-1
Current Local time: 2007-05-29 09:18:05.712636
Current UTC time: 2007-05-29 08:18:05.712755
TimeZone ID: GMT
Day Names: Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday,
Month Names: January, February, March, April, May, June, July, August, September, October, November, December,
First day of week: 2
Days in first week of year: 1
AM: AM PM: PM |
and echo ${TZ} gives the following:
| Code: |
| GMT0BST,M3.5.0,M10.5.0 |
I guess we could do the CAST and then adjust the resultant time using the LOCAL_TIMEZONE function?
What I really need though is for the CAST operation to not perform any locale adjustments. Is this acheivable?
Does GMTTime give this functionality?
Thanks in advance. |
|
| Back to top |
|
|
| fschofer |
| Posted: Fri Jul 13, 2007 3:48 am Post subject: |
|
|
Knight
Joined: 02 Jul 2001
Posts: 524
Location: Mainz, Germany
|
Hello Paul,
i have a similar problem here with casting a string from a xml message to a timestamp.
| Code: |
| This resolved to ''CAST('2007-07-12 23:59:59' AS TIMESTAMP FORMAT 'yyyy-MM-dd HH:mm:ss' )''. The result was ''TIMESTAMP '2007-07-13 00:59:59''' |
.
Did you find a way to prevent the CAST function from doing this or have you implemented a work around ?
Greetings
Frank |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Fri Jul 13, 2007 3:03 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
| fschofer wrote: |
Hello Paul,
i have a similar problem here with casting a string from a xml message to a timestamp.
| Code: |
| This resolved to ''CAST('2007-07-12 23:59:59' AS TIMESTAMP FORMAT 'yyyy-MM-dd HH:mm:ss' )''. The result was ''TIMESTAMP '2007-07-13 00:59:59''' |
.
Did you find a way to prevent the CAST function from doing this or have you implemented a work around ?
Greetings
Frank |
The way around is to express all your times either with a TZ component (ZZZ) in the format or express them all in GMTTIMESTAMP and not TIMESTAMP.
_________________
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
|
|
|
|
