1
0
-1

Hi Freaks

Just a simple question (smile) 
How to get the correct time from another timezone, if the day is not today?

Example:

I do have two timestamps, both created in germany

1) 2020101T180000 (1 january of 2020, 6 PM CET)
1) 2020501T180000 (1 may of 2020 , 6 PM CEST)

Now I want to convert them to UTC

The correct values would be:

1) 2020101T170000Z (1 january of 2020, 5 PM UTC)
1) 2020501T160000Z (1 may of 2020 , 4 PM UTC)

Problem is, that the difference is not a constant, but depends on "daylight saving"

With $nlstimezone set to UTC, I got only $datim as the currenct time in UTC

$nlstimezone
 *Returned by the Proc functions $clock, $date, and $datim
 *Displayed in fields with data types date, time, or combined date and time
 *Stored and retrieved in the database
 *Exchanged when using XML, call-in, or call-out

This does not include formating of datetime into a string, so
v_TIMESTAMP = "020101180000"
$nlstimezone="UTC"
$1 = "%%v_TIMESTAMP%%%"
does not give the correct value (as it doesn't convert at all)
As I want to convert a timestamp to the correct "Zulu" time with global procedures, I can't use field or database access

Any idea how to simple convert a timestamp to the correct time in another timezone?

Regards Ingo




    CommentAdd your comment...

    3 answers

    1.  
      1
      0
      -1

      So, here is a corrected version:

      !!! crash, if a timzone is "CLASSIC" !!!

      ; Convert datetime(C=combined) from "timezone from" to "timezone to"
      ; $status
      ;  0  done, day is the same
      ;  1  done, day changed
      ; <0  error
      ENTRY SF_TZ_CNV_C
        returns datetime
        params
          datetime v_DATIM_FROM :IN ; a datetime
          string   v_TZ_FROM    :IN ; timezone from
          string   v_TZ_TO      :IN ; timezone to
          string   v_PARA       :IN ; furher parameters if needed
        endparams
        variables
          datetime v_DATIM_UTC
          datetime v_DATIM_TO
          datetime v_DATIM_XUTC
          numeric  v_DIFF
          string   v_TXT
          string   v_TZ_MEM1,v_TZ_MEM2
          numeric  v_DD
          string   v_TMP
        endvariables
      
        IF(v_DATIM_FROM=="") 
          $status = 0
          RETURN("")
        ENDIF
        ;defaults
        IF(v_TZ_FROM=="") v_TZ_FROM = $nlstimezone
        IF(v_TZ_TO=="")   v_TZ_TO   = $nlstimezone
      
        ; make the function case independent
        v_TZ_FROM = $uppercase(v_TZ_FROM)
        v_TZ_TO   = $uppercase(v_TZ_TO)
      
        ; Convenience: Replace "Z" for "Zulu time" (used in XML) by "UTC"
        IF(v_TZ_FROM=="Z") v_TZ_FROM = "UTC"
        IF(v_TZ_TO=="Z")   v_TZ_TO   = "UTC"
      
        IF(v_TZ_FROM==v_TZ_TO) ; Nothing to do
          RETURN(v_DATIM_FROM)
        ENDIF
      
        IF(0) ; Check timezones (performance, as I got always the whole list of timezones?)
          getitem/id v_TMP,$nlstimezonelist,v_TZ_FROM
          IF($status<=0)
            putmess "SF_TZ_CNV_C:Timezone from '%%v_TZ_FROM%%%' is not a vaild timezone!"
            $status = <UPROCERR_ARGUMENT>
            RETURN("")
          ENDIF
          getitem/id v_TMP,$nlstimezonelist,v_TZ_TO
          IF($status<=0)
            putmess "SF_TZ_CNV_C:Timezone to '%%v_TZ_TO%%%' is not a vaild timezone!"
            $status = <UPROCERR_ARGUMENT>
            RETURN("")
          ENDIF
        ENDIF
      
      
        ; Memorize the old value
        v_TZ_MEM1 = $nlsinternaltime
        v_TZ_MEM2 = $nlstimezone
      
        IF(v_TZ_FROM=="UTC")
          v_DATIM_UTC = v_DATIM_FROM
        ELSE
          $nlsinternaltime = "UTC"               ; from UTC
          $nlstimezone     = v_TZ_FROM           ; to timezone TZ_FROM
          $$TL_DATIM_ISO2 = v_DATIM_FROM
      ;!!! Crash if v_DATIM_FROM ="CLASSIC" !!!!
          v_TXT = "%%$$TL_DATIM_ISO2%%%"         ; Convert UTC->TZ_FROM / Format to "yyyymmddhhnnsstt"
          v_DATIM_XUTC = v_TXT                   ; TZ_FROM (wrong direction)
          v_DIFF = v_DATIM_XUTC-v_DATIM_FROM  ; Difference (TZ_FROM-UTC)
          v_DATIM_UTC = v_DATIM_FROM - v_DIFF ; TZ_FROM-(TZ_FROM-UTC) = UTC
        ENDIF
      
        ;v_DATIM_UTC is now UTC
      
        IF(v_TZ_TO=="UTC")
          v_DATIM_TO = v_DATIM_UTC
        ELSE
          $nlsinternaltime = "UTC"               ; from UTC 
          $nlstimezone = v_TZ_TO                 ; to timezone TZ_TO
          $$TL_DATIM_ISO2 = v_DATIM_UTC
          v_TXT = "%%$$TL_DATIM_ISO2%%%"         ; Convert UTC->TZ_TO / Format to "yyyymmddhhnnsstt"
          v_DATIM_TO = v_TXT                     ; TZ_TO
        ENDIF
      
        $nlsinternaltime = v_TZ_MEM1
        $nlstimezone     = v_TZ_MEM2
      
        IF(v_DATIM_FROM[D]==v_DATIM_TO[D])
          $status = 0
        ELSE
          $status = 1
        ENDIF
        RETURN(v_DATIM_TO)
      END ; SF_TZ_CNV_C

      Still the "bug" with the time at a transit "normal" ↔ "daylight saving"
      And the check if the timezone is correct is switched off

      Ingo

      1. Ingo Stiller

        So, a next version.
        Hopefully without crash (smile)

        ;
        ;SF_TZ_CNV_C(<datim>,<tz_from>,<tz_to>,<para>)
        ;
        ; Convert datetime(C=combined) from "timezone from" to "timezone to"
        ; $status
        ;  0  done, day is the same
        ;  1  done, day changed
        ; <0  error
        ;ENTRY SF_TZ_CNV_C
          returns datetime
          params
            datetime v_DATIM_FROM :IN ; a datetime
            string   v_TZ_FROM    :IN ; timezone from
            string   v_TZ_TO      :IN ; timezone to
            string   v_PARA       :IN ; furher parameters if needed
          endparams
          variables
            datetime v_DATIM_UTC
            datetime v_DATIM_TO
            datetime v_DATIM_XUTC
            numeric  v_DIFF
            string   v_TXT
            string   v_TZ_MEM1,v_TZ_MEM2
            numeric  v_DD
            string   v_TMP
          endvariables
        
          IF(v_DATIM_FROM=="") 
            $status = 0
            RETURN("")
          ENDIF
          ;defaults
          IF(v_TZ_FROM=="") v_TZ_FROM = $nlstimezone
          IF(v_TZ_TO=="")   v_TZ_TO   = $nlstimezone
        
          ; make the function case independent
          v_TZ_FROM = $uppercase(v_TZ_FROM)
          v_TZ_TO   = $uppercase(v_TZ_TO)
        
          ; if "CLASSIC" got the current, local  timezone
          ; else UnifAce will crash after 
          ;   $nlstimezone = "classic"
          ;   $$TL_DATIM_ISO2 = v_DATIM_FROM
          ;   v_TXT = "%%$$TL_DATIM_ISO2%%%"        ; <-Crash
        
          IF(v_TZ_FROM=="CLASSIC")
            v_TZ_FROM = $uppercase(SF_TZ_GET_LOC_TZ(""))
          ENDIF
          IF(v_TZ_TO=="CLASSIC")
            v_TZ_TO = $uppercase(SF_TZ_GET_LOC_TZ(""))
          ENDIF
        
          ; Convenience: Replace "Z" for "Zulu time" (used in XML) by "UTC"
          IF(v_TZ_FROM=="Z") v_TZ_FROM = "UTC"
          IF(v_TZ_TO=="Z")   v_TZ_TO   = "UTC"
        
          IF(v_TZ_FROM==v_TZ_TO) ; Nothing to do
            RETURN(v_DATIM_FROM)
          ENDIF
        
          IF(0) ; Check timezones (performance, as I got always the whole list of timezones?)
            getitem/id v_TMP,$nlstimezonelist,v_TZ_FROM
            IF($status<=0)
              putmess "SF_TZ_CNV_C:Timezone from '%%v_TZ_FROM%%%' is not a vaild timezone!"
              $status = <UPROCERR_ARGUMENT>
              RETURN("")
            ENDIF
            getitem/id v_TMP,$nlstimezonelist,v_TZ_TO
            IF($status<=0)
              putmess "SF_TZ_CNV_C:Timezone to '%%v_TZ_TO%%%' is not a vaild timezone!"
              $status = <UPROCERR_ARGUMENT>
              RETURN("")
            ENDIF
          ENDIF
        
        
          ; Memorize the old value
          v_TZ_MEM1 = $nlsinternaltime
          v_TZ_MEM2 = $nlstimezone
        
          IF(v_TZ_FROM=="UTC")
            v_DATIM_UTC = v_DATIM_FROM
          ELSE
            $nlsinternaltime = "UTC"               ; from UTC
            $nlstimezone     = v_TZ_FROM           ; to timezone TZ_FROM
            $$TL_DATIM_ISO2 = v_DATIM_FROM
            v_TXT = "%%$$TL_DATIM_ISO2%%%"         ; Convert UTC->TZ_FROM / Format to "yyyymmddhhnnsstt"
            v_DATIM_XUTC = v_TXT                   ; TZ_FROM (wrong direction)
            v_DIFF = v_DATIM_XUTC-v_DATIM_FROM  ; Difference (TZ_FROM-UTC)
            v_DATIM_UTC = v_DATIM_FROM - v_DIFF ; TZ_FROM-(TZ_FROM-UTC) = UTC
          ENDIF
        
          ;v_DATIM_UTC is now UTC
        
          IF(v_TZ_TO=="UTC")
            v_DATIM_TO = v_DATIM_UTC
          ELSE
            $nlsinternaltime = "UTC"               ; from UTC 
            $nlstimezone = v_TZ_TO                 ; to timezone TZ_TO
            $$TL_DATIM_ISO2 = v_DATIM_UTC
            v_TXT = "%%$$TL_DATIM_ISO2%%%"         ; Convert UTC->TZ_TO / Format to "yyyymmddhhnnsstt"
            v_DATIM_TO = v_TXT                     ; TZ_TO
          ENDIF
        
          $nlsinternaltime = v_TZ_MEM1
          $nlstimezone     = v_TZ_MEM2
        
          IF(v_DATIM_FROM[D]==v_DATIM_TO[D])
            $status = 0
          ELSE
            $status = 1
          ENDIF
          RETURN(v_DATIM_TO)
        END ; SF_TZ_CNV_C
        
        



      2. Roger Wallin


        Hi Ingo,
        Thanks for the code.
        Unfortunately I didn't get it working at first try.
        What does
        SF_TZ_GET_LOC_TZ("")
        do?
        How is the global parameter 
        $$TL_DATIM_ISO2
        defined?  Combined date and time, DIS(yyyymmddhhnnsstt)?

        Regards RogerW.

      3. Ingo Stiller

        Hi Roger

        Here the code for SF_TZ_GET_LOC_TZ
        It "just" get the/a local timezone (first best timezone ID)
        $$TZ_LOC_TZ is a simple string to hold the local ID.
        As you can change the local timezone only by modifying the OS-time and/or by assignment setting this should by stable for a UnifAce session
        And yes, $$TL_DATIM_ISO2 is  "C" -type with DIS(yyyymmddhhnnsstt)

        ;
        ; SF_TZ_GET_LOC_TZ(<para>)
        ;
        ; Get the/a local timezone. 
        ;
          returns string
          params
            string  v_PARA:IN
          endparams
          variables
            datetime v_DATIM1
            datetime v_CUR_DATIM
            datetime v_DATIM1_L,v_DATIM1_H
            string   v_TZ_MEM1,v_TZ_MEM2
            string   v_LST
            string   v_ID
            string   v_DESC
          endvariables
        
          IF($$TZ_LOC_TZ!="")
            IF($$TZ_LOC_TZ=="·*ERR")
              $status = -2
              RETURN("")
            ELSE
              $status = 0 
              RETURN($$TZ_LOC_TZ)
            ENDIF
          ENDIF
        
          v_TZ_MEM1 = $nlsinternaltime
          v_TZ_MEM2 = $nlstimezone
        
          $nlsinternaltime = "classic"
          $nlstimezone     = "classic"
          v_DATIM1 = $datim
          v_DATIM1_L = v_DATIM1-1n
          v_DATIM1_H = v_DATIM1+1n
          v_LST = $nlstimezonelist
          FORLIST/id v_ID,v_DESC in v_LST
            IF($length(v_ID)<=3)
              $nlstimezone = v_ID
              v_CUR_DATIM = $datim
              IF(v_DATIM1_L<=v_CUR_DATIM && v_CUR_DATIM<=v_DATIM1_H)
                GOTO TAG_FOUND
              ENDIF
            ENDIF
          ENDFOR
          FORLIST/id v_ID,v_DESC in v_LST
            $nlstimezone = v_ID
            v_CUR_DATIM = $datim
            IF(v_DATIM1_L<=v_CUR_DATIM && v_CUR_DATIM<=v_DATIM1_H)
              GOTO TAG_FOUND
            ENDIF
          ENDFOR
          v_ID = ""
         TAG_FOUND:
        
          $nlsinternaltime = v_TZ_MEM1
          $nlstimezone     = v_TZ_MEM2
        
        
          IF(v_ID=="")
            $$TZ_LOC_TZ = "·*ERR"
            $status = -2
          ELSE
            $$TZ_LOC_TZ = v_ID
            $status = 0
          ENDIF
          RETURN(v_ID)
        END ; SF_TZ_GET_LOC_TZ

        Regards Ingo


      4. Roger Wallin

        Hi Ingo,

        The code is working now. Actually I don't need the code now, but one never knows. 
        The $nlstimezonelist doesn't seem to contain summer-time, ie. EET and CET are available but not EEST neither CEST. Perhaps that's natural as CEST is the same as EET and so on..... 

        Regards RogerW.

      5. Ingo Stiller

        HI Roger
        CET "includes" CEST (smile)
        IF you convert CET↔UTC you will see, that the difference is 2 hours i.e CET actual is CEST
        BTW: The abrevations CET, CEST, EET, ... are just convenience names but not "official".
        Ingo

      6. Roger Wallin

        I converted 
        UTC 25.1.2020 10:01 
        UTC 25.8.2020 10:01 
        to CET.
        Nice, now I understand your comment "CET includes CEST".

        Regards RogerW.


      CommentAdd your comment...
    2.  
      1
      0
      -1

      Hi Roger

      Just trying to use $clock(<source>) with $nlstimezone

      $nlsformat="classic"
      $nlstimezone="classic"
      v_TIME = $clock("180000")

      $nlsformat="nlslocale"
      $nlstimezone="UTC"
      v_TIME = $clock("180000")

      $nlsformat="nlslocale"
      $nlstimezone="CET"
      v_TIME = $clock("180000")

      $nlsformat="classic"
      $nlstimezone="classic"

      the result is always the same "180000"
      How does $clock work with $nlstimezone?

      9.4.01

      Additional option for overriding the default behavior if the time zone has been defined (using $nlstimezone or $NLS_TIME_ZONE)


      Ingo

      1. Roger Wallin

        This works

        $nlstimezone = "UTC"
        testansv = "%%$clock%%%"

        ie. at 17:08 it shows 14:08 in Finland.

        apparently not working as you give some own <source>....

        Regards Roger

      2. Ingo Stiller

        Hi Roger

        That's the same then $datim (smile)
        But as UnifAce mention $clock,$date and $datime should "recognize" the timezone setting.
        If this is only true without a source all of the functions are useless for converting a time, date, datetime (sad)

        Daniel Iseli: Any idea to convert a date from one timezone to another?

        Ingo

      3. Daniel Iseli

        Hi Ingo,

        When you also use $nlsinternaltime = "UTC" then you should be able to do some time zone calculations.

        $nlsinternaltime = "UTC"
        $nlstimezone = "UTC"
        putmess vTime
        putmess "%%vTime%%%"
        $nlstimezone = "CET"
        putmess vTime
        putmess "%%vTime%%%"
        $nlsinternaltime = "classic"
        $nlstimezone = "classic"

        In case vTime is "01012020180000" (1-January-2020 18:00) then the external values (%%vTime%%%) are:

        • UTC: 1-January-2020 18:00
        • CET: 1-January-2020 19:00

        And when vTime is "01052020180000" (1-May-2020 18:00) then the external values are:

        • UTC: 1-May-2020 18:00
        • CET: 1-May-2020 20:00

        With this you should be able to reverse the values from CET to UTC.

        If you would store the date values in the database from the start using $nlsinternaltime = "UTC" then this would be even easier, since in the database you then always have the UTC value and with $nlstimezone you then can display the value in the required time zone. In the case at hand you already have time zone dependent data in the database and that makes things a bit more complicated.

        I hope this helps.

        Daniel

      4. Ingo Stiller


        Hi Daniel

        So I build a function wich converts a datetime variable to text an UTC to another timezone:

        ENTRY LF_CNV_UTC_TZ
          returns string
          params
            datetime v_DATIM :IN
            string   v_TZ    :IN
          endparams
          variables
            string  v_TXT
            string  v_TZ_R1,v_TZ_R2
          endvariables
          v_TZ_R1 = $nlsinternaltime
          v_TZ_R2 = $nlstimezone
          $nlsinternaltime = "UTC"
          $nlstimezone = v_TZ
          v_TXT = "%%v_DATIM%%%"
          $nlsinternaltime = v_TZ_R1
          $nlstimezone = v_TZ_R2
          $status = 0
          RETURN(v_TXT)
        END
        
        


        To draw backs:

        1) The opposite conversion (TZ→UTC) does not work that simple

        2) the result is a text, not datetime

        To solve 1) I need the time difference in minutes
        (Some countries do have an offset of n hours and 30 minutes ...)
        But subtract on text from another doesn't work ...

        So I have to solve 2)
        This looks easy, but it isn't
        Which datetime-formats uses the current UnifAce application?
        The text could have anny format.
        I will try to use a global variable with predefined format

        When all this is working, it should possible to create a function
        LF_CNH_TZ1_TO_TZ2

        Isn't this a task for the programm language?

        Ingo


      5. Ingo Stiller

        So, I hope I got the (almost) final solution:

        ; convert datetime from timezone 1 to timezone 2
        ENTRY LF_CNV_TZ1_TZ2
          returns datetime
          params
            datetime v_DATIM_TZ1 :IN ; datetime in timezone 1
            string   v_TZ1       :IN ; from timezone 1
            string   v_TZ2       :IN ; to timezone 2
          endparams
          variables
            datetime v_DATIM_TZM ; datetime "in the middle"
            datetime v_DATIM_TZ2 ; Convert datetime
            datetime v_DATIM2    ; 
            numeric  v_DIFF
            string   v_TXT
            string   v_TZ_R1,v_TZ_R2
          endvariables
        
          IF(v_TZ1=="") v_TZ1 = $nlstimezone
          IF(v_TZ2=="") v_TZ2 = $nlstimezone
        
          IF(v_TZ1==v_TZ2)
            RETURN(v_DATIM_TZ1)
          ENDIF
        
          ; How to check, is a timezone is valid?
          ; and $nlstimezonelist is to slow for just a simple test, or?
          ;
          v_TZ_R1 = $nlsinternaltime
          v_TZ_R2 = $nlstimezone
        
          ; TZ1=>UTC
          IF(v_TZ1=="UTC")
            v_DATIM_TZM = v_DATIM_TZ1
          ELSE
            ;convert datetime from TZ1 first from UTC=>TZ1 (wrong direction)
            ; Does not work properly for dates in transit "normal time" <-> "daylight saving time", but who cares!?!?!
            $nlsinternaltime = "UTC" ; from UTC
            $nlstimezone = v_TZ1     ; to timezone TZ1
            $$TL_DATIM_ISO2 = v_DATIM_TZ1
            v_TXT = "%%$$TL_DATIM_ISO2%%%"
            v_DATIM2 = v_TXT ; TZ1  
            v_DIFF = v_DATIM2-v_DATIM_TZ1 ; Difference (TZ1-UTC)
            ; Correct the calculation 
            v_DATIM_TZM = v_DATIM_TZ1 - v_DIFF ; TZM = TZ1-(TZ1-UTC) = UTC
          ENDIF
        
          ;v_DATIM_TZM is now UTC
        
          ; UTC=>TZ2
          IF(v_TZ2=="UTC")
            v_DATIM_TZ2 = v_DATIM_TZM
          ELSE
            v_TZ_R1 = $nlsinternaltime
            v_TZ_R2 = $nlstimezone
            $nlsinternaltime = "UTC" ; from UTC 
            $nlstimezone = v_TZ2     ; to timezone TZ2
            $$TL_DATIM_ISO2 = v_DATIM_TZM
            v_TXT = "%%$$TL_DATIM_ISO2%%%"
            v_DATIM_TZ2 = v_TXT ; TZ2
          ENDIF
        
          $nlsinternaltime = v_TZ_R1
          $nlstimezone = v_TZ_R2
        
          $status = 0
          RETURN(v_DATIM_TZ2)
        END
        
        

        Not jet solved

        1. problem in transit between "normal" and "daylight saving" timezones
        2.  The procedure is slow(?) Will check (maybe) this with bigger testset

        And as I said in last comment, this should be a task for the program language, as that can use directly the NLS-utilities and C/C++

        Ingo

      6. Ingo Stiller

        Remarks to daylight saving:

        Assume a datetime of "2020032901300000" (CET 29 march 2020, 1:30 am)
        My default timetone is CET
        To convert first I have to convert first to a dummy CET datetime to get the diff
        $nlsinternaltime = "UTC"
        $nlstimezone = "CET"
        this gives me "2020032903300000" (CEST 29 march 2020, 3:30 am) *)
        which is a diff of 2 hours
        Now I subtract this diff from the original datetime
        so the reuslt is "2020032811300000" (UCT 28 march 2020, 11:30 pm)
        And this is wrong!
        Correct would be
        "2020032900300000" (UCT 29 march 2020, 0:30 am)
        The problem is, that the dummy calculation is assuming, that the time is now in CEST and not CET
        A solution would be to check, if the back conversion results in the old value, otherwise one have to adjust the diff ....


        BTW:

        $$TL_DATIM_ISO2 is a global datetime variable with format
        "yyyymmddhhnnsstt"



      CommentAdd your comment...
    3.  
      1
      0
      -1

      Strange, Roger send me an answer via noreply@uniface.com, but I didn't see them here!?!?

      Rogger suggest du use database fields and the write mit timezone="UTC"
      For two reasons this will not work (smile)

      1) I try to implement the conversion as an globale procedure

      2) The data is already written, millions of rows. All date/time fields use the local time (i.e. CET/CEST)

      My job is to use this data und create CRS(common reporting standard) XML-Files.
      [or better to modify an old programm to the new CRS version]
      And the (only german?) tax authority  wants to see date/time in UTC ("Z")
      When compiling the XML, I have to convert all fields accorging to CRS and XML standards
      Using a general offset (say 1 hour UTC→CET) does not work, as some dates are written in winter (CET) in some in sommer (CEST) and UnifAce only use $nlstimezone for the current $datim and/or the whole XML (if using UnifAce XML-operations)

      So, how to find the offset for a arbirtary date?

      Ingo


      1. Roger Wallin

        Hi Ingo,

        yes I deleted it, as it went wrong in all kind of ways. First I didn't notice that you can't use the database, second my algorithm wasn't correct (sad).

        My database solution is something like this:
        actTimeZone = $nlstimezone
        $nlstimezone = "UTC"
        Store date into temporary database-table column
        $nlstimezone = actTimeZone
        retrieve date from temporary database-table column.

        Otherwise I don't think that you can solve it just in Uniface, you probably have to use some kind of database-procedure or web-service....

        Regards RogerW.

      CommentAdd your comment...