The DATETIME and DATETIME-TZ data types are new to OpenEdge Release 10. These two ABL data types provide the following benefits (where datetime refers to DATETIME or DATETIME-TZ):

  • You no longer have to manipulate dates and times separately to have datetime types of data.
  • If you use DataServers, you can get direct mapping to their datetime data.
  • If you do open ABL programming, you can convert your datetime data directly.

DATETIME data type

The DATETIME data type consists of two parts, one an ABL date and one an ABL time. The unit of time is milliseconds from midnight.

The DATETIME data type is useful for dealing with datetime data in the local time of the session that assigned the data, not absolute time. It is also useful for applications that deal with datetime data from one, and only one, time zone.

An example is a procedure that determines peak-time or off-peak times for billing telephone calls. Even though the calls could be made from different time zones, it is the local time of the call that determines the cost of the call.

DATETIME-TZ data type

The DATETIME-TZ data type consists of three parts: an ABL date and time (as for DATETIME), and an integer representing the time zone offset from Coordinated Universal Time (UTC) in minutes.

ABL stores DATETIME-TZ data in UTC, along with the time zone offset of the DATETIME-TZ. This allows for indexing of datetime data based on absolute times. UTC is the current universal standard for time. Local time zone values are relative to UTC (for example, Eastern Standard Time is UTC–05:00). For example, if a DATETIME-TZ field is created in Bedford, MA (time zone offset UTC-05:00) on June 20, 2003 at 15:55, the value stored in the database is 6/20/03 at 20:55 (time converted to milliseconds) with a -5 hour (-300 minute) offset.

Many applications deal with data that spans time zones. The DATETIME-TZ data type is useful for dealing with datetime data in absolute time. Here are some examples of when you might use the DATETIME-TZ data type:

  • An order entry application that accepts orders from around the world wants the order datetimes stored in UTC so that they index in absolute time.
  • An ABL application communicating with a Web service that includes an XML Schema datetime in its interface can use DATETIME-TZ parameters to communicate in absolute time, and to preserve time zone information from the datetime data coming from the Web service.
  • An ABL client/application server communication should also use DATETIME-TZ, since the client and the application server might be in different time zones. For DATETIME-TZ, the value represents an absolute date and time, and there is no data loss when passing parameters between sessions in different time zones.

Summary of features

The following features apply to the DATETIME and DATETIME-TZ data types:

  • ABL supports both DATETIME and DATETIME-TZ in ABL data management statements such as ASSIGN, UPDATE, DISPLAY, IMPORT/EXPORT; by the dynamic BUFFER-FIELD and TEMP-TABLE objects; and by ABL functions and conversions that apply to DATE or TIME.
  • You can define DATETIME and DATETIME-TZ database and temp-table fields (static and dynamic). The Data Dictionary supports creating DATETIME and DATETIME-TZ database fields.
  • You can define a variable or field AS a DATETIME or DATETIME-TZ variable orLIKE a DATETIME or DATETIME-TZ database field.
  • You can index DATETIME and DATETIME-TZ database and temp-table fields.
  • You can reference DATETIME and DATETIME-TZ data in WHERE clauses.
  • You can use all of the ABL constructs (BUFFER-COPY, BUFFER-COMPARE, RAW-TRANSFER) that deal with records containing DATETIME or DATETIME-TZ data.
  • You can pass DATETIME and DATETIME-TZ parameters to local or remote procedures and user-defined functions, and as return values from user-defined functions and methods.
  • The default initial value for DATETIME and DATETIME-TZ variables or fields is the Unknown value (?). The entire value is unknown if any or all of the parts are unknown.
  • You can use NOW to initialize both DATETIME and DATETIME-TZ variable or fields to the current system date and time. The time zone for a DATETIME-TZ initialized to NOW is the time zone of the session creating the DATETIME-TZ.