Determining the time zone with a user defined function

In a previous post I wrote about calculating the time in different places in my IBM i partition. I mentioned at the end of that post that I had created a User Defined Function, UDF, to perform that calculation for me. This post is to show you how I did that.

As I described in the previous post I can calculate the UTC by using the CURRENT_TIMEZONE special register:

UTC time = CURRENT_TIMESTAMP – CURRENT_TIMEZONE

It is not possible to know what other time zone's difference is from UTC without using a table to contain the following information:

• The name I am going to call this time zone
• Hours difference from UTC
• Minutes difference from UTC
• Daylight Savings Time, DST, start and end dates

I called this table DAYLIGHT_SAVINGS_TIMES, and defined it with the following SQL Create Table statement:

01 CREATE TABLE MYLIB.DAYLIGHT_SAVINGS_TIMES 02 FOR SYSTEM NAME "DSTTABLE" 03 (CODE VARCHAR(20) DEFAULT NOT NULL, 04 UTC_DIFFERENCE_HOURS DECIMAL(2,0) NOT NULL DEFAULT 0, 05 UTC_DIFFERENCE_MINUTES DECIMAL (2,0) NOT NULL DEFAULT 0, 06 START_DATE DATE, 07 END_DATE DATE, 08 PRIMARY KEY(CODE)) ;

Line 2: As I have given the table a SQL name that is longer than ten characters it is a good idea to give it a system name too. This is what tools like PDM will know it as.

Lines 3 – 7: Definition for the columns of the table. Lines 3, 4, and 5 have defaults, therefore, they cannot be null. Lines 6 and 7 do not, therefore, they will be null if the time zone does not observe DST.

Line 8: This assigns the Table a unique key, the time zone code.

I added three rows to the Table. Any it contains:

01 SELECT * FROM DAYLIGHT_SAVINGS_TIMES UTC_ UTC_ DIFFERENCE DIFFERENCE CODE _HOURS _MINUTES START_DATE END_DATE ------- ---------- ---------- ---------- ---------- UK 0 0 2023-03-26 2023-10-28 NOWHERE 1 30 2023-04-02 2023-10-11 JAPAN 9 0 <NULL> <NULL>

The UK standard time is the same as UTC, and it does observe DST in the time period shown.

None of the time zones that are different from UTC by hours and minutes observe DST. Therefore, I created the time zone to be 1 hour and 30 minutes different from UTC, and observe DST.

Japan does not observe DST, therefore, its start and end dates are null.

I built a SQL statement to perform the calculation I want the UDF to do. This way I could work the "bugs" out of my statement before I put it in to the UDF.

01 WITH T0 (MY_TIME,UTC_TIME) AS 02 (VALUES (CURRENT_TIMESTAMP,CURRENT_TIMESTAMP - CURRENT_TIMEZONE)), 03 T1 (MY_TIME,UTC_TIME,THEIR_TIME) AS 04 (SELECT T0.*, 05 CASE 06 WHEN DATE(UTC_TIME) BETWEEN B.START_DATE AND B.END_DATE 07 THEN (UTC_TIME + (B.UTC_DIFFERENCE_HOURS + 1) HOUR) + B.UTC_DIFFERENCE_MINUTES MINUTES 09 ELSE 10 (UTC_TIME + B.UTC_DIFFERENCE_HOURS HOUR) + B.UTC_DIFFERENCE_MINUTES MINUTES 11 END 12 FROM T0, 13 LATERAL 14 (SELECT UTC_DIFFERENCE_HOURS,UTC_DIFFERENCE_MINUTES, 15 START_DATE,END_DATE 16 FROM DAYLIGHT_SAVINGS_TIMES 17 WHERE CODE = 'NOWHERE') B) 18 SELECT * FROM T1

I used a Common Table Expression, CTE, as I found it easier to "debug".

Lines 1 and 2: The definition for the first temporary file, T0, is that it will contain the timestamp in my time zone and in UTC.

Lines 3 – 17: The second part, performs the calculation to convert UTC time into the desired time zone takes a little more code.

Line 5: I am using a CASE statement to calculate the timestamp for the other time zone.

Line 6: If the current date is in the DST date range then I need to add 1 hour to the hours difference, then add that to the UTC timestamp, finally add the minutes difference.

Line 8: If the date of the UTC timestamp is not in the DST date range I can just add the hours and minutes difference.

Line 11: I found it easier to use a Lateral to join the above part of the SQL statement to...

Lines 12 – 15: The SQL statement to retrieve the columns I need from the Daylight Saving Times table for the "NOWHERE" time zone.

Line 16: Display my results:

The results for this are:

MY_TIME UTC_TIME THEIR_TIME -------------------------- -------------------------- -------------------------- 2023-09-24 09:45:51.607747 2023-09-24 14:45:51.607747 2023-09-24 17:15:51.607747

The "Their time" column is 2 hours and 30 minutes later than "UTC time". As today is within the DST range the difference from UTC is +1 hour and 30 minutes + 1 DST hour.

Once I was happy with the above CTE I could code my UDF:

01 CREATE OR REPLACE FUNCTION MYLIB.THEIR_TIMESTAMP ( 02 Code_In varchar(20) 03 ) 04 RETURNS TIMESTAMP 05 LANGUAGE SQL 06 DETERMINISTIC 07 BEGIN 08 DECLARE Timestamp_Out TIMESTAMP ; 09 WITH T0 (UTC_TIME) AS 10 (VALUES CURRENT_TIMESTAMP - CURRENT_TIMEZONE), 11 T1 (THEIR_TIME) AS 12 (SELECT CASE 13 WHEN DATE(UTC_TIME) BETWEEN B.START_DATE AND B.END_DATE 14 THEN (UTC_TIME + (B.UTC_DIFFERENCE_HOURS + 1) HOUR) + B.UTC_DIFFERENCE_MINUTES MINUTES 15 ELSE 16 (UTC_TIME + B.UTC_DIFFERENCE_HOURS HOUR) + B.UTC_DIFFERENCE_MINUTES MINUTES 17 END 18 FROM T0, 19 LATERAL 20 (SELECT UTC_DIFFERENCE_HOURS,UTC_DIFFERENCE_MINUTES, 21 START_DATE,END_DATE 22 FROM DAYLIGHT_SAVINGS_TIMES 23 WHERE CODE = Code_In) B) 24 SELECT THEIR_TIME INTO Timestamp_Out FROM T1 ; 25 RETURN Timestamp_Out ; 26 END

Line 1: I like using CREATE OR REPLACE as if I need to make a change I don't have to drop the existing function before creating a new one.

Line 2: The UDF only has one input parameter, the Table's time zone code.

Line 4: And it returns a timestamp value.

Line 5: The UDF is written in SQL.

Line 6: The function is deterministic, if it is called with the same parameter it "saves" the timestamp and return that rather then perform the calculation again.

Line 7: Where the "magic" begins.

Line 8: I have defined a timestamp variable, which will be used to return the timestamp value back to whatever executed this.

Lines 9 – 24: Basically the same as the CTE I had before. The only differences are:

Line 23: Rather than hard coded time zone code I use the code that was passed.

Line 24: I move the result of the final Select into the variable I created on line 8.

Line 25: The timestamp value is returned.

Line 26: The "magic" comes to an end.

Now I can use the UDF in my SQL Select statements:

01 SELECT CURRENT_TIMESTAMP AS "My time", 02 CURRENT_TIMESTAMP - CURRENT_TIMEZONE AS "UTC", 03 THEIR_TIMESTAMP('NOWHERE') AS "Nowhere", 04 MYLIB.THEIR_TIMESTAMP('JAPAN') AS "Japan", 05 THEIR_TIMESTAMP('UK') AS "UK" 06 FROM SYSIBM.SYSDUMMY1

Lines 1 and 2: The first two columns show the timestamp for Central time and then UTC. I want to show the UTC so I can validate the next three columns from the results.

Line 3: Call the UDF to return the timestamp for "NOWEHRE", this should be UTC + 2 hours and 30 minute, UTC difference = 1:30 + DST.

Line 4: If the UDF is not in my library list I can prefix the UDF with the library it is in. The timestamp for "JAPAN", the time will be UTC + 9 hours with no DST.

Line 5: The time for "UK". Its timestamp is zero hours + DST.

The results all confirm these values:

My time UTC -------------------------- -------------------------- 2023-09-24 11:00:41.886275 2023-09-24 16:00:41.886275 Nowhere Japan UK -------------------------- -------------------------- -------------------------- 2023-09-24 18:30:41.886523 2023-09-25 01:00:41.886764 2023-09-24 17:00:41.886905

The ability to return a valid timestamp depends upon their being an entry in the DAYLIGHT_SAVINGS_TIMES Table for the location I desire. If there is not a row for the code passed to the UDF a value of null is returned:

VALUES THEIR_TIMESTAMP('?') ; 00001 ------ <NULL>

Now I have an easy way to get the time for Japan, or any other location, providing there is an entry in the DAYLIGHT_SAVINGS_TIMES Table for it.

 

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