IBM i Global

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
------------------------------

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
------------------------------

Original Message:
Sent: Thu May 12, 2022 06:23 AM
From: Paolo Salvatore
Subject: Convert timestamp (local time) to UTC

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
------------------------------

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
------------------------------

Original Message:
Sent: Fri May 13, 2022 01:36 PM
From: Birgitta Hauser
Subject: Convert timestamp (local time) to UTC

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

Original Message:
Sent: Thu May 12, 2022 06:23 AM
From: Paolo Salvatore
Subject: Convert timestamp (local time) to UTC

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
------------------------------

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.


Original Message:
Sent: 5/13/2022 1:37:00 PM
From: Birgitta Hauser
Subject: RE: Convert timestamp (local time) to UTC

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
------------------------------

Original Message:
Sent: Thu May 12, 2022 06:23 AM
From: Paolo Salvatore
Subject: Convert timestamp (local time) to UTC

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
------------------------------

Original Message:
Sent: 5/13/2022 4:11:00 PM
From: Paolo Salvatore
Subject: RE: Convert timestamp (local time) to UTC

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.


Original Message:
Sent: 5/13/2022 1:37:00 PM
From: Birgitta Hauser
Subject: RE: Convert timestamp (local time) to UTC

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
------------------------------

Original Message:
Sent: Thu May 12, 2022 06:23 AM
From: Paolo Salvatore
Subject: Convert timestamp (local time) to UTC

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
------------------------------

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
------------------------------

Original Message:
Sent: Fri May 13, 2022 04:10 PM
From: Paolo Salvatore
Subject: Convert timestamp (local time) to UTC

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.


Original Message:
Sent: 5/13/2022 1:37:00 PM
From: Birgitta Hauser
Subject: RE: Convert timestamp (local time) to UTC

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

Original Message:
Sent: Thu May 12, 2022 06:23 AM
From: Paolo Salvatore
Subject: Convert timestamp (local time) to UTC

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
------------------------------

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


Original Message:
Sent: 5/17/2022 3:27:00 PM
From: Daniel Gross
Subject: RE: Convert timestamp (local time) to UTC

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
------------------------------

Original Message:
Sent: Fri May 13, 2022 04:10 PM
From: Paolo Salvatore
Subject: Convert timestamp (local time) to UTC

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.


Original Message:
Sent: 5/13/2022 1:37:00 PM
From: Birgitta Hauser
Subject: RE: Convert timestamp (local time) to UTC

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

Original Message:
Sent: Thu May 12, 2022 06:23 AM
From: Paolo Salvatore
Subject: Convert timestamp (local time) to UTC

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
------------------------------

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
------------------------------

Original Message:
Sent: Wed May 18, 2022 01:32 PM
From: John Yeung
Subject: Convert timestamp (local time) to UTC

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


Original Message:
Sent: 5/17/2022 3:27:00 PM
From: Daniel Gross
Subject: RE: Convert timestamp (local time) to UTC

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

Original Message:
Sent: Fri May 13, 2022 04:10 PM
From: Paolo Salvatore
Subject: Convert timestamp (local time) to UTC

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.


Original Message:
Sent: 5/13/2022 1:37:00 PM
From: Birgitta Hauser
Subject: RE: Convert timestamp (local time) to UTC

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

Original Message:
Sent: Thu May 12, 2022 06:23 AM
From: Paolo Salvatore
Subject: Convert timestamp (local time) to UTC

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
------------------------------

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
------------------------------

Original Message:
Sent: Thu May 12, 2022 06:23 AM
From: Paolo Salvatore
Subject: Convert timestamp (local time) to UTC

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
------------------------------

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
------------------------------

Original Message:
Sent: Fri May 13, 2022 02:28 PM
From: Daniel Gross
Subject: Convert timestamp (local time) to UTC

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

Original Message:
Sent: Thu May 12, 2022 06:23 AM
From: Paolo Salvatore
Subject: Convert timestamp (local time) to UTC

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
------------------------------

Hi Daniel,

many thanks for your help.

Wonderful!!

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

Original Message:
Sent: Fri May 13, 2022 04:08 PM
From: Daniel Gross
Subject: Convert timestamp (local time) to UTC

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

Original Message:
Sent: Fri May 13, 2022 02:28 PM
From: Daniel Gross
Subject: Convert timestamp (local time) to UTC

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

Original Message:
Sent: Thu May 12, 2022 06:23 AM
From: Paolo Salvatore
Subject: Convert timestamp (local time) to UTC

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
------------------------------