ASG
IBM
Zystems
Cressida
Icon
Netflexity
 
  MQSeries.net
Search  Search       Tech Exchange      Education      Certifications      Library      Info Center      SupportPacs      LinkedIn  Search  Search                                                                   FAQ  FAQ   Usergroups  Usergroups
 
Register  ::  Log in Log in to check your private messages
 
RSS Feed - WebSphere MQ Support RSS Feed - Message Broker Support

MQSeries.net Forum IndexWebSphere Message Broker SupportTrouble generating a Timestamp in GMT TZ

Post new topicReply to topic
Trouble generating a Timestamp in GMT TZ View previous topic :: View next topic
Author Message
rahulk01
PostPosted: Sun Jul 19, 2020 9:08 am Post subject: Trouble generating a Timestamp in GMT TZ Reply with quote

Apprentice

Joined: 26 Dec 2019
Posts: 29

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
View user's profile Send private message
timber
PostPosted: Sun Jul 19, 2020 12:50 pm Post subject: Reply with quote

Grand Master

Joined: 25 Aug 2015
Posts: 1127

What are you using to produce that output? A Trace node? Some kind of logger?
Back to top
View user's profile Send private message
rahulk01
PostPosted: Sun Jul 19, 2020 9:55 pm Post subject: Reply with quote

Apprentice

Joined: 26 Dec 2019
Posts: 29

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
View user's profile Send private message
abhi_thri
PostPosted: Sun Jul 19, 2020 11:40 pm Post subject: Reply with quote

Partisan

Joined: 17 Jul 2017
Posts: 357
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
View user's profile Send private message
rahulk01
PostPosted: Mon Jul 20, 2020 9:32 pm Post subject: Reply with quote

Apprentice

Joined: 26 Dec 2019
Posts: 29

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
View user's profile Send private message
Display posts from previous:
Post new topicReply to topic Page 1 of 1

MQSeries.net Forum IndexWebSphere Message Broker SupportTrouble generating a Timestamp in GMT TZ
Jump to:



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
Protected by Anti-Spam ACP


Theme by Dustin Baccetti
Powered by phpBB 2001, 2002 phpBB Group

Copyright MQSeries.net. All rights reserved.