Converting DECIMAL (getJMSTimestamp) TO DATETIME

I'm assuming getJMSTimestamp returns the number of milliseconds past mightnight 1/1/1970 offset 0. We've been storing timestamps in our Progress databases for decades (well 20 years) as the number of seconds past midnight 1/1/1970 (UNIX time as we've always called it).  If getJMSTimestamp is similar the following should help get you started.You'll need to account for milliseconds and convert date and time to timezone...

&GLOBAL-DEFINE Bias           4
&GLOBAL-DEFINE StandardName   64
&GLOBAL-DEFINE StandardDate   16
&GLOBAL-DEFINE StandardBias   4
&GLOBAL-DEFINE DaylightName   64
&GLOBAL-DEFINE DaylightDate   16
&GLOBAL-DEFINE DaylightBias   4

FUNCTION GetUTCOffsetFromRegistry RETURNS INTEGER ():
  &SCOPED-DEFINE HKEY_LOCAL_MACHINE -2147483646
  &SCOPED-DEFINE ERROR_SUCCESS      0
  &SCOPED-DEFINE MAX_PATH           260
  &SCOPED-DEFINE REG-KEY            "SYSTEM\CurrentControlSet\Control\TimeZoneInformation":u
  &SCOPED-DEFINE REG-ATT            "ActiveTimeBias":u

  DEFINE VARIABLE iOffset    AS INTEGER    NO-UNDO INITIAL ?.
  DEFINE VARIABLE iTZBias    AS INTEGER    NO-UNDO.
  DEFINE VARIABLE iKey       AS INTEGER    NO-UNDO.
  DEFINE VARIABLE iResult    AS INTEGER    NO-UNDO.
  DEFINE VARIABLE iCount     AS INTEGER    NO-UNDO.
  DEFINE VARIABLE iDataType  AS INTEGER    NO-UNDO.
  DEFINE VARIABLE iBiasKey   AS INTEGER    NO-UNDO.
  DEFINE VARIABLE mBias      AS MEMPTR     NO-UNDO.

  RUN RegOpenKeyA
      ({&HKEY_LOCAL_MACHINE},
       {&Reg-Key},
       OUTPUT iKey,
       OUTPUT iResult) NO-ERROR.
  IF NOT ERROR-STATUS:ERROR OR
     iResult = {&ERROR_SUCCESS} THEN DO:

     ASSIGN iCount          = {&MAX_PATH} + 1.
            SET-SIZE(mBias) = iCount
         NO-ERROR.
     RUN RegQueryValueExA
         (iKey,
          {&REG-ATT},
          0,                         /* Reserved */
          OUTPUT iDataType,
          GET-POINTER-VALUE(mBias),
          INPUT-OUTPUT iCount,
          OUTPUT iResult)
         NO-ERROR.
     RUN RegCloseKey
         (iBiasKey,
          OUTPUT iResult)
         NO-ERROR.

     ASSIGN iTZBias =                   ASC(GET-BYTES(mBias, 4, 1))
            iTZBias = (iTZBias * 256) + ASC(GET-BYTES(mBias, 3, 1))
            iTZBias = (iTZBias * 256) + ASC(GET-BYTES(mBias, 2, 1))
            iTZBias = (iTZBias * 256) + ASC(GET-BYTES(mBias, 1, 1))
         NO-ERROR.

     ASSIGN SET-SIZE(mBias) = 0 NO-ERROR.

     /* Assign Offset in seconds */
     ASSIGN iOffset = iTZBias * 60 NO-ERROR.
  END.

  RUN RegCloseKey(iKey,
                  OUTPUT iResult)
      NO-ERROR.

  RETURN iOffset.
END FUNCTION.

/* Gets the Time Zone Offset for the current machine - Returns value in seconds. */
FUNCTION GetUTCOffsetFromKernel       RETURNS INTEGER     () :
  DEFINE VARIABLE mZoneInfo         AS MEMPTR     NO-UNDO.
  DEFINE VARIABLE iReturnValue      AS INTEGER    NO-UNDO.
  DEFINE VARIABLE iUTCOffSet        AS INTEGER    NO-UNDO.
  DEFINE VARIABLE cTimeZoneList     AS CHARACTER  NO-UNDO.
  DEFINE VARIABLE cTimeZoneOffset   AS CHARACTER  NO-UNDO.

  ASSIGN cTimeZoneList   = "ATL,EST,CST,MST,PST,ALA,HAW,EDT,CDT,MDT,PDT":u.
         cTimeZoneOffset = "-4,-5,-6,-7,-8,-9,-10,-4,-5,-6,-7":u
             NO-ERROR.

  ASSIGN SET-SIZE(mZoneInfo) = {&Bias} + {&StandardName} + {&StandardDate} +
                               {&StandardBias} + {&DaylightName} + {&DaylightDate} + {&DaylightBias}
         .

  RUN GetTimeZoneInformation (mZoneInfo, OUTPUT iReturnValue).

  ASSIGN iUTCOffSet =  (GET-LONG(mZoneInfo,1)  +
                        GET-LONG(mZoneInfo,{&Bias} + {&StandardName} + {&StandardDate} +
                                {&StandardBias} + {&DaylightName} + {&DaylightDate} + 1)) * 60
         SET-SIZE(mZoneInfo) = 0
         .

  RETURN iUTCOffSet.
END FUNCTION.

FUNCTION GetUTCOffset RETURNS INTEGER ():
  DEFINE VARIABLE iUTCOffSet AS INTEGER    NO-UNDO INITIAL ?.

  IF OPSYS = "WIN32" THEN DO:
     ASSIGN iUTCOffSet = GetUTCOffsetFromRegistry() NO-ERROR.
     IF iUTCOffSet = ? THEN
        ASSIGN iUTCOffSet = GetUTCOffsetFromKernel() NO-ERROR.
  END.
  ELSE IF OPSYS = "UNIX" THEN DO:
    INPUT THROUGH date +%z NO-ECHO.
    IMPORT iUTCOffSet NO-ERROR.
    INPUT CLOSE.

    IF iUTCOffSet <> ? THEN
       ASSIGN iUTCOffSet = ((iUTCOffset / 100) * -1) * 60 * 60 NO-ERROR.
  END.

  RETURN iUTCOffSet.
END FUNCTION.

PROCEDURE ConvertFromUTCTimeStamp:
  DEFINE INPUT  PARAMETER ip_nTimeStamp     AS DECIMAL    NO-UNDO.
  DEFINE INPUT  PARAMETER ip_iUTCOffset     AS INTEGER    NO-UNDO.
  DEFINE OUTPUT PARAMETER op_dDate          AS DATE       NO-UNDO.
  DEFINE OUTPUT PARAMETER op_iTime          AS INTEGER    NO-UNDO.

  DEFINE VARIABLE iNumDays  AS DECIMAL NO-UNDO.
  DEFINE VARIABLE nNewTime  AS DECIMAL NO-UNDO.

  ASSIGN nNewTime = ip_nTimeStamp - ip_iUTCOffset
         iNumDays = TRUNCATE(nNewTime / 86400, 0)
         op_iTime = INTEGER(nNewTime - (iNumDays * 86400))
         op_dDate = DATE(1, 1, 1970) + iNumDays.

  RETURN.
END PROCEDURE.

PROCEDURE GetTimeZoneInformation EXTERNAL "kernel32.dll":u :
  DEFINE INPUT        PARAMETER mZoneInfo   AS MEMPTR.
  DEFINE RETURN       PARAMETER cchReturned AS LONG.
END PROCEDURE.

PROCEDURE RegOpenKeyA      EXTERNAL "advapi32":u:
  DEFINE INPUT        PARAMETER hkey       AS LONG.
  DEFINE INPUT        PARAMETER lpszSubKey AS CHAR.
  DEFINE OUTPUT       PARAMETER phkResult  AS LONG.
  DEFINE RETURN       PARAMETER lpResult   AS LONG.
END PROCEDURE.

PROCEDURE RegCloseKey      EXTERNAL "advapi32":u:
  DEFINE INPUT        PARAMETER hkey     AS LONG.
  DEFINE RETURN       PARAMETER lpresult AS LONG.
END PROCEDURE.

PROCEDURE RegQueryValueExA EXTERNAL "advapi32":u:
  DEFINE INPUT        PARAMETER hkey         AS LONG.
  DEFINE INPUT        PARAMETER lpValueName  AS CHAR.
  DEFINE INPUT        PARAMETER lpdwReserved AS LONG.
  DEFINE OUTPUT       PARAMETER lpdwType     AS LONG.
  DEFINE INPUT        PARAMETER lpbData      AS LONG. /* memptr */
  DEFINE INPUT-OUTPUT PARAMETER lpcbData     AS LONG.
  DEFINE RETURN       PARAMETER lpresult     AS LONG.
END PROCEDURE.

DEFINE VARIABLE dDate AS DATE        NO-UNDO.
DEFINE VARIABLE iTime AS INTEGER     NO-UNDO.

RUN ConvertFromUTCTimeStamp
    (1111111111,
     GetUTCOffset(),
     OUTPUT dDate,
     OUTPUT iTime).

MESSAGE dDate iTime
   VIEW-AS ALERT-BOX
   TITLE "DEVELOPER DEBUG MESSAGE":u.

A simpler solution is:

Assuming that jmsTime is a decimal variable with a time in elapsed milliseconds since jan 1, 1970 00:00:00.000 GMT,

def var dtz as datetime-tz no-undo.

dtz = add-interval (datetime-tz (1, 1, 1970, 0, 0, 0, 0, 0), int64 (jmsTime), "milliseconds").

Now that you have a datetime with timezone (set to GMT), you can display it using whatever timezone you wish.

Also, the SESSION:TIMEZONE attribute contains the current value of the current session's timezone offset in minutes from GMT.

and the SESSION:DISPLAY-TIMEZONE attribute controls the timezone used for display formatting purposes when the format string for a datetime-tz value does not specify a timezone offset.

My examples were from legacy 9.1x code (without datetime) so Gus's example would be a better solution under 10.x and higher. 
However, under 10.2B my SESSION:DISPLAY-TIMEZONE and SESSION:TIMEZONE are always ? and ? so I still use the API to get the info from the OS. 

I also get ? for those. Looks like there might be a bug -- I thought it was

supposed to be initialized to the session's time zone.

However, if I set a value for the session:display-timezone, it affects the

displayed results.

def var jmstime as decimal.

def var dtz as datetime-tz.

jmstime = 1272551602123.

dtz = add-interval (datetime-tz (1, 1, 1970, 0, 0, 0, 0, 0),

int64(jmstime), "milliseconds").

display dtz format "99/99/99 hh:mm:ss.sss"

gives

04/29/10 10:33:22.123

This is right for the EST5EDT timezone where I am. If I then set the

display-timezone to 60 (which corresponds to GMT + 1), I get

04/29/10 15:33:22.123

Which is what it is supposed to do.

-gus

On 4/29/10 3:01 PM, "progresscommunities"

The TIMEZONE ABL function gives you the session's timezone.  SESSION:TIMEZONE is for telling the AVM what to use when evaluating the NOW, TODAY, TIME, MTIME, and TIMEZONE functions.

Robin

True enough. I had forgotten the timezone function. That returns -240 for

EST5EDT, which is just what I would have expected. Why does session:timezone

return ? instead of -240?

I'm confuzed.

Why don't the session:display-timezone and session:timezone get initialized

to the same value that the timezone function returns?

-gus

On 4/29/10 4:41 PM, "progresscommunities"

I don't remember why it was implemented this way, but the behvior for DISPLAY-TIMEZONE goes against the doc, which does say it defaults to the session's timezone.  We keep track of whether these attributes are set internally so that we do the right thing, but I don't see a reason why a query on the attribute shouldn't return the default (like SESSION:CPSTREAM, etc).  So I agree there's a bug here.

If it's a bug, does anybody from progress cares about?

I just logged a bug for this (DISPLAY-TIMEZONE and TIMEZONE attributes on the SESSION handle should return the session's time zone if not set).

Regards,

Robin

It is a trivial bug, if it even is a bug at all, which is not entirely

clear. I was involved in the design of the datetime and datetime-tz stuff,

but I don't remember if we intended that when session:timezone returns ?

that means you haven't changed it.

As Robin pointed out, the timezone function with no arguments returns the

current session's timezone setting.

Both session:display-timezone and session:timezone can be changed from their

initial setting of ? to some other value of your choosing. When they have

not been changed and have the value ?, you still get the right behaviour,

namely that the current' session's timezone is used.

You could perhaps do

session:timezone = timezone.

session:display-timezone = timezone.

However, there is no need to do this at all, as far as I can see.

-gus

On 4/30/10 8:39 AM, "progresscommunities"