Understanding the Date, TimeofDay, and DateTime property types

A single PRPC application can be deployed worldwide. The system can have:

• Multiple server nodes operating in different time zones
• Users who access servers located in different time zones and different locales from where they work
• Multiple databases, which can be located in different time zones than the users or servers
• Multiple, application-specific work calendars with different hours, days of the workweek, and holidays
• Interfaces to and from external databases or systems that expect many different date or time formats

To support varied needs, three different property types are available:

• Date — A day of the Gregorian calendar, without reference to a time zone or locale. The unit is a whole day, such as July 23, 2006.
• TimeofDay — A time of day, without reference to a time zone or locale. The unit is seconds, such as 15 seconds after 1:25 P.M.
• DateTime — A date and time, stored internally to the millisecond if available, in a known time zone. For example 15.452 seconds after midnight on July 23, 2006 in London, U. K.

When presented with a DateTime value, a corresponding date and a time-of-day (in a specified time zone) can be determined.

However, the converse is not usually true: when starting with a Date and a TimeofDay value, multiple differing but "correct" DateTime values can be constructed, depending on assumptions about time zone, Daylight Savings Time, and other considerations. For example, when the date is January 1 in London, it can be December 31 in New York.

Likewise, when comparing two values, or computing with dates, times, and DateTime values, context is important. A time of 6:15 P.M. in London is earlier, not later, than 6:16 P.M. in New York.

Different languages and locales use different formats to present dates and times; PRPC supports many output and import formats while using only three property types: Date, TimeofDay, and DateTime.

The internal representations are efficient for sorting and comparisons and Java operations, but may not be familiar to business users:

• The internal representation of a Date value is eight digits in the format YYYYMMDD, for example 20061215 for December 15, 2006.
• The internal representation of Time of Day value is six digits in the format HHMMSS, where the hours portion ranges from 00 to 23. For example, 153025 represents 25 seconds after 3:15 P.M.
• For DateTime values, the internal representation has the format YYYYMMDDTHHMMSS.QQQ GMT where T and GMT are literal characters, YYYY represents a year, MM is a month, DD is a day, HH is an hour value between 00 to 23, MM is a minute value between 00 and 59, SS is a second value between 00 and 59, and QQQ is a millisecond value between 000 and 999. (The pattern character Z — for Zulu time — sometimes appears rather than the time zone characters GMT in some descriptions. The value always contains a space followed by the three letters GMT.)

When a developer enters a value corresponding to a Time of Day or DateTime into a rule form, no format conversion occurs, but certain shortcuts are available.

Date values

You can enter a date using either two or four digits for the year. If the year contains two digits, the system uses an algorithm to determine a "reasonable" century based on the current date. During 2006, the system prefers dates between 1997 and 2097. Examples:

A property of type Date can have a null value. For example, if you use the Property-Set method to set a Date type value from a Text value that is null, the result is null. However, the Java Date operations convert an integer value of 0 to the date 19700101 (January 1, 1970). This Date value may result when PRPC evaluates expressions that internally use the Java Date operations.

TimeofDay values

To enter a constant TimeofDay value into a rule form, enter either all six digits or omit the seconds portion. For example:

DateTime values

When entering a DateTime value, you can omit all the characters after the T, or omit the period and the millisecond values. If the year contains two digits, the system uses an algorithm to determine a "reasonable" century based on the current date. During 2006, the system prefers dates between 1997 and 2097. If you omit the time zone, the date and time are converted from the current server time zone to the GMT time zone by adding or subtracting hours. Examples:

Date constants

When entering a Date literal constant as an entire expression, be sure to include double quotes, to distinguish:

"20061215"

the date December 15, 2006 from:

20061215

an integer that is converted to 20,061,215.

Entering and presenting values in an application context

PRPC supports dozens of formats that your application can choose to present dates and times in ways familiar to application users. The formats that apply in specific cases depend on the selected non-auto-generated control rule or the configuration of the pxDateTime auto-generated control rule (Rule-HTML-Property rule type) associated with the property or the form and the locale.

Many formats are locale-specific, changing automatically based on the value set on each user's Windows workstation, or the locale of a user, as recorded in the Operator ID data instance.

For example, the date 20061215 can appear as any of the following:

12/15/06
15/12/06
December 15, 2006
15 décembre 2006

Similarly, many different formats and shortcuts are available for input. These date and time formats are based on version 1.3 of the Unicode Common Locale Data Repository (CLDR).

Time zone and Daylight Savings Time conversions

The boundaries of time zones change from time to time. In addition, the Energy Policy Act of 2005 changes the starting and ending dates for Daylight Savings Time in most of the United States, starting in 2007.

V5.3 uses the time zones provided by the JVM on which it runs. The JVM typically uses data provided by the Olson TZ database. Details on this database are available from ftp://elsie.nci.nih.gov/pub.

Daylight savings time rules are supplied by Java JDK vendors. In a multinode system, it is important that the same rules are installed on each node. For specifics on Sun Microsystems JDKs, see PDN article 21247: Platform Bulletin: Sun JVM Issues. For details of vendor JDK support of daylight savings time and downloads, consult one of the following links:

http://www-128.ibm.com/developerworks/java/jdk/dst/
http://java.sun.com/javase/tzupdater_README.html
https://h20392.www2.hp.com/portal/swdepot
      /displayProductInfo.do?productNumber=HPUXJAVADST&jumpid=reg_r1002_usen
http://www.oracle.com/technetwork/java/javase/tzupdater-readme-136440.html

Testing input and output formats

To see the effect of a specific control rule on input and output formats for a property:

1. Open the Property form.
2. Click the Preview toolbar button ().
3. Enter a value into the Input field. Click  Show output .

The Locale Settings tool is useful in understanding the effect of locales on date and time formats. See About the Locale Settings tool.

The first eight characters of a DateTime value match the structure of a Date value, that is YYYYMMDD, for example 20060930. Accordingly, a simple way to compare a Date value with a DateTime value is to first select a substring of the DateTime value, consisting of the initial eight characters. Both values then have a common string format and length, and a normal string comparison works.

For example, the Property-Set method "converts" a DateTime value to a Date value by selecting the first eight characters:

When the DateTime value is 20071201T011532.006 GMT, the resulting value for the Date property MyDate is 20070120.

However, this approach ignores the time zone information in the DateTime value, because the Date value conveys no time zone.

Use the standard function rule:

@FormatDateTime(.pyExpireDateTime, "yyyyMMdd", "", "")

to convert a DateTime value to a Date value, in the time zone of the PRPC server.
Avoid using Date property types in persistent objects, such as work items or assignments, even if your application is used in only a single time zone, unless a time zone value is also stored in another property of the object.

The substring of a DateTime value from character position 10 to character position 15 contains a time-of-day value, in the format HHmmSS expected by a property that has a Type of TimeOfDay.

Similarly to Date conversion, you can "convert" or extract this TimeOfDay value from a DateTime value with a Property-Set assignment:

When the DateTime value is 20071201T011532.006 GMT, the resulting value for the Time property MyTime is 011523.

However, this approach ignores the time zone information in the DateTime value, because the Time value conveys no time zone.

Use the standard function rule:

@FormatDateTime(.pyExpireDateTime, "HHmmss", "", "")

to convert a DateTime value to a TimeOfDay value, in the time zone of the PRPC server. (For examples, see PDN article 24087 How to extract a DateTime value to a Date or TimeOfDay value.
Avoid using TimeofDay property types in persistent objects, such as work items or assignments, even if your application is used in only a single time zone, unless a time zone value is also stored in another property of the object.

See Data type conversions in expressions and property assignments.

Because vendors have implemented variations from SQL standards, mappings between PRPC data types and database column types vary by vendor. For the PegaRULES database, PRPC uses the mappings described here.

This information may be helpful when your application uses Connect SQL rules to access an external database that uses the vendor's software.

Oracle

The Oracle datatype DATE corresponds to the PRPC DateTime property type. To save a PRPC Date property into an Oracle database, use VARCHAR2(8), not DATE, as the Oracle datatype.

IBM DB2

IBM's DB2 DATE datatype corresponds to the PRPC Date property type.  This datatype does not contain any time information. Use the DB2 DATETIME datatype with the PRPC DateTime property to store a date with the time.

The DB2 TIME datatype does not correspond to any PRPC property type. Use a DB2 VARCHAR(8) to map a Time property to a DB2 column..

When feasible in external DB2 databases, use the DB2 TimeStamp datatype instead of DATE or TIME. If not feasible, use a PRPC property type of Text to hold the value, which can be converted (cast).

Microsoft SQL Server

The Microsoft DATETIME datatype corresponds to the DateTime property type.