Author Topic: System log Timestamp extraction from UTC to EST  (Read 1416 times)

Manikandan

  • Newbie
  • *
  • Posts: 3
    • View Profile
System log Timestamp extraction from UTC to EST
« on: December 30, 2019, 10:34:03 AM »
Hello All,

When we are trying to extract the OD System Log table on CMOD 10.1 every 15 mins based on TIME_STAMP, we are getting the results in GMT0 instead of EST.  since SL tables are stored in UTC (GMT0) format, we could not get the expected results from our query after 7 PM EST which is next day 12:00 AM UTC. Below is my tweaked query (-5 HOURS) to fetch EST time zone but still it's not working.

SELECT CHAR(MSG_TEXT) FROM ARSSERVR.SL7721
WHERE TIME_STAMP >
     (DAYS(CURRENT DATE) - DAYS('1970-01-01')) * 86400 +
     MIDNIGHT_SECONDS(CURRENT TIME - 15 MINUTES -
     CURRENT TIMEZONE - 5 HOURS)
     AND MSG_NUM = 87

Any help or advice is greatly appreciated.

-Mani

scottnys

  • Jr. Member
  • **
  • Posts: 38
    • View Profile
Re: System log Timestamp extraction from UTC to EST
« Reply #1 on: January 03, 2020, 01:37:48 PM »
Two options:
1) upgrading the system log will add a DB2 timestamp in addition to the CMOD timestamp.  Makes life much easier.  We support multiple CMODS. I haven't upgraded it, but when building a NEW instance it is defined that way.
2) In addition to the "System Log" there is a "System LOAD" AG.  Seeing that you are looking at 87 records, that is exactly what it has in it.  Again with DB2 timestamps.  It break out the TEXT into separate columns.