Present Timestamps in the Local Time Zone

Published: May 24, 2006

by Michael Sansoterra

The code for this article is available for download.

For companies with locations in multiple time zones that connect to a central iSeries, time stamp presentation can be a problem. For instance, consider a legacy shipping application that records the date and time of a shipment. If the legacy application isn't time zone aware, the shipments at the various locations will all be stamped with the date and time of the system's time zone. A desirable solution for this problem would be a routine that can adjust a timestamp from the main time zone to a local time zone.

Fortunately the "Convert Date and Time Format" (QWCCVTDT) API can help. As of V5R3, this API is capable of converting a timestamp from one time zone to another. RPG service program ADJTS, which you can see by clicking here, shows sample subprocedure ADJTIMESTAMP that uses this API for time zone conversion. The subprocedure's parameters are: an input timestamp, the input timestamp's time zone followed by the output time zone. The subprocedure returns the timestamp adjusted for the requested time zone.

Here is a free form example that converts the current date and time to Atlantic Standard Time:

AdjustedTime=AdjTimeStamp(%Timestamp:
                          '*SYS':'QN0400AST');

The input/output time zones are the names of time zone (*TIMZON) objects that define the time zone's name, offset from Coordinated Universal Time (UTC) and daylight savings information. The predefined IBM time zones can be viewed by issuing the WRKTIMZON command or by clicking here. Here are a few examples of the default time zone values:

If for some reason IBM doesn't have one defined to fit your needs, you can create a *TIMZON object with the CRTTIMZON command.

In addition to these pre-defined time zones, the following special values are allowed by the API:

• *SYS -- System Time Zone
• *UTC -- Coordinated Universal Time
• *JOB -- The current job's defined time zone

This subprocedure can be wrapped with a myriad of related signatures (i.e. parameter lists) to meet any number of variations. For example, a wrapper can be created to accept a numeric date and a numeric time from a legacy application. These two pieces of information can be combined into a timestamp to call the base AdjTimeStamp subprocedure.

For SQL users, an SQL CREATE FUNCTION statement can be used as follows to make the function available to SQL:

Create Function xxxxx/AdjTS 
(parmTimeStamp   Timestamp, 
 parmInTimeZone  Char(10), 
 parmOutTimeZone Char(10)) 
Returns TimeStamp 
Language RPGLE 
External Name 'xxxxx/ADJTS(ADJTIMESTAMP)' 
Parameter Style General 

The function could be used in SQL to convert a timestamp from the system's time zone to Eastern Standard Time as follows:

Select AdjTS(Timestamp('2006-10-02 10:25:30'),        
       Char('*SYS'),Char('QN0500EST')) 
  From SysIBM/SysDummy1

In order to implement this function, an application would need a way to track time zone by user or location. One approach is to store all of the date and time information in a single base time zone such as Coordinated Univeral Time (UTC) or the central system's default time zone. The time zone adjustment subprocedure would be used to adjust a timestamp for display purposes. It would also be used to adjust user input to convert it back to the application's base time zone. A second approach would be to record a time stamp in its native time zone along with the time zone indicator so that it could be properly converted to any other time zone when appropriate.

Applications can quickly jump time zone boundaries for localized date and time display with the use of the QWCCVTDT API.