Ciao Knut,

AFAIK $datim on various platforms is already giving back current datetime, as minimum up to hundreds of second.

What about a simple global user defined function?

Something like:

returns string
params
    datetime    pDatim
endparams
variables
    string    vDatim
endvariables
if (pDatim = "") return("")
vDatim = pDatim
vDatim = $concat(vDatim[1,4],"-",vDatim[5,6],"-",vDatim[7,8],"T",vDatim[9,10],":",vDatim[11,12],":",vDatim[13,14],".",vDatim[15,17])
if ($length(vDatim) < 23) vDatim = $concat(vDatim, "000"[1,(23-$length(vDatim))]
vDatim = $concat(vDatim,"Z")
return(vDatim)

You can obviously refine it as you prefer...

Gianni

------------------------------
Gianni Sandigliano
IT
------------------------------

Similarly:- 

variables
struct v_struct
string v_xml
endvariables


v_struct = $newstruct
v_struct->date = $datim
structtoxml v_xml, v_struct
v_xml = $replace(v_xml,1,"<date>","",-1)
v_xml = $replace(v_xml,1,"</date>",".000Z",-1)

But I don't think either of these deal with the problem of removing timezone/DST from the $datim results. (UK is currently GMT anyway, so a practical test doesn't show me anything here.)

Regards, 

Iain

variables
struct v_struct
string v_xml
endvariables


v_struct = $newstruct
v_struct->date = $datim
structtoxml v_xml, v_struct
v_xml = $replace(v_xml,1,"<date>","",-1)
v_xml = $replace(v_xml,1,"</date>",".000Z",-1)

Hi Gianni / Iain,

Thank you both - I already have done something similar - but as Iain states - 
paraphrased - nothing deals with the TZ difference.   For example, EST here in the US
is (at the moment) 5 hrs behind GMT.  Other parts are 6-8 hrs behind.
That's why I was hoping there might have been anything / something ...

Alas - it doesn't look as there's anything....

Cheers,
Knut

Ciao Knut,

in both code cases it is needed to find current timezone and current offset to apply a proper delta of hours.

Few hints:
- Original page tip23 from Tony Marston HERE - "A Timezone conversion utility"
- Encapsulating a piece of Javascript in a service; see HERE

There is also possibility to get directly Zulu time encapsulating a powershell script:

[DateTime]::UtcNow.ToString('u').Replace('Z','UTC')

Hope it helps.

Gianni

[DateTime]::UtcNow.ToString('u').Replace('Z','UTC')

Hi Gianni,

Awesome!  And thank you!

I didn't know about that one.

Regards,
Knut

and then - of course - I could do something like this;

sql/data "select to_char(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'YYYY-MM-DD HH24:MM:SS') from dual", "$ORA"

pso_zulu_time = $replace($replace($result, 1, " ", "T", 1), 1, " ", "", -1)
pso_zulu_time = $concat(pso_zulu_time,".000Z")

And then it all works...

------------------------------
Knut Dybendahl
------------------------------

Yeah but wouldn't it be nice if, in these days of Uniface developing web based software and therefore potentially international applications, there were a data type of UTCdate that automatically formatted in client timezone, but stored in UTC, and a function to find UTC within the code itself? 

Ciao Knut,

I feel you should implement ticks to have a complete alignment with $datim. Considering systimestamp is giving back 6 digit (Are they called milions of seconds?) it should not be a problem.

It means adding FF2 to your mask definition, like:
sql/data "select to_char(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'YYYY-MM-DD HH24:MM:SS.FF2') from dual", "$ORA"

Regards,
Gianni

Hi Iain,

I feel OK for the function but which is the added value for a separate data type? 
It just a datetime...if you have both in the same entity you could call them LOCAL_DATETIME and UTC_DATETIME.

Just my 2 cents...

Best Regards,
Gianni

------------------------------
Gianni Sandigliano
IT
------------------------------

Hi

I miss if you mean the format or the real value.

You can use $nlsinternaltime

variables
    datetime VE1
    lineardatetime VTZ
endvariables

debug

$nlsinternaltime = "UTC"
VE1 = $datim
$nlsinternaltime = "classic"
VTZ = $datim - VE1

variables
    datetime VE1
    lineardatetime VTZ
endvariables

debug

$nlsinternaltime = "UTC"
VE1 = $datim
$nlsinternaltime = "classic"
VTZ = $datim - VE1

It's already there! Thanks Luis! :-)

My thought was to smooth the database updates, you'd have a specific data type which was converted to UTC on storage in the DB, and to local  time on retrieval (or format/deformat) thereby making it easier for developers to build an international database without having to manually implement the conversion. (And assigning a datetime to a UTCtime variable/field would perform the conversion implicitly. 
I will check out the $nlsinternaltime to see if this makes it easy  though. 

Regards, 

Iain

;
;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(v_TZ_FROM=="" || v_TZ_FROM=="CLASSIC")
    v_TZ_FROM = $uppercase(SF_TZ_GET_LOC_TZ(""))
  ENDIF
  IF(v_TZ_TO=="" || 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

Hi Ingo,

nice piece of software... thanks for sharing. What about those two lines using SF_TZ_GET_LOC_TZ("")?

Gianni

Hi Gianni

I wanted to have a small collection of functions that can do all these things, no matter what time zones they are and what someone requests.
Since uniface only supports calculations with time zones in a rudimentary way, the routines are a bit tricky :-)

Ingo

Here is the missing code.

----

;
; 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_TZ_LOC
    string   v_DESC
    numeric  v_OFFSET
    string   v_LST_TZ_BY_O
  endvariables

  ;$$S_SYS_LOC_TZ cached local time zone
  IF($$S_SYS_LOC_TZ!="")
    IF($$S_SYS_LOC_TZ=="·*ERR")
      $status = -2
      RETURN("")
    ELSE
      $status = 0
      RETURN($$S_SYS_LOC_TZ)
    ENDIF
  ENDIF

  v_LST_TZ_BY_O = SF_TZ_GET_TZ_LST("O","") ; get a list of all time zones, sorted by offset
  v_OFFSET = SF_TZ_GET_DIFF("","UTC","")   ; get the time difference to UTC
  v_TZ_LOC = $item("TZS",$item(v_OFFSET,v_LST_TZ_BY_O))
  IF(v_TZ_LOC!="") GOTO TAG_WEITER ; Found offset

  ; We have to do a little bit of research

  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_TZ_LOC,v_DESC in v_LST
    IF($length(v_TZ_LOC)<=3)
      $nlsinternaltime = "system"
      $nlstimezone = v_TZ_LOC
      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_TZ_LOC,v_DESC in v_LST
    $nlsinternaltime = "system"
    $nlstimezone = v_TZ_LOC
    v_CUR_DATIM = $datim
    IF(v_DATIM1_L<=v_CUR_DATIM && v_CUR_DATIM<=v_DATIM1_H)
      GOTO TAG_FOUND
    ENDIF
  ENDFOR
  v_TZ_LOC = ""
 TAG_FOUND:

  $nlsinternaltime = v_TZ_MEM1
  $nlstimezone     = v_TZ_MEM2

TAG_WEITER:
  IF(v_TZ_LOC=="")
    $$S_SYS_LOC_TZ = "·*ERR" ; not determinable
    $status = -2
  ELSE
    $$S_SYS_LOC_TZ = v_TZ_LOC ; cache the local time zone
    $status = 0
  ENDIF
  RETURN(v_TZ_LOC)
END ; SF_TZ_GET_LOC_TZ

----

;
;SF_TZ_GET_TZ_LST(<typ>,<para>)
; Typ
;  "S" = Short
;  "L" = Long
;  "O" = Sorted by offset
;  "M" = Military
;
  returns string
  params
    string  v_TYP:IN
    string  v_PARA:IN
  endparams
  variables
    datetime v_DATIM1
    datetime v_CUR_DATIM
    string   v_TZ_MEM1,v_TZ_MEM2
    numeric  v_OFFSET
    string   v_OFFSET_STR
    string   v_LST
    string   v_LST2
    string   v_LST3
    string   v_LST4
    string   v_LST6
    string   v_CUR_TZX
    string   v_CUR_TZS,v_CUR_TZA,v_CUR_TZA2,v_CUR_TZM
    string   v_ITEM
    string   v_M_LST1,v_M_LST2
  endvariables

  IF($$S_SYS_TZ_LSTL!="") GOTO TAG_WEITER

  v_M_LST1 = "ABCDEFGHIKLM"
  v_M_LST2 = "NOPQRSTUVWXY"

  $$S_SYS_TZ_LSTL = ""
  $$S_SYS_TZ_LSTS = ""
  $$S_SYS_TZ_LSTO = ""
  $$S_SYS_TZ_LSTM = ""

  v_ITEM = ""
  putitem/id v_ITEM,"TZS","UTC"
  putitem/id v_ITEM,"TZA","Etc/GMT"
  putitem/id v_ITEM,"OFH",0
  putitem/id v_ITEM,"DSC","Coordinated Universal Time"
  putitem/id $$S_SYS_TZ_LSTL,"UTC",v_ITEM
  putitem/id $$S_SYS_TZ_LSTS,"UTC",v_ITEM

  putitem/id $$S_SYS_TZ_LSTO,0,v_ITEM

  v_ITEM = ""
  putitem/id v_ITEM,"TZS","UCT"
  putitem/id v_ITEM,"TZA","Etc/GMT"
  putitem/id v_ITEM,"OFH",0
  putitem/id v_ITEM,"DSC","Coordinated Universal Time"
  putitem/id $$S_SYS_TZ_LSTL,"UCT",v_ITEM
  putitem/id $$S_SYS_TZ_LSTS,"UCT",v_ITEM

  putitem/id v_ITEM,"TZS","Z"
  putitem/id v_ITEM,"TZA","Etc/GMT"
  putitem/id v_ITEM,"OFH",0
  putitem/id v_ITEM,"DSC","Zulu Time"
  putitem/id $$S_SYS_TZ_LSTL,"Z",v_ITEM
  putitem/id $$S_SYS_TZ_LSTS,"Z",v_ITEM

  putitem/id $$S_SYS_TZ_LSTM,"Z",v_ITEM

  v_TZ_MEM1 = $nlsinternaltime
  v_TZ_MEM2 = $nlstimezone

  v_LST  = $nlstimezonelist
  v_LST2 = ""
  v_LST3 = ""
  v_LST4 = ""
  FORLIST/id v_CUR_TZX,v_CUR_TZA in v_LST

    $nlsinternaltime = "UTC"
    $nlstimezone     = "UTC"
    v_DATIM1 = $datim

    $nlsinternaltime = "system"
    $nlstimezone = v_CUR_TZX
    v_CUR_DATIM = $datim
    v_OFFSET = (v_CUR_DATIM-v_DATIM1)/1n ; Offset in minutes
    ;Rounding inaccuracies (time interval between $datim of the two requests)
    IF(v_OFFSET<0)
      v_OFFSET = $int(v_OFFSET-0.5)
    ELSE
      v_OFFSET = $int(v_OFFSET+0.5)
    ENDIF
    v_OFFSET = v_OFFSET/60 ; Offset in hours

    v_ITEM = ""
    ;IF($length(v_CUR_TZX)<=3)
    IF($scan(v_CUR_TZX,"/")==0)
      putitem/id v_ITEM,"TZS",v_CUR_TZX
    ELSE
      IF(v_CUR_TZA=="Etc/GMT")
        putitem/id v_ITEM,"TZS","UTC"
      ELSE
        putitem/id v_ITEM,"TZS",""
      ENDIF
    ENDIF

    putitem/id v_ITEM,"TZL",v_CUR_TZX
    IF(v_CUR_TZA!=v_CUR_TZX)
      putitem/id v_ITEM,"TZA",v_CUR_TZA
    ENDIF
    v_CUR_TZM = LFF_GET_TZM(v_OFFSET)
    IF(v_CUR_TZM!="") putitem/id v_ITEM,"TZM",v_CUR_TZM
    putitem/id v_ITEM,"OFH",v_OFFSET
    putitem/id v_ITEM,"OFS",LFF_GET_OFF_STR(v_OFFSET)

    putitem/id v_LST2,v_CUR_TZX,v_ITEM
    IF(v_CUR_TZA!=v_CUR_TZX)
      ;IF($length(v_CUR_TZX)==3)
      IF($scan(v_CUR_TZX,"/")==0)
        putitem/id v_LST3,v_CUR_TZA,v_CUR_TZX
      ENDIF
    ENDIF

  ENDFOR

  $nlsinternaltime = v_TZ_MEM1
  $nlstimezone     = v_TZ_MEM2

  v_LST6 = ""
  FORLIST/id v_CUR_TZX,v_ITEM in v_LST2

    v_CUR_TZS = $item("TZS",v_ITEM)
    IF(v_CUR_TZS=="")
      v_CUR_TZS = $item(v_CUR_TZX,v_LST3)
      IF(v_CUR_TZS=="")
         v_CUR_TZS = $item($item("TZA",v_ITEM),v_LST3)
      ENDIF
      IF(v_CUR_TZS!="")
        putitem/id v_ITEM,"TZS",v_CUR_TZS
      ELSE
        delitem/id v_ITEM,"TZS"
        putitem/id v_LST6,v_CUR_TZX,v_ITEM
      ENDIF
    ENDIF

    IF(v_CUR_TZS!="")
      v_CUR_TZA2 = $item("TZA",$item(v_CUR_TZS,v_LST2))
    ELSE
      v_CUR_TZA2 = ""
    ENDIF
    v_CUR_TZA = $item("TZA",v_ITEM)
    IF(v_CUR_TZA=="")
      IF(v_CUR_TZA2!="")
        putitem/id v_ITEM,"TZA",v_CUR_TZA2
      ELSE
        delitem/id v_ITEM,"TZA"
      ENDIF
    ELSEIF($scan(v_CUR_TZA,"/")==0) ;$length(v_CUR_TZA)<=3)
      ;IF($length(v_CUR_TZA2)>3)
      IF($scan(v_CUR_TZA2,"/")>0)
        putitem/id v_ITEM,"TZA",v_CUR_TZA2
      ENDIF
    ENDIF

    putitem/id $$S_SYS_TZ_LSTL,v_CUR_TZX,v_ITEM

    ;IF($length(v_CUR_TZX)<=3)
    IF($scan(v_CUR_TZX,"/")==0)
      putitem/id $$S_SYS_TZ_LSTS,v_CUR_TZX,v_ITEM
      v_OFFSET = $item("OFH",v_ITEM)
      IF($length(v_CUR_TZX)==3)
        putitem/id $$S_SYS_TZ_LSTO,v_OFFSET,v_ITEM
      ELSE
        putitem/id v_LST3,v_OFFSET,v_ITEM
      ENDIF
    ENDIF

  ENDFOR

  FOR v_OFFSET = -12 to 12
    v_CUR_TZM = LFF_GET_TZM(v_OFFSET)
    getitem/id v_ITEM,$$S_SYS_TZ_LSTO,v_OFFSET
    IF($status<=0)
      v_ITEM = $item(v_OFFSET,v_LST3)
      IF(v_ITEM=="")
        putitem/id v_ITEM,"TZS",v_CUR_TZM
        putitem/id v_ITEM,"TZM",v_CUR_TZM
        putitem/id v_ITEM,"OFH",v_OFFSET
        putitem/id v_ITEM,"OFS",LFF_GET_OFF_STR(v_OFFSET)
      ENDIF
      putitem/id $$S_SYS_TZ_LSTO,v_OFFSET,v_ITEM
    ENDIF
    putitem/id $$S_SYS_TZ_LSTM,v_CUR_TZM ,v_ITEM
  ENDFOR

  FORLIST/id v_CUR_TZX,v_ITEM in v_LST6
    v_CUR_TZS = $item("TZS",v_ITEM)
    IF(v_CUR_TZS=="")
      v_CUR_TZM = $item("TZM",v_ITEM)
      IF(v_CUR_TZM!="")
        v_CUR_TZS = $item("TZS",$item(v_CUR_TZM,$$S_SYS_TZ_LSTM))
        IF(v_CUR_TZS!="")
          putitem/id v_ITEM,"TZS",v_CUR_TZS
          putitem/id $$S_SYS_TZ_LSTL,v_CUR_TZX,v_ITEM
        ENDIF
      ENDIF
    ENDIF
  ENDFOR

  $$S_SYS_TZ_LSTO = $sortlist($$S_SYS_TZ_LSTO, "$idpart: numeric")

  $$S_SYS_TZ_LSTM = $sortlist($$S_SYS_TZ_LSTM, "$idpart:")

 TAG_WEITER:

  IF($$S_SYS_TZ_LSTL=="·*ERR")
    $status = -2
    RETURN("")
  ENDIF
  SELECTCASE v_TYP
  CASE "S"
    $status = 0
    RETURN($$S_SYS_TZ_LSTS)
  CASE "L"
    $status = 0
    RETURN($$S_SYS_TZ_LSTL)
  CASE "O"
    $status = 0
    RETURN($$S_SYS_TZ_LSTO)
  CASE "M"
    $status = 0
    RETURN($$S_SYS_TZ_LSTM)
  ELSECASE
    $status = -2
    RETURN("")
  ENDSELECTCASE
END ; SF_TZ_GET_TZ_LST

ENTRY LFF_GET_TZM
  returns string
  params
    numeric v_OFFSET:IN
  endparams
  variables
    string  v_CUR_TZM
  endvariables
  IF($frac(v_OFFSET)==0)
    IF(v_OFFSET==0)
      v_CUR_TZM = "Z"
    ELSEIF(v_OFFSET>0 && v_OFFSET<=12)
      v_CUR_TZM = "ABCDEFGHIKLM"[v_OFFSET:1]
    ELSEIF(v_OFFSET<0 && v_OFFSET>=-12)
      v_CUR_TZM = "NOPQRSTUVWXY"[(-v_OFFSET):1]
    ELSE
      v_CUR_TZM = ""
    ENDIF
  ELSE
    v_CUR_TZM = ""
  ENDIF
  RETURN(v_CUR_TZM)
END ; LFF_GET_TZM

ENTRY LFF_GET_OFF_STR
  returns string
  params
    numeric v_OFFSET:IN
  endparams
  variables
    string  v_VZ
    string  v_STR1,v_STR2
  endvariables
  IF(v_OFFSET==0)
    RETURN("+00:00")
  ELSEIF(v_OFFSET>0)
    v_VZ = "+"
  ELSE
    v_OFFSET *= -1
    v_VZ = "-"
  ENDIF
  v_STR1 =$int(v_OFFSET)
  v_STR2 =$int($frac(v_OFFSET)*60)
  WHILE($length(v_STR1)<2) v_STR1 = $concat("0",v_STR1)
  WHILE($length(v_STR2)<2) v_STR2 = $concat("0",v_STR2)

  RETURN($concat(v_VZ,v_STR1,":",v_STR2))
END ; LFF_GET_OFF_STR

----