Maintaining Time Zones
This section discusses how to define and maintain time zones.
Oracle delivers a subset of time zone data including information about daylight saving time observances in the time zone table. Typically, you will access time zone data through the PSTIMEZONE view, which shows current entries from the PSTIMEZONEDEFN table. The PSTIMEZONEDEFN table has an effective datetime field, allowing historical time zone definitions to be stored. The Oracle-provided subset of time zone data is consistent with current law at the time of the PeopleTools GA release. Oracle does not provide patches or updates to this time zone data once it is released—for example, to bring it up to date with recently enacted legislation.
You are, therefore, responsible for three tasks:
Create any time zone definitions required by your global operations but not included in the Oracle-provided data.
Ensure that each time zone has an entry with a PTEFFDTTM of 1900-01-01 00:00:00, plus entries for any regulatory changes you want to track, in the PSTIMEZONEDEFN table.
Update time zone definitions whenever local regulations change the definition for a time zone; for example, the daylight saving time start or end date.
Ensure that each time zone ID you added has an entry in the Time Zone Mapping Table to map to an IANA time zone.
Access the Time Zone IDs page ().
Image: Time Zone IDs page: Time Zone Data tab
This example illustrates the fields and controls on the Time Zone IDs page: Time Zone Data tab. You can find definitions for the fields and controls later on this page.
Time Zone Data Tab
|
Field or Control |
Definition |
|---|---|
| Time Zone ID |
Displays the name (or ID) for the time zone. |
| Effective DateTime |
Displays the effective date and time for each time zone definition. |
| Description |
Displays the description for the time zone ID. |
| ID for Standard Time |
Displays the name for the time zone during standard time. |
| ID for DST |
Displays the name for the time zone during daylight saving time. |
| Offset from UTC |
Displays the number of minutes the time is offset from universal coordinated time (UTC, also known as Greenwich mean time or GMT). A positive offset indicates a time zone east of UTC; a negative offset indicates a time zone west of UTC. For example, the time zone for India, which is 5 and a half hours east of UTC, has an offset of +330. U.S. pacific standard time (PST), which is 8 hours west of UTC, has an offset of -480. |
| Generate Query Offsets |
Click to access the open the Time Zone Offset Generation page. On this page, you can use the information from the Time Zone IDs page to populate the PSTZOFFSET table with offsets for all the time zones and their daylight saving time periods for a specified period of time. This makes the time zone information available in a format that can be easily accessed with SQL. This may be useful for PeopleSoft Query. The PeopleSoft system does not require that you populate this table; you need to do so only if you require access to this information. If you do use the information, regenerate the offsets anytime the underlying time zone data is changed. |
Daylight Saving Data Tab
Select the Daylight Saving Data tab to view daylight saving offset information and the IDs specified for daylight saving time start and daylight saving time end.
Image: Time Zone IDs page: Daylight Saving Data tab
This example illustrates the fields and controls on the Time Zone IDs page: Daylight Saving Data tab. You can find definitions for the fields and controls later on this page.
|
Field or Control |
Definition |
|---|---|
| Observes DST |
Indicates whether the time zone observes daylight saving times. If this check box is cleared, the other DST values have no effect. |
| DST Offset |
Displays the number of minutes by which the time is offset during daylight saving time. |
| DST Start ID |
Displays the ID for when daylight saving time begins. This field prompts against values from the PSDSTTIME table (the DST IDs page). |
| DST End ID |
Displays the ID for when daylight saving time ends. This field prompts against values from the PSDSTTIME table (the DST IDs page). |
Access the Time Zone Offset Generation page (click the Generate Query Offsets button on the Time Zone IDs page).
To generate query offsets:
Enter a start date and an end date.
Select a reasonable range of dates. With the time zones delivered with the PeopleSoft system, more than 100 rows are generated for each year.
Click OK.
The data is generated for the table. Any existing data for the same range of dates is overwritten.
Note: If you receive an error about missing daylight saving time IDs, the error can be corrected by adding entries to the PSDSTTIME table on the DST IDs page.
For example, the following data from the PSTZOFFSET table represents one time zone, EST, for a two-year period, from January 1, 1999, to January 1, 2001. It is based on a system with PST as the base time zone. When you generate query offsets, the PSTZOFFSET table contains similar data for all the time zones defined in the PSTIMEZONE table.
|
TIMEZONE |
STARTDATETIME |
ENDDATETIME |
BASEOFFSET |
TIMEZONELABEL |
|---|---|---|---|---|
|
EST |
1999-01-01 00:00:00.000 |
1999-04-03 23:00:00.000 |
180 |
EST |
|
EST |
1999-04-03 23:00:00.000 |
1999-04-04 02:00:00.000 |
240 |
EDT |
|
EST |
1999-04-04 02:00:00.000 |
1999-10-30 23:00:00.000 |
180 |
EDT |
|
EST |
1999-10-30 23:00:00.000 |
1999-10-31 02:00:00.000 |
120 |
EST |
|
EST |
1999-10-31 02:00:00.000 |
2000-04-01 23:00:00.000 |
180 |
EST |
|
EST |
2000-04-01 23:00:00.000 |
2000-04-02 02:00:00.000 |
240 |
EDT |
|
EST |
2000-04-02 02:00:00.000 |
2000-10-28 23:00:00.000 |
180 |
EDT |
|
EST |
2000-10-28 23:00:00.000 |
2000-10-29 02:00:00.000 |
120 |
EST |
|
EST |
2000-10-29 02:00:00.000 |
2001-01-01 00:00:00.000 |
180 |
EST |
The first row of data shows that from January 1, 1999, at midnight, until April 3, 1999, at 11 p.m., (base time—PST, in this case), the offset between EST and the base time zone is 180 minutes, meaning that EST is 180 minutes ahead of PST. The label for EST during this period is EST.
The second row shows that from April 3, 1999, at 11 p.m., until April 4, 1999, at 2 a.m., there is a three-hour time period during which EST is 240 minutes ahead of PST. This is because EST has changed to daylight saving time, but PST hasn’t changed yet. The label for EST during this period is EDT, for eastern daylight time.
Access the DST IDs page ().
Image: DST IDs page
This example illustrates the fields and controls on the DST IDs page. You can find definitions for the fields and controls later on this page.
|
Field or Control |
Definition |
|---|---|
| DST ID |
Displays a unique identifier for the combination of date and daylight saving start time. For example, 22ndSunFeb represents the second Sunday in February at 2:00 am. |
| Absolute |
Clear this check box to indicate a relative date, such as the last Sunday in October. |
| Month |
Displays the month. |
| Day |
For absolute dates, displays the day of the month. For relative dates, the numbers 1, 2, 3, and 4 represent the first, second, third, and fourth occurrence of the day of the week—for example, to describe the second Sunday in February, enter 2. Use the number 5 for a relative date to indicate the last occurrence of the specified day of the week, even if that day of the week occurs only four times in the month. |
| Hour |
Displays the hour at which the time switch occurs. |
| Minute |
Displays the minute at which the time switch occurs. |
| Description |
Provides a description of the DST ID. |
Access the Time Zone Mapping page ().
PeopleSoft has a mapping table to the industry standard IANA time zone IDs that is managed from the Time Zone Mapping page.
Image: Time Zone Mapping Page: Time Zone Mapping Tab
This example illustrates the fields and controls on the Time Zone Mapping Tab in the Time Zone Mapping Page. You can find definitions for the fields and controls later on this page.
|
Field or Control |
Definition |
|---|---|
| IANA TZ |
IANA TZ: Specify the IANA time zone in the format from the tzdata file. This value should match the IANA time zone detected by the JVM running on the application server or process scheduler server. Note: The IANA time zone is listed in the server logs as Detected IANA timezone. |
| Time Zone |
Specify the PeopleSoft time zone ID this IANA time zone maps to. |
| Default |
Set this to indicate that this is the default for reverse mapping from PeopleSoft time zone ID to IANA time zone ID. This reverse mapping is used in XMLP. See Using Time Zones in BI Publisher Reports |
Image: Time Zome Mapping Page: IANA Time Zone Tab
This example illustrates the fields and controls on the IANA Time Zone Tab in the Time Zone Mapping Page. You can find definitions for the fields and controls later on this page.
|
Field or Control |
Definition |
|---|---|
| IANA Time Zone |
The time zone listed in tzdata. When the application server or process scheduler starts, it will be running in one of these time zones. These are the lookup values for the Time Zone Mapping page. |
Base Time Zone and Server Time Zone
The base time zone, database server time zone, and application server time zone should all match. This is the reference time zone where all data is stored. The process scheduler time zone can be set to a time zone which is often the same as the base time zone, but can also be set to any local time zone.
When the application server and process scheduler server start up, they map the IANA time zone, as reported by the JVM, to a PeopleSoft time zone ID. This mapping is used by the Query Export to Excel function, and the reverse mapping is used by XMLP.
Tables involved in this mapping are:
PSTIMEZONEDEFN: Defines the PeopleSoft time zone IDs.
PSDSTTIME: Defines the day and time when DST rules are in effect.
PSTIMEZONEIANA: Defines the IANA time zones.
PSTIMEZONEMAP: Maps between IANA and PeopleSoft time zone definitions.
The IANA time zone mapping is used in Query, when exporting queries with date or time related fields to Excel. To show the IANA time zone in the server logs, set theLogFence = 5 in psappsrv.cfg or psprcs.cfg. Then, in Query Manager or Query Viewer, export a query having a date, time, or datetime field to Excel. The log will show the IANA time zone detected on that server, and the PeopleSoft time zone it was mapped to.
Adding DST Rules
Adding DST Rules: PST Example
The delivered DST rules for the PST time zone include effective dates for several time ranges. A more complete list for PST would be the following:
|
PTEFFDTTM |
OBSERVEDST |
DSTOFFSET |
DSTSTART |
DSTEND |
|---|---|---|---|---|
|
1900-01-01 |
N |
60 |
||
|
1918-01-01 |
Y |
60 |
2LastSunMar |
2LastSunOct |
|
1920-01-01 |
Y |
0 |
2LastSunMar |
2LastSunOct |
|
1942-01-01 |
Y |
60 |
22ndMonFeb |
12LastThuDec |
|
1943-01-01 |
Y |
60 |
0FirstFriJan |
12LastFriDec |
|
1944-01-01 |
Y |
60 |
0FirstSatJan |
12LastSunDec |
|
1945-01-01 |
Y |
60 |
0FirstMonJan |
2LastSunSep |
|
1946-01-01 |
Y |
0 |
2LastSunMar |
2LastSunOct |
|
1948-01-01 |
Y |
60 |
22ndSunMar |
12LastFriDec |
|
1949-01-01 |
Y |
60 |
0FirstSatJan |
2FirstSatJan |
|
1949-01-01 |
Y |
0 |
2FirstSatJan |
2LastSunSep |
|
1950-01-01 |
Y |
60 |
2LastSunApr |
2LastSunSep |
|
1962-01-01 |
Y |
60 |
2LastSunApr |
2LastSunOct |
|
1974-01-01 |
Y |
60 |
2FirstSunJan |
2LastSunOct |
|
1975-01-01 |
Y |
60 |
2LastSunFeb |
2LastSunApr |
|
1976-01-01 |
Y |
60 |
2LastSunApr |
2LastSunOct |
|
1987-01-01 |
Y |
60 |
2FirstSunApr |
2LastSunOct |
|
2007-01-01 |
Y |
60 |
22ndSunMar |
2FirstSunNov |
In the PSTIMEZONEDEFN table, there must be a placeholder row for 1900-01-01, even if DST was not observed then. Additional rows are also needed as the DST rules change over the years, but you only need the DST definitions for the date ranges that you require for business reasons.
To add the DST rules for additional time ranges, check the tzdata file to see which Rules were defined that apply to the given time zone, and derive the PeopleSoft PSDSTTIME entries and PSTIMEZONEDEFN entries you will need. Then use the time zone pages to add the data.
For more information on time zone and the latest tzdata file, see the documentation for IANA.
The tzdata file has a Rule for when Daylight Saving Time comes into effect, and a separate Rule for when Daylight Saving Time goes out of effect. The PeopleSoft PSTIMEZONEDEFN table has one effective dated row showing both the DST start and the DST end. So in general, two lines of tzdata Rules will be represented in one PSTIMEZONEDEFN row.
Adding DST Rules: IST Example
The IANA naming standard for time zones is based on a location. For example, Asia/Kolkata, and the typical time zone abbreviation used locally is IST for India Standard Time. But IST is also the abbreviation used locally for Israel Standard Time and Irish Standard Time. If your business requires all three of those time zones, only one of them can have a PeopleSoft time zone ID of IST, and the other two will need to have alternative IDs. In this example, IST is only needed for India Standard Time.
India Standard Time does not observe Daylight Saving Time, so it will not need any entries in PSDSTOFFSET, but it will need at least the definition with effective date of 1900-01-01 in PSTIMEZONEDEFN. It will also need entries in PSTIMEZONEMAP for any IANA time zones that the new IST PeopleSoft time zone should map to. In this example, there will be a new row in PSTIMEZONEMAP mapping from Asia/Kolkata to IST, and the column Default will be checked, since the reverse mapping for IST should point to Asia/Kolkata.