Determining the time zone, and calculating the time in another country

I was asked if there is a way to calculate the current time in Japan, in an IBM i partition in the USA. To add to the complication in doing this the use of Daylight Savings Time, DST, has to be handled. Most places in the USA observe DST, not all other countries do.

Every IBM i partition has a set of system values that are related date and time. Two of these are particularly relevant here:

• QTIMZON:  Time zone
• QUTCOFFSET:  UTC offset, hours and minutes different from UTC. UTC the same as GMT, Greenwich Mean Time

I can retrieve these two system values with a SQL statement using the SYSTEM_VALUE_INFO View:

01 SELECT SYSTEM_VALUE_NAME,CURRENT_CHARACTER_VALUE 02 FROM QSYS2.SYSTEM_VALUE_INFO 03 WHERE SYSTEM_VALUE_NAME IN ('QTIMZON','QUTCOFFSET') 04 ORDER BY 1

The results are:

SYSTEM_ CURRENT_ VALUE_ CHARACTER_ NAME VALUE ---------- ---------- QTIMZON QN0600CST2 QUTCOFFSET -0500

The IBM i partition I used here is on US Central Time, which is what the QN0600CST2 stands for. US Central Time is normally -6 hours different UTC, except when it is DST then it is -5 hours.

Fortunately IBM has a web page that describes what all the Time Zone means. It is found here.

For the time zone code QN0600CST2 the entry on that page is:

Time zone object	QN0600CST2
Offset from UTC	-6:00
Standard time name	Central Standard Time (CST)
Daylight Savings time name	Central Daylight Time (CDT)
DST start	Second Sunday in March at 2:00 AM
DST end	First Sunday in November at 2:00 AM
Message id for standard time description*	CPX0921
Message id for DST description*	CPX0922

The message ids, marked with *, are different depending upon the time zone. If you want to see what they all are use the MESSAGE_FILE_DATA View:

01 SELECT MESSAGE_ID, 02 MESSAGE_SECOND_LEVEL_TEXT 03 FROM QSYS2.MESSAGE_FILE_DATA 04 WHERE MESSAGE_FILE_LIBRARY = 'QSYS' 05 AND MESSAGE_FILE = 'QCPFMSG' 06 AND MESSAGE_ID BETWEEN 'CPX091A' AND 'CPX0969' 07 AND MESSAGE_TEXT = ' '

Line 6: All the time zone message ids are in this range. But there are some other message ids that are not related to time zones in that range.

Line 7: Fortunately all the time zone messages had a blank message text column.

There are too many to list here. Below are the entries for the US Central Time:

MESSAGE_ID MESSAGE_SECOND_LEVEL_TEXT ---------- ------------------------------- CPX0921 CST Central Standard Time CPX0922 CDT Central Daylight Time

Now I know what time zone I am in and its difference from UTC how can I use that view the time in UTC or for another time zone?

I don't have to use any of the results from the system values. There is a special register called CURRENT_TIMEZONE, it returns the UTC offset. I can use it thus:

01 SELECT CURRENT_TIMESTAMP AS "My time", 02 CURRENT_TIMEZONE AS "Time zone", 03 CURRENT_TIMESTAMP - CURRENT_TIMEZONE AS "UTC time" 04 FROM SYSIBM.SYSDUMMY1

I used a Select statement, rather than VALUES, as I can give the columns my choice of headings.

Line 3: Calculation UTC time is as simple as subtracting the time zone from the current timestamp.

My results are:

My time Time zone UTC time -------------------------- --------- -------------------------- 2023-09-21 12:53:51.010068 -50000 2023-09-21 17:53:51.010068

The time zone is shown as -5 hours, which is Central DST, but there are other numbers that follow it. There are some places in the world where their time zone is hours and minutes different from UTC, for example: India is +5:30, Western Australia is +8:45. Seconds different from UTC? No-one is that.

The original question was to calculate the time in Japan, which is +9 hours UTC. Fortunately Japan does not observe DST. My calculation for display its time would be:

(local_time – time_zone) + (9 hours)

In an SQL statement this can be expressed as:

01 SELECT CURRENT_TIMESTAMP AS "My time", 02 (CURRENT_TIMESTAMP - CURRENT_TIMEZONE) + 9 HOURS AS "Japan time" 03 FROM SYSIBM.SYSDUMMY1

The result is:

My time Japan time -------------------------- -------------------------- 2023-09-21 13:26:20.864599 2023-09-22 03:26:20.864599

In my experience only IT people use the timestamps. Everyone else uses a separate date and time. I can modify the previous SQL statement to give me those:

01 SELECT CURRENT_TIMESTAMP AS "My time", 02 DATE((CURRENT_TIMESTAMP - CURRENT_TIMEZONE) + 9 HOURS) AS "Japan date", 03 TIME((CURRENT_TIMESTAMP - CURRENT_TIMEZONE) + 9 HOURS) AS "Japan date" 04 FROM SYSIBM.SYSDUMMY1

Line 2: I have converted the timestamp to a date with the DATE function.

Line 3: Converted the timestamp to a time with TIME.

My time Japan date Japan time ------------------------- ---------- --------- 2023-09-21 13:26:20.864599 2023-09-22 03.26.20

That was simple for Japan as I do not have to consider DST.

I decided to make this better by creating a User Defined Function, UDF, to return the timestamp for another place. This post is long enough as it is. I have created "Part two" that demonstrates what I did to create the UDF. Which is coming soon.

 

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