Using the time zone UDF I created in RPG

Yesterday I showed how to create a SQL User Defined Function, UDF, to return the current timestamp for another time zone.

Several people messaged me asking me how I would use this in a RPG program. Rather than answer them individually I thought would share my answer with you all.

I could think of two examples of when I would use the THEIR_TIMESTAMP UDF in a RPG program:

1. I want to retrieve the timestamp from the UDF into a RPG variable, that I can then use elsewhere in the program
2. Use it as part of an SQL Insert statement into a DDL Table or DDS file

Let me start with the first scenario: retrieving the timestamp from the UDF into RPG variables:

01 **free 02 dcl-s TimeZoneCode varchar(20) inz('NOWHERE') ; 03 dcl-s ReturnedTimestamp timestamp ; 04 dcl-s ReturnedDate date ; 05 dcl-s ReturnedTime time ; 06 exec sql SET :ReturnedTimestamp = THEIR_TIMESTAMP(:TimeZoneCode) ; 07 dsply ('Timestamp = ' + %char(ReturnedTimestamp)) ; 08 ReturnedDate = %date(ReturnedTimestamp) ; 09 ReturnedTime = %time(ReturnedTimestamp) ; 10 dsply ('Date = ' + %char(ReturnedDate) + ' Time = ' + %char(ReturnedTime)) ;

Line 1: If you are writing RPG in 2023 you should be using totally free RPG.

Line 2: Definition for the variable to contain the time zone code that the UDF uses as its input parameter. I have defined it as variable length character, and initialize it with the time zone code I will be using in this example.

Lines 3 – 5: Definition of variables that will be used to contain the result from the UDF.

Line 6: I am using the SQL Set statement to get the result from a SQL statement into a RPG program variable, ReturnedTimestamp. Notice that the RPG variable is prefixed with a colon ( : ), which tells the SQL precompiler that this is a program variable, rather than a SQL one. To the right on the equal sign ( = ) is the UDF. The UDF uses the RPG program's TimeZoneCode variable as the input parameter.

Line 7: I use the Display operation code, DSPLY, to display the contents of ReturnedTimestamp, which has to be converted to character to be concatenated.

Lines 8 and 9: If I want to create separate values for the date and time I would not bother run the UDF again. I would use the timestamp from line 6 and use RPG's %DATE and %TIME built in functions, BiFs.

When I run this code I displayed the following:

DSPLY Timestamp = 2023-10-11-20.13.09.928691 DSPLY Date = 2023-10-11 Time = 20.13.09

The other scenario I could think of was writing the timestamp generated by the UDF into a DDS file or DDL table. Here I am going to use the SQL Insert statement, and to prove that there is no special "SQL magic" I will inserting the data into a DDS file.

The layout for this file is:

01 A R TESTFILER 02 A TIME_ZONE 20A VARLEN 03 A TIME_STAMP Z

The Insert statement is a multiple row insert.

11 exec sql INSERT INTO TESTFILE 12 VALUES('My time', CURRENT_TIMESTAMP), 13 ('UTC', CURRENT_TIMESTAMP - CURRENT_TIMEZONE), 14 (:TimeZoneCode, THEIR_TIMESTAMP(:TimeZoneCode)) ;

Line 2: The first row will contain the current timestamp for the time zone I am in, US Central time.

Line 3: I am calculating the UTC time using the CURRENT_TIMEZONE function.

Line 4: I can use the UDF directly in the Insert statement to insert the current timestamp for the "NOWHERE" time zone.

After running this program the contents of TESTFILE are:

TIME_ZONE TIME_STAMP --------- -------------------------- My time 2023-10-11-20.05.12.459923 UTC 2023-10-12-01.05.12.459923 NOWHERE 2023-10-12-03.35.12.478224

The above are just a couple of examples of how I can use the THEIR_TIMESTAMP UDF in my RPG program to allow me to get the current timestamp of a different time zone.

 

This article was written for IBM i 7.5, and should work for some earlier releases too.