| Author |
Message
|
| maven |
 Posted: Mon May 18, 2009 12:32 am Post subject: How to get Timestamp in CET thru ESQL |
 |
|
Novice
Joined: 03 Jun 2007
Posts: 15
|
Hey,
I need to set timestamp in CET in the MQMD. I can find that only localtime or gmt time can be set thru esql function. Is there any esql function or a way to set CET timestamp, taking into considering the daylight saving. The server is in US.
Im planning to code something like below,
SET Diff = EXTRACT( HOUR from (CURRENT_GMTTIMESTAMP - CURRENT_TIMESTAMP);
SET TineZone = CASE Diff
WHEN 7 THEN 'set PutTime = CURRENT_TIME + CAST(5 AS INTERVAL HOUR);'
WHEN 8 THEN 'set PutTime = CURRENT_TIME + CAST(6 AS INTERVAL HOUR);'
WHEN -17 THEN 'set PutTime = ...'
'set PutDate = CURRENT_DATE + CAST(1 AS INTERVAL DAY)'
WHEN -18 THEN 'set PutTime = ...'
'set PutDate = ...'
END;
Im using MB v6.1.0.3
Let me know if there is any other suggestion. |
|
| Back to top |
|
 |
| MQEnthu |
| Posted: Mon May 18, 2009 12:48 am Post subject: |
|
|
Partisan
Joined: 06 Oct 2008
Posts: 329
Location: India
|
|
| Back to top |
|
|
| Vitor |
| Posted: Mon May 18, 2009 12:51 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| MQEnthu wrote: |
| http://publib.boulder.ibm.com/infocenter/wmbhelp/v6r1m0/topic/com.ibm.etools.mft.doc/ak05490_.htm |
I think the poster's point is that CET is not local to the server he's using, but is local to him.
_________________
Honesty is the best policy.
Insanity is the best defence.
Last edited by Vitor on Mon May 18, 2009 1:04 am; edited 1 time in total |
|
| Back to top |
|
|
| MQEnthu |
| Posted: Mon May 18, 2009 12:57 am Post subject: |
|
|
Partisan
Joined: 06 Oct 2008
Posts: 329
Location: India
|
| Vitor wrote: |
| ...CET is not local to the server he's using, but is local to him |
| maven wrote: |
| ...The server is in US. |
_________________
-----------------------------------------------
It is good to remember the past,
but don't let past capture your future |
|
| Back to top |
|
|
| maven |
| Posted: Mon May 18, 2009 6:27 am Post subject: |
|
|
Novice
Joined: 03 Jun 2007
Posts: 15
|
hi MQEnthu n Vitor,
I had checked for the 'LOCAL_TIMEZONE' function. But it gives the difference between local time and GMT.
Also CET is not local. Since the server on which the boker is installed is in US and we want to set CET time for all our message since our applications in europe cant be modified to use GMT or PST. |
|
| Back to top |
|
|
| Vitor |
| Posted: Mon May 18, 2009 6:31 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| maven wrote: |
| Also CET is not local. Since the server on which the boker is installed is in US and we want to set CET time for all our message since our applications in europe cant be modified to use GMT or PST. |
My point exactly. 
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| rekarm01 |
| Posted: Tue May 19, 2009 11:51 pm Post subject: Re: How to get Timestamp in CET thru ESQL |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 1415
|
| maven wrote: |
| I need to set timestamp in CET in the MQMD. |
Although applications could privately agree to use something other than GMT in the MQMD PutDate/PutTime fields, it's not a good practice.
| maven wrote: |
| Is there any esql function or a way to set CET timestamp, taking into considering the daylight saving. |
No.
| maven wrote: |
I'm planning to code something like below
| Code: |
SET Diff = EXTRACT( HOUR from (CURRENT_GMTTIMESTAMP - CURRENT_TIMESTAMP);
SET TineZone = CASE Diff
WHEN 7 THEN 'set PutTime = CURRENT_TIME + CAST(5 AS INTERVAL HOUR);'
WHEN 8 THEN 'set PutTime = CURRENT_TIME + CAST(6 AS INTERVAL HOUR);'
WHEN -17 THEN 'set PutTime = ...'
'set PutDate = CURRENT_DATE + CAST(1 AS INTERVAL DAY)'
WHEN -18 THEN 'set PutTime = ...'
'set PutDate = ...'
END; |
|
Just a few problems so far:- ESQL casts operands of (CURRENT_GMTTIMESTAMP - CURRENT_TIMESTAMP) to the same type before substracting, returning INTERVAL 0 HOUR; the LOCAL_TIMEZONE function would work better
- some of the other numbers are a bit off
- DST may have started/ended between the message put time and the current time
- the US and Europe don't start or end DST at the same time, the same day, or even the same week, so the difference between local time and CET/CEST is not constant; deriving CET/CEST time from local time would be ... difficult
Here are some other options:- check again whether the applications in Europe really, really can't accept GMT PutDates/PutTimes
- use Java to calculate CET/CEST timestamps; java.util.Calendar is a good place to start
- set the broker's (or server's) local timezone to CET/CEST, so the ESQL local time functions will work as desired; the broker need not use the same timezone as the underlying OS, (though that might present some other challenges)
|
|
| Back to top |
|
|
| fjb_saper |
| Posted: Wed May 20, 2009 12:32 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
| maven wrote: |
| I need to set timestamp in CET in the MQMD. I can find that only localtime or gmt time can be set thru esql function. Is there any esql function or a way to set CET timestamp, taking into considering the daylight saving. The server is in US. |
This is entirely the wrong approach.
MQ writes the timestamp to the MQMD as a GMTTIMESTAMP worldwide.
If you need to display it in CET this is easily done with a number of functions some of which are easily available in Java (Calendar, SimpleDateFormater, TimeZone, Locale...).
This is a display problem. The timestamp on the message's MQMD is always in GMT and you should keep it so. It makes calculations real easy. How you display the result is then a completely different thing...
Have fun 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
|
|
