Wednesday, October 18, 2023

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.

No comments:

Post a Comment

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.