When timestamps are stored in a database table, they are often stored in local time. This is not bad at all, but if you have to compare or coordinate timestamps of different time zones, it quickly gets complicated due to different time offsets, different daylight saving time rules and so on. So it would be nice, to convert timestamps from local time to UTC and back. We will create a RPG procedure to do that, and make it available as a SQL user defined function (UDF).

I don’t post complete source code, because I think you know how to create a source file, service program, etc. I also don’t go through the source code line by line, because I think you are an experienced RPG programmer. So if your are open questions feel free to post them in the comment section.

IBM i has an the QWCCVTDT API to convert dates, times and timestamps between different formats and time zones. The prototype for the API looks like this:

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

As most system APIs, QWCCVTDT is very flexible – but it also looks a bit complicated. So to make it easier to use, it’s best, to wrap it in a simple procedure, which takes a timestamp in one time zone and converts it to UTC.

dcl-proc localTimestampToUtc export;
  dcl-pi *n timestamp(6);
    pLocalTime timestamp(6) const;
    pLocalTimezone char(10) const options(*nopass);
  end-pi;
  dcl-ds errorCode qualified inz;
    qusec likeds(qusec);
    msgData char(1024);
  end-ds;
  dcl-ds timezoneInfo likeds(qwctzi) inz;
  dcl-s localTimezone char(10) inz('*SYS');
  dcl-s utcString char(20) inz;
  dcl-s utcTime timestamp(6) inz;
  if %parms() >= %parmnum(pLocalTimezone) and %addr(pLocalTimezone) <> *null;
    localTimezone = pLocalTimezone;
  endif;
  errorCode.qusec.qusbprv = %size(errorCode);
  convertDateTime('*YYMD':%char(pLocalTime:*iso0):
                  '*YYMD':utcString:
                  errorCode:
                  localTimezone:'*UTC':
                  timezoneInfo:%size(timezoneInfo):'1');
  if errorCode.qusec.qusbavl = *zero;
    utcTime = %timestamp(utcString:*iso0:6);
  else;
    snd-msg *escape %msg(errorCode.qusec.qusei:'QCPFMSG':errorcode.msgData)
                    %target(*caller);
  endif;
  return utcTime;
end-proc;

Such a universal procedure should reside in a service program and you should write a prototype in an include file for it.

The source code assumes, that the includes and the prototype for the API are already in your source. But you can also add the includes and the prototype inside the procedure directly after end-pi;.

As you can see, I have already used the new snd-msg opcode and the %msg and %target BIFs, that are part of V7.5. If the API returns an error via the errorCode data structure, the procedure will send this error message 1:1 back to the calling routine.

Of course you can do this also using the QMHSNDPM API, or you can simply pass the value *ZERO in the errorCode.qusec.qusbprv field – in this case, the API will send the *ESCAPE message to the procedure itself.

After all, the procedure is very simple to use:

dcl-s utc timestamp(6) inz;
 
utc = localTimestampToUtc(%timestamp('2022-05-13-14.15.16.123456'));

With only one parameter it uses the special time zone ‚*SYS‚ which means the time zone from the system value QTIMZON. But it is also possible to use the name of a *TIMZON object like ‚QP0100CET‚ for Central European Time.

If your are testing this on PUB400 be informed that the machine is configured to GMT which is equivalent to UTC. So you have to supply the second parameter to the procedure, because *SYS would result in a conversion from GMT to UTC.

utc = localTimestampToUtc(%timestamp('2022-05-13-14.15.16.123456'):'QP0100CET');

Now while this RPG procedure is useful in RPG, it would be even more useful, if it would be useable in SQL as a user defined function (UDF).

CREATE OR REPLACE FUNCTION localTimestampToUTC
(
    localTimestamp TIMESTAMP(6),
    localTimezone CHAR(10) DEFAULT '*SYS'
)
RETURNS TIMESTAMP
RETURNS NULL ON NULL INPUT
allow parallel
deterministic
NOT fenced
LANGUAGE RPGLE
program TYPE sub
external name 'MYLIB/MYSRVPGM(LOCALTIMESTAMPTOUTC)'
parameter STYLE general;

This creates a SQL user defined function, but no object on disk, because the procedure already exists in a service program. So it only creates a row in the SYSFUNCS table. And you can use the function to convert any timestamp field from the time zone it was stored in to UTC.

You should always think about these clauses when creating a UDF or stored procedure:

• returns null on null input – this is optimizing the SQL – if one or more parameters are null, the procedure isn’t called, and the result is set to null – this is handy when using UDFs on a LEFT JOIN
• allow parallel – seems pretty obvious, but always think about it – because all operations, API calls, SQL statements, etc have to be thread safe
• deterministic – means, that when the UDF is called with a given set of parameter values, it always returns the same value – it is also for optimization, because the SQE can cache parameter/result pairs and reuse them – lets say you have the same timestamp in 10.000 rows – the procedure is only called once, because the same timestamp and timezone is always resulting in the same result
• not fenced – means that the procedure is running in the same thread as the caller and no new thread is needed – but if you use a SQL cursor in your procedure, the name of the cursor can collide with a cursor in the calling procedure, because SQL cursor names have to be unique inside one thread – so fenced would create a new thread, and your cursor name cannot collide with a cursor of the caller

After the creation of the SQL UDF you can use in – here 2 examples:

VALUES ( mylib.localTimestampToUTC(TIMESTAMP('2022-05-13-14.15.16.123456'), 'QP0100CET') );

SELECT mytable.mytimest, localTimestampToUTC(mytable.mytimest, 'QP0100CET')
FROM mylib.mytable;

Now you can build a whole service program full of useful RPG procedures, that you can use directly from your RPG programs AND directly from SQL with no duplicate code (DRY principle).

I hope you will find this useful, and I appreciate your comments.

Update 19-MAY-2022

Someone asked me about the optional parameter „Input Time Indicator“ of the QWCCVTDT API, which I have ignored. This indicator is important if you want to convert a timestamp that falls into that small time interval that may be repeated, when clocks are switched.

As an example – in 2021 the clocks in Europa were switched back for 1 hour at ‚2021-10-31-03.00.00.000000‘. So the interval vom 2:00 until 3:00 was repeated another time.

The first time interval was 2:00-3:00 CEST (daylight saving time before the switch) – whereas the second interval was 2:00-3:00 CET (standard time after the switch).

In my example, we cannot know if our timestamp is daylight saving time or standard time – and I honestly don’t care about it. If you leave the parameter out, the API assumes that your timestamp is in the first interval (meaning daylight saving time).

But of course, if you know, in which segment your timestamp is in, you can also use that parameter for an even more exact timestamp conversion.

(added links to Info Center for some APIs)