| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| Trouble generating a Timestamp in GMT TZ |
« View previous topic :: View next topic » |
| Author |
Message
|
| rahulk01 |
 Posted: Sun Jul 19, 2020 9:08 am Post subject: Trouble generating a Timestamp in GMT TZ |
 |
|
Apprentice
Joined: 26 Dec 2019
Posts: 35
|
Hi,
I have a requirement where I need to get the current GMT Timestamp and store it in DB. My Broker is running in a TZ 2 hours ahead of GMT.
I have tried multiple options but nothing seems to work:
Following is a list of code I have tried:
DECLARE tsCurrTime TIMESTAMP CURRENT_TIMESTAMP;
DECLARE tsCurrGMTTime GMTTIMESTAMP CURRENT_GMTTIMESTAMP;
DECLARE dbTS_UTC CHAR Timezone.formatTSinTimezone(CURRENT_TIMESTAMP, 'UTC', 'I'); -- this is an internal function in our framework which gives the TS in GMT as a char.
DECLARE cDate CHAR SUBSTRING(dbTS_UTC FROM 1 FOR 10);
DECLARE nPos INTEGER POSITION('T' IN dbTS_UTC);
DECLARE cTime CHAR SUBSTRING(dbTS_UTC FROM (nPos + 1) FOR  ;
DECLARE tsTimeout2 GMTTIMESTAMP CAST(dbTS_UTC AS GMTTIMESTAMP FORMAT 'I');
DECLARE cTS CHARACTER (cDate || ' ' || cTime);
DECLARE tsGmt1 GMTTIMESTAMP CAST(cTS AS GMTTIMESTAMP FORMAT 'yyyy-MM-dd HH:mm:ss');
DECLARE tsGmt2 GMTTIMESTAMP CAST(dbTS_UTC AS GMTTIMESTAMP FORMAT 'I');
DECLARE tsGmt3 TIMESTAMP CAST(dbTS_UTC AS TIMESTAMP FORMAT 'I');
The output is as below:
dbTS_UTC:CHARACTER:2020-07-19T16:56:41.594+00:00 (Only option giving me a TS in GMT but as a char)
cDate:CHARACTER:2020-07-19
cTS:CHARACTER:2020-07-19 16:56:41
cTime:CHARACTER:16:56:41
tsCurrGMTTime:TIMESTAMP: 2020-07-19 18:56:41.594
tsCurrTime:TIMESTAMP: same as tsCurrGMTTime
tsGmt1:TIMESTAMP: same as tsCurrGMTTime
tsGmt2:TIMESTAMP: same as tsCurrGMTTime
tsGmt3:TIMESTAMP: same as tsCurrGMTTime
tsTimeout2:TIMESTAMP: same as tsCurrGMTTime
Frankly I had expected the option DECLARE tsCurrGMTTime GMTTIMESTAMP CURRENT_GMTTIMESTAMP; to work.
Any help |
|
| Back to top |
|
 |
| timber |
| Posted: Sun Jul 19, 2020 12:50 pm Post subject: |
|
|
Grand Master
Joined: 25 Aug 2015
Posts: 1292
|
| What are you using to produce that output? A Trace node? Some kind of logger? |
|
| Back to top |
|
|
| rahulk01 |
| Posted: Sun Jul 19, 2020 9:55 pm Post subject: |
|
|
Apprentice
Joined: 26 Dec 2019
Posts: 35
|
It's a regular compute node. I need to schedule a task 10 mins from now in the GMT TZ, as my scheduler works in GMT. So I am trying to get the current GMT Timestamp and add 10 mins to it and update it in the DB.
I was expecting the code:
DECLARE tsCurrGMTTime GMTTIMESTAMP CURRENT_GMTTIMESTAMP;
to provide me tsCurrGMTTime as the current GMT Time and I can add 10 mins to it, but it instead it gives me the current Timestamp in the local Timezone (which is 2 hours ahead of GMT), and then adds 10 minutes to it.
So the scheduled task which should have kicked of after 10 mins, gets kicked off after 2 hrs and 10 mins.
I tried using an internal function DECLARE dbTS_UTC CHAR Timezone.formatTSinTimezone(CURRENT_TIMESTAMP, 'UTC', 'I'); which gives me timestamp in UTC as a Char. Then extracted date and time from it casted them to make a Timestamp.
But strangely after casting, it generates the Timestamp in the local Timzezone:
DECLARE dbTS_UTC CHAR Timezone.formatTSinTimezone(CURRENT_TIMESTAMP, 'UTC', 'I');
it returns:
dbTS_UTC:CHARACTER:2020-07-19T16:56:41.594+00:00
DECLARE cDate CHAR SUBSTRING(dbTS_UTC FROM 1 FOR 10);
DECLARE nPos INTEGER POSITION('T' IN dbTS_UTC);
DECLARE cTime CHAR SUBSTRING(dbTS_UTC FROM (nPos + 1) FOR 8;
DECLARE cTS CHARACTER (cDate || ' ' || cTime);
cDate:CHARACTER:2020-07-19
cTime:CHARACTER:16:56:41
cTS:CHARACTER:2020-07-19 16:56:41
All the values are correct till now.
But after casting as below:
1) DECLARE tsGmt2 GMTTIMESTAMP CAST(dbTS_UTC AS GMTTIMESTAMP FORMAT 'I');
2) DECLARE tsGmt3 TIMESTAMP CAST(dbTS_UTC AS TIMESTAMP FORMAT 'I');
3) DECLARE tsGmt1 GMTTIMESTAMP CAST(cTS AS GMTTIMESTAMP FORMAT 'yyyy-MM-dd HH:mm:ss');
4) DECLARE tsGmt1 TIMESTAMP CAST(cTS AS TIMESTAMP FORMAT 'yyyy-MM-dd HH:mm:ss');
All four of them returns the timestamp in the current local timezone as '2020-07-19 18:56:41.594' |
|
| Back to top |
|
|
| abhi_thri |
| Posted: Sun Jul 19, 2020 11:40 pm Post subject: |
|
|
Knight
Joined: 17 Jul 2017
Posts: 516
Location: UK
|
hi...it is a bit strange that even the GMTTIMESTAMP declaration is showing up the value as TIMESTAMP. Can you please remove all the other code and just keep the below declaration and run the user trace to see the value getting assigned.
| Quote: |
| DECLARE tsCurrGMTTime GMTTIMESTAMP CURRENT_GMTTIMESTAMP; |
| Quote: |
| tsCurrGMTTime:TIMESTAMP: 2020-07-19 18:56:41.594 |
Ideally it should show the value as GMTTIMESTAMP one and not as the above TIMESTAMP value.
E.g:-
| Quote: |
| tsCurrGMTTime:GMTTIMESTAMP : <value> |
PS: There are some timestamp params that will influence timestamp processing in the code but I don't think it should affect the declaration as such,
https://www.ibm.com/support/knowledgecenter/SSMKHH_10.0.0/com.ibm.etools.mft.doc/bn23633_.html |
|
| Back to top |
|
|
| rahulk01 |
| Posted: Mon Jul 20, 2020 9:32 pm Post subject: |
|
|
Apprentice
Joined: 26 Dec 2019
Posts: 35
|
Apparently, this solved the issue:
$ vi $MQSI_WORKPATH/config/<IntegrationNode>/profiles/commonprofile.sh
edit TZ in file:
--------------------------------
export SPRING_PROFILES_ACTIVE=st
export TZ=Europe/Oslo
--------------------------------
restart IIB:
mqsistop ftmsmp && mqsistart ftmsmp |
|
| 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
|
|
|
|
