IBM i

 View Only
Expand all | Collapse all

Convert timestamp (local time) to UTC

  • 1.  Convert timestamp (local time) to UTC

    Posted Thu May 12, 2022 06:24 AM
    Hi, 

    in a table I stored a timestamp expressed in my timezone (CEST or CET depends on day light saving time), now I have to read it and convert it to UTC.

    I know there's the current offset register, but this is valid only now, if I have a timestamp relative to january, the current offset is wrong, 

    There's a simple method to covert it to utc?

    Or I have to make a function with the range of the date where there's the daylight saving time or not?

    Many thanks

    ------------------------------
    Paolo Salvatore
    ------------------------------


  • 2.  RE: Convert timestamp (local time) to UTC

    IBM Champion
    Posted Fri May 13, 2022 01:37 PM
    There is a special register CURRENT_TIMEZONE.
    If you subtract CURRENT_TIMEZONE from your timestamp then you get the UTC:

    Values(Current_Timestamp - Current_Timezone);

    ------------------------------
    Birgitta Hauser
    Database and Software Engineer
    Selfemployed - Modernization-Education-Consulting on IBM i
    Kaufering
    +49 170 5269964
    ------------------------------



  • 3.  RE: Convert timestamp (local time) to UTC

    Posted Fri May 13, 2022 04:01 PM

    Birgitta,
    Here on this side of the pond we have something called Daylight Savings Time.
    The last two results should not both be 18:00
    values timestamp('2022-05-13-14.00.00');
    values timestamp('2022-05-13-14.00.00') - current_timezone;
    values timestamp('2022-01-13-14.00.00') - current_timezone;

    Is there something like
    values timestamp('2022-01-13-14.00.00') - timezone(date('2022-01-13'));



    ------------------------------
    Robert Berendt
    ------------------------------



  • 4.  RE: Convert timestamp (local time) to UTC

    Posted Fri May 13, 2022 04:11 PM
    Thanks Birgitta I’ll try

    Paolo Salvatore
    +393480397754
    Directa sim
    Via Buozzi 5
    10121 - Torino



    ----------------
    Questa e-mail e le informazioni ivi contenute, compresi eventuali allegati, possono avere contenuto confidenziale e/o riservato e sono rivolte esclusivamente ai destinatari indicati.
    Chiunque la ricevesse per errore e' pregato di avvisare tempestivamente il mittente e di distruggerne il contenuto.
    Qualsiasi duplicazione, diffusione, pubblicazione a terzi o altro utilizzo non autorizzato del contenuto di questa mail o dei suoi allegati e' vietato e potrebbe costituire un reato.
    Directa non si assume alcuna responsabilita' per eventuali errori, omissioni o imprecisioni contenute in queste informazioni o per le conseguenze che ne derivano, ne' si ritiene in alcun modo vincolata da esse salvo eventuali accordi scritti.
    Directa tratta i dati contenuti secondo la normativa in materia di protezione dei dati personali italiana, tra cui il D.lgs. 196/2003, come modificato dal D.lgs. n. 101 del 2018, ed europea, tra cui il Regolamento (UE) 2016/679. Il destinatario del messaggio potra' trovare maggiori informazioni su come Directa tratta i suoi dati o su quali sono i suoi diritti, consultando le nostre privacy policies all'indirizzo www.directa.it nella sezione Privacy.
    ----------------
    This e-mail and all information contained in or attached to it may have confidential or personal content and are intended solely for the addressee. If you are not the intended addressee, please immediately notify the sender and delete all content.
    Any duplication, distribution, publication or other non authorised use of the information in this e-mail or attachments is prohibited and may be persecuted by law. Directa do not accept any legal responsibility for mistakes or incomplete information and any possible consequence that may arise from them, nor can be considered legally binded by the content, if not included in previous written agreements.
    Directa treats personal data according to italian and european privacy regulation, among which D.lgs. 196/2003, as modified by D.lgs. 101/2018, and EU Regulation 2016/679. The intended addressee can find additional information on Directa's privacy policies and his rights by viewing our site www.directa.it in the Privacy section.




  • 5.  RE: Convert timestamp (local time) to UTC

    Posted Sat May 14, 2022 12:19 AM
    As far as I don't like to contradict Birgitta, but I don't think, that this will work in that special case. 

    CURRENT_TIMEZONE only works for the current time - but daylight saving time rules are changing the offset to UTC two times a year. So a stored timestamp here in Europe can either be UTC+1 (when in standard time roughly between November to April) or it can be UTC+2 (when in daylight saving time roughly between May and October). 

    If you have to convert timestamps that are outside the current DST-window, you would have to calculate the offset first. The QWCCVTDT API handles that automatically - at least in my tests - but AFAIK there is no built in function in SQL for this use case, so you would have to build it yourself. 

    Regards,
    Daniel


    Von meinem iPhone gesendet





  • 6.  RE: Convert timestamp (local time) to UTC

    Posted Tue May 17, 2022 03:27 PM
    Hi Paolo,

    I finally found some time, to write it all up - if you like, read my blog post which was "heavily inspired" by your problem.

    https://qpgmr.de/converting-timestamps-local-utc

    Thanks for the inspiration.

    Regards,
    Daniel

    ------------------------------
    Daniel Gross
    ------------------------------



  • 7.  RE: Convert timestamp (local time) to UTC

    Posted Wed May 18, 2022 01:33 PM
    I noticed that you don't use the "Input time indicator". I guess that
    is left as an exercise for the reader?

    That would be reasonable, given how rarely it is needed. And you did
    say you were considering your readers to be "experienced" RPG
    programmers, not needing every last bit of source code handed to them.

    John




  • 8.  RE: Convert timestamp (local time) to UTC

    Posted Wed May 18, 2022 02:29 PM

    Hi John,

    well, the Input Time Indicator is very tricky - because it only happens with one hour per year, and if you only have a timestamp, you cannot know, which segment is correct.

    Let's have an example in CET/CEST - you cannot know which time '2021-10-31-02.30.00.000000' is - this can be CEST (before the switch) or it can be CET (after the switch). So to know this exactly, and to use the right indicator, you would have to store that information somewhere.

    But in reality, no database that I have seen ever does this.

    So It would always be a good idea to store the correct time zone with your timestamps.

    And you are right - I don't write up every last piece of code or comment every last parameter of an API. E.g. I don't think, that I have to write up control specs or **free - because it's obvious, and everyone as personal preferences about control specs - I would only write them up, if they have a special meaning or function for the example. And of course everyone knows how to read about an API in Info Center.

    Thanks for your comments.

    Regards,

    Daniel




    ------------------------------
    Daniel Gross
    ------------------------------



  • 9.  RE: Convert timestamp (local time) to UTC

    Posted Fri May 13, 2022 02:29 PM

    If I understand you correctly, you have already stored time stamps in a table column - maybe from January or from July - in a database which are in your local time. And now you want to convert them to UTC/GMT - and the conversion should honor the daylight saving time of your timezone at the time of the timestamp (maybe no daylight saving in January or daylight saving in July). Am I Right?

    I would try to write a small RPG procedure which uses the QWCCVTDT system API - this API has an optional parameter group 1 which does time zone conversion. Something like this

    dcl-pr convertLocalTimeToUtc timestamp;
      localTimeStamp timestamp const;
    end-pr;

    Compile this into a service program and wrap an UDF around it - and then, you can convert your local timestamp wherever you want - use the RPG procedure in RPGLE, use the SQL UDF in SQL or SQLRPGLE.

    If I find the time, I will try to write something up for this on my small blog.

    HTH
    Daniel



    ------------------------------
    Daniel Gross
    ------------------------------



  • 10.  RE: Convert timestamp (local time) to UTC

    Posted Fri May 13, 2022 04:09 PM

    Hi,

    after fiddling around a bit, here is a very basic form of the procedure I've written about:

    dcl-proc convertLocalToUtc;
      dcl-pi *n timestamp(6);
        pLocalTime timestamp(6) const;
      end-pi;
    
      /include qsysinc/qrpglesrc,qusec
      /include qsysinc/qrpglesrc,qwccvtdt
    
      dcl-pr convertDateTime extpgm('QWCCVTDT');
        inputFormat char(10) const;
        inputValue char(20) const options(*varsize);
        outputFormat char(10) const;
        outputValue char(20) options(*varsize);
        errorCode likeds(qusec);
        inputTimezone char(10) const options(*nopass);
        outputTimezone char(10) const options(*nopass);
        timezoneInfo likeds(qwctzi) options(*nopass);
        timezoneInfoLen int(10:0) const options(*nopass);
        precisionIndic char(1) const options(*nopass);
      end-pr;
    
      dcl-ds errorCode likeds(qusec) inz;
      dcl-ds timezoneInfo likeds(qwctzi) inz;
    
      dcl-s utcString char(20) inz;
      dcl-s utcTime timestamp(6) inz;
    
      monitor;
        convertDateTime('*YYMD':%char(pLocalTime:*iso0):
                        '*YYMD':utcString:
                        errorCode:
                        'QP0100CET':'*UTC':timezoneInfo:%size(timezoneInfo):'1');
        utcTime = %timestamp(utcString:*iso0:6);
      on-error;
        reset utcTime;
      endmon;
    
      return utcTime;
    end-proc;​

    It takes a timestamp field in local time - here it's hard coded as 'QP0100CET' which means CET/CEST - you can also use '*SYS' which means, the systems local timezone. And it converts to *UTC.

    Feel free, to play around with it.

    HTH and kind regards,
    Daniel

    ------------------------------
    Daniel Gross
    ------------------------------



  • 11.  RE: Convert timestamp (local time) to UTC

    Posted Wed May 18, 2022 04:55 AM
    Hi Daniel,

    many thanks for your help.

    Wonderful!!

    ------------------------------
    Paolo Salvatore
    ------------------------------