| Author |
Message
|
| souciance |
 Posted: Mon Nov 24, 2014 5:58 am Post subject: GMT TIMESTAMP summer/winter time |
 |
|
Disciple
Joined: 29 Jun 2010
Posts: 169
|
Hello,
We have a requirement to take the current timestamp and convert it to GMT and substract one hour and send that as output. Ourr code looks as the following:
| Code: |
DECLARE currenttime GMTTIMESTAMP;
DECLARE querytime GMTTIMESTAMP;
DECLARE queryPattern CHARACTER 'yyyy-MM-dd''T''HH:mm:s''Z';
SET currenttime=CAST (CURRENT_TIMESTAMP AS GMTTIMESTAMP FORMAT queryPattern);
SET querytime=CAST (currenttime - (INTERVAL '1' HOUR) AS GMTTIMESTAMP FORMAT queryPattern);
|
I have looked at the info center but couldn't find any concrete information on this. My main question is, will the broker be able to calculate the summer / winter differences (+1/+2 hours) automatically when we do the GMT conversion?
Thanks
Souciance |
|
| Back to top |
|
 |
| Vitor |
| Posted: Mon Nov 24, 2014 6:30 am Post subject: Re: GMT TIMESTAMP summer/winter time |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| souciance wrote: |
| I have looked at the info center but couldn't find any concrete information on this. My main question is, will the broker be able to calculate the summer / winter differences (+1/+2 hours) automatically when we do the GMT conversion? |
Not like this. The time in GMT doesn't vary with daylight savings. You might want to consider TIMESTAMP rather than GMTTIMESTAMP if you want broker to work it out.
You might also want to consider using the LOCAL_TIMEZONE capability rather than a hard coded INTERVAL value. This link also talks about when broker converts from GMT (briefly).
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| souciance |
| Posted: Mon Nov 24, 2014 10:47 am Post subject: |
|
|
Disciple
Joined: 29 Jun 2010
Posts: 169
|
Hi Vitor,
Thanks for the reply.
So you basically you mean the following:
1. Replace all the GMTTIMESTAMP with CURRENT_TIMESTAMP
2. Use local time zone to calculate the offset to GMT.
We have no problem changing the code, but the infocenter does not specify which approach to use. Basically our broker server uses UTC time. Salesforce uses GMT time. We need to send GMT time to salesforce. |
|
| Back to top |
|
|
| Vitor |
| Posted: Mon Nov 24, 2014 10:55 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| souciance wrote: |
| Basically our broker server uses UTC time. Salesforce uses GMT time. We need to send GMT time to salesforce. |
Explain to me the difference between UTC and GMT. except that the French wouldn't stand for a British term being used (!)
http://en.wikipedia.org/wiki/Coordinated_Universal_Time
In my world, UTC & GMT are the same value (plus or minus a few milliseconds). UTC certainly isn't subject to daylight savings.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| souciance |
| Posted: Mon Nov 24, 2014 11:00 am Post subject: |
|
|
Disciple
Joined: 29 Jun 2010
Posts: 169
|
Well looking at the description there seems to be no technical difference, just different ways of referencing the same time standard. Also looking at the info center local timezone it says:
However, for calculations involving GMTTIMEs and GMTTIMESTAMPs, ESQL performs this transformation automatically.
So basically our code should work for GMT conversion and even when the broker server during summer time is +2h ahead. |
|
| Back to top |
|
|
| Vitor |
| Posted: Mon Nov 24, 2014 12:08 pm Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| souciance wrote: |
| So basically our code should work for GMT conversion and even when the broker server during summer time is +2h ahead. |
If your broker server changes time during summer time (i.e. the system clock shows a different time) it's not using UTC as you previously claimed. It's using the local timezone and you'll need to compensate.
If your broker server is using UTC and the displayed time changes because it's using a LOCALE setting or similar you're fine.
You need to check with whoever's running your box and be clear on what they're doing. I've seen sites where (for various reasons) the server didn't have a LOCALE set and the sys admins changed time manually.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| rekarm01 |
| Posted: Mon Nov 24, 2014 4:59 pm Post subject: Re: GMT TIMESTAMP summer/winter time |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 1415
|
| souciance wrote: |
We have a requirement to take the current timestamp and convert it to GMT and subtract one hour ...
| Code: |
DECLARE currenttime GMTTIMESTAMP;
DECLARE querytime GMTTIMESTAMP;
DECLARE queryPattern CHARACTER 'yyyy-MM-dd''T''HH:mm:s''Z';
SET currenttime=CAST (CURRENT_TIMESTAMP AS GMTTIMESTAMP FORMAT queryPattern);
SET querytime=CAST (currenttime - (INTERVAL '1' HOUR) AS GMTTIMESTAMP FORMAT queryPattern); |
|
CURRENT_GMTTIMESTAMP returns the current timestamp, and it's already GMT, so need to convert it, nor worry about winter/summer time. Also, FORMAT strings only affect conversion to/from CHARACTER; TIMESTAMP datatypes don't have a format:
| Code: |
| DECLARE querytime GMTTIMESTAMP CURRENT_GMTTIMESTAMP - INTERVAL '1' HOUR; |
|
|
| Back to top |
|
|
| fjb_saper |
| Posted: Mon Nov 24, 2014 8:44 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
I like to do my time conversions in Java. I don't think the broker keeps track of when summer or winter time goes into effect. So the conversion of current_time to gmt_time is always right. But the conversion of query_time???. For the conversion of query time I would suggest using Java.
Have fun 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
|
|
