Converting DATETIME and DATETIME-TZ from UTC to LOCAL timezone on days when daylight saving start and end is wrong

On dates of daylight saving start, during daylight saving hours, converted time is incorrect.
On dates of daylight saving end, during daylight saving hours, converted time-zone is incorrect.
On dates of daylight saving end, during standard-hours, converted time is one hour off.

On a Windows machine with a timezone set to '(UTC+01:00) Amsterdam, Berlin, Bern, Rome, Stockholm, Vienna' the following code produces incorrect results:

DEFINE VARIABLE dtzUtc   AS DATETIME-TZ NO-UNDO.
DEFINE VARIABLE dtLocal  AS DATETIME    NO-UNDO.
DEFINE VARIABLE dtzLocal AS DATETIME-TZ NO-UNDO.

// Date of summertime start 27/3/2022, during summertime hours INCORRECT time - correct timezone
dtzUtc   = DATETIME-TZ('2022-03-27T01:30:00.000+00:00').
dtLocal  = DATETIME(ISO-DATE(dtzUtc)).
dtzLocal = DATETIME-TZ(DATETIME(ISO-DATE(dtzUtc))).

MESSAGE 
    //dtLocal SKIP dtzLocal SKIP(2) 
   'Date of summertime start 27/3/2022, during summertime hours' SKIP 
   'INCORRECT time - correct timezone' SKIP(1) 
   SKIP 'UTC:    ' ISO-DATE(dtzUtc)      'UTC'
   SKIP 'DIFF:' INTERVAL(dtLocal    ,dtzUtc,'minutes') ISO-DATE(dtLocal)     'LOCAL-DATETIME'   
   SKIP 'DIFF:' INTERVAL(dtzLocal   ,dtzUtc,'minutes') ISO-DATE(dtzLocal)    'LOCAL-DATETIME-TZ'
   VIEW-AS ALERT-BOX INFO BUTTONS OK.
   
// INCORRECT time - correct timezone 
// UTC:      2022-03-27T01:30:00.000+00:00 UTC 
// DIFF: -60 2022-03-27T02:30:00.000 LOCAL-DATETIME 
// DIFF: -60 2022-03-27T02:30:00.000+02:00 LOCAL-DATETIME-TZ   

// But should be:
// UTC:    2022-03-27T01:30:00.000+00:00 UTC 
// DIFF: 0 2022-03-27T03:30:00.000 LOCAL-DATETIME 
// DIFF: 0 2022-03-27T03:30:00.000+02:00 LOCAL-DATETIME-TZ

 

DEFINE VARIABLE dtzUtc AS DATETIME-TZ       NO-UNDO.
DEFINE VARIABLE dtLocal AS DATETIME         NO-UNDO.
DEFINE VARIABLE dtzLocal AS DATETIME-TZ     NO-UNDO.

// Date of summertime end 30/10/2022, during summertime hours correct time - INCORRECT timezone
dtzUtc   = DATETIME-TZ('2022-10-30T00:30:00.000+00:00').
dtLocal  = DATETIME(ISO-DATE(dtzUtc)).
dtzLocal = DATETIME-TZ(DATETIME(ISO-DATE(dtzUtc))).

MESSAGE 
    //dtLocal SKIP dtzLocal SKIP(2) 
   'Date of summertime end 30/10/2022, during summertime hours' SKIP 
   'correct time - INCORRECT timezone' SKIP(1) 
   SKIP 'UTC:    ' ISO-DATE(dtzUtc)      'UTC'
   SKIP 'DIFF:' INTERVAL(dtLocal    ,dtzUtc,'minutes') ISO-DATE(dtLocal)     'LOCAL-DATETIME'   
   SKIP 'DIFF:' INTERVAL(dtzLocal   ,dtzUtc,'minutes') ISO-DATE(dtzLocal)    'LOCAL-DATETIME-TZ'
   VIEW-AS ALERT-BOX INFO BUTTONS OK.
   
// correct time - INCORRECT timezone 
// UTC:     2022-10-30T00:30:00.000+00:00 UTC 
// DIFF: 0 2022-10-30T02:30:00.000 LOCAL-DATETIME 
// DIFF: 60 2022-10-30T02:30:00.000+01:00 LOCAL-DATETIME-TZ   

// But should be:
// UTC:     2022-10-30T00:30:00.000+00:00 UTC 
// DIFF:  0 2022-10-30T02:30:00.000 LOCAL-DATETIME 
// DIFF:  0 2022-10-30T02:30:00.000+02:00 LOCAL-DATETIME-TZ

 

DEFINE VARIABLE dtzUtc AS DATETIME-TZ       NO-UNDO.
DEFINE VARIABLE dtLocal AS DATETIME         NO-UNDO.
DEFINE VARIABLE dtzLocal AS DATETIME-TZ     NO-UNDO.

// Date of summertime end 30/10/2022, during summertime hours INCORRECT time - correct timezone
dtzUtc   = DATETIME-TZ('2022-10-30T01:30:00.000+00:00').
dtLocal  = DATETIME(ISO-DATE(dtzUtc)).
dtzLocal = DATETIME-TZ(DATETIME(ISO-DATE(dtzUtc))).

MESSAGE 
    //dtLocal SKIP dtzLocal SKIP(2) 
   'Date of summertime end 30/10/2022, during summertime hours' SKIP 
   'INCORRECT time - correct timezone' SKIP(1) 
   SKIP 'UTC:    ' ISO-DATE(dtzUtc)      'UTC'
   SKIP 'DIFF:' INTERVAL(dtLocal    ,dtzUtc,'minutes') ISO-DATE(dtLocal)     'LOCAL-DATETIME'   
   SKIP 'DIFF:' INTERVAL(dtzLocal   ,dtzUtc,'minutes') ISO-DATE(dtzLocal)    'LOCAL-DATETIME-TZ'
   VIEW-AS ALERT-BOX INFO BUTTONS OK.
   
// INCORRECT time - correct timezone 
// UTC:     2022-10-30T01:30:00.000+00:00 UTC 
// DIFF: 60 2022-10-30T03:30:00.000 LOCAL-DATETIME 
// DIFF: 60 2022-10-30T03:30:00.000+01:00 LOCAL-DATETIME-TZ  

// But should be:
// UTC:     2022-10-30T01:30:00.000+00:00 UTC 
// DIFF:  0 2022-10-30T02:30:00.000 LOCAL-DATETIME 
// DIFF:  0 2022-10-30T02:30:00.000+01:00 LOCAL-DATETIME-TZ

 

The Unix/Linux "date" command does the same conversions correctly.
 

This is expected behavior.  The DATETIME/DATETIME-TZ data types are not 'locale' aware. The time zone portion of a DATETIME-TZ is simply an offset from UTC. When converting from a DATETIME to a DATETIME-TZ, the AVM determines whether or not the local date and time are during daylight savings on the given system, and assign the UTC offset accordingly.

Conversions between DATETIME and DATETIME-TZ cannot be relied upon during the daylight savings transition hours.  This is a product limitation.   A documentation defect (CONT-27582) has been logged to document this behaviour in the official documentation.  

To request a change in this behaviour, please log an enhancement on the ideas community.  For detailed information on how to submit an Idea, please refer to article How to submit an enhancement request for a Progress product?  
 

None at this time.

 

• Use the Unix 'date' command with an INPUT STREAM.  For example:

FUNCTION UTCDtzToLocalDtzUsingUnixDate RETURNS DATETIME-TZ ( pdtzUTCTime AS DATETIME-TZ) :
  DEFINE VARIABLE dtzLocalTime AS DATETIME-TZ       NO-UNDO.
  IF OPSYS = 'UNIX' THEN DO:
    INPUT STREAM sThrough THROUGH VALUE(SUBSTITUTE('date -d &1 +%Y-%m-%dT%H:%M:%S%:z',ISO-DATE(pdtzUTCTime))).
        IMPORT STREAM sThrough dtzLocalTime.
    INPUT STREAM sThrough CLOSE.
  END.
  RETURN dtzLocalTime.
...

 

• Write an ABL function to convert from UTC to Daylight Savings Time:

// Procedure to convert UTC to local time with DST transitions
SESSION:DATE-FORMAT = "ymd".

DEFINE VARIABLE dtzUtc1   AS DATETIME-TZ NO-UNDO.
DEFINE VARIABLE dtzLocal1 AS DATETIME-TZ NO-UNDO.

FUNCTION UTCDtzToLocalDtz RETURNS DATETIME-TZ ( pdtzUTCTime AS DATETIME-TZ) :
    // Converts a UTC DATETIME-TZ value to local time accounting for Daylight savings time    
    // 27/03/2022 01:00 DST Summer time start moves clock 1 hour forward
    // 30/10/2022 02:00 DST Summer time end, Standard time start moves clock 1 hour back        
    
    DEFINE VARIABLE dtzLocalTime AS DATETIME-TZ NO-UNDO.
        
    dtzLocalTime = ADD-INTERVAL(DATETIME-TZ(DATETIME(ISO-DATE(ADD-INTERVAL(pdtzUTCTime,INTERVAL(pdtzUTCTime,DATETIME-TZ(DATETIME(ISO-DATE(pdtzUTCTime))),'minutes'),'minutes')))),INTERVAL(pdtzUTCTime,DATETIME-TZ(DATETIME(ISO-DATE(ADD-INTERVAL(pdtzUTCTime,INTERVAL(pdtzUTCTime,DATETIME-TZ(DATETIME(ISO-DATE(pdtzUTCTime))),'minutes'),'minutes')))),'minutes'),'minutes').
    RETURN dtzLocalTime.   
END FUNCTION.

dtzUtc1   = DATETIME-TZ('2022-01-15T15:00:00.000+00:00'). 
dtzLocal1 = UTCDtzToLocalDtz(dtzUtc1).
MESSAGE "DATETIME-TZ : " SKIP "UTC            : " dtzUtc1 SKIP "Local Time : " dtzLocal1 VIEW-AS ALERT-BOX.
dtzUtc1   = DATETIME-TZ('2022-07-15T15:00:00.000+00:00').
dtzLocal1 = UTCDtzToLocalDtz(dtzUtc1).
MESSAGE "DATETIME-TZ : " SKIP "UTC            : " dtzUtc1 SKIP "Local Time : " dtzLocal1 VIEW-AS ALERT-BOX.
dtzUtc1   = DATETIME-TZ('2022-03-27T00:30:00.000+00:00').
dtzLocal1 = UTCDtzToLocalDtz(dtzUtc1).
MESSAGE "DATETIME-TZ : " SKIP "UTC            : " dtzUtc1 SKIP "Local Time : " dtzLocal1 VIEW-AS ALERT-BOX.
dtzUtc1   = DATETIME-TZ('2022-03-27T01:30:00.000+00:00'). 
dtzLocal1 = UTCDtzToLocalDtz(dtzUtc1).
MESSAGE "DATETIME-TZ : " SKIP "UTC            : " dtzUtc1 SKIP "Local Time : " dtzLocal1 VIEW-AS ALERT-BOX.
dtzUtc1   = DATETIME-TZ('2022-10-30T00:30:00.000+00:00'). 
dtzLocal1 = UTCDtzToLocalDtz(dtzUtc1).
MESSAGE "DATETIME-TZ : " SKIP "UTC            : " dtzUtc1 SKIP "Local Time : " dtzLocal1 VIEW-AS ALERT-BOX.
dtzUtc1   = DATETIME-TZ('2022-10-30T01:30:00.000+00:00'). 
dtzLocal1 = UTCDtzToLocalDtz(dtzUtc1).
MESSAGE "DATETIME-TZ : " SKIP "UTC            : " dtzUtc1 SKIP "Local Time : " dtzLocal1 VIEW-AS ALERT-BOX. 

 

Progress Article(s):
000142352, How to submit an enhancement request for a Progress product?