Date, Time, and Timestamps

External Data Formats

For support of date, time, and timestamp, the external data format that is used is a character string. Associated with this character string will be a data definitional attribute list (DDAT) that describes all the attributes of the string.

The attributes that are associated with the character string are as follows:

• Calendar table.

• Era table.

• Format code.

• Date separator type.

• Time separator type.

• Time zone.

• Month definition.

• Year definition.

• Century.

Date, Time, and Timestamp Concepts

Data Definitions

DATE

Date data type. The internal format is a 4 byte binary value. The DDAT number must reference a DDAT with an internal DATE format code or be set to zero which implies internal format. The internal format is fixed length, trailing blanks are NOT allowed.

TIME

Time data type. The internal format is six numbers packed into a three byte value. There is no sign nibble. The DDAT number must reference a DDAT with an internal TIME format code or be set to zero which implies internal format. The internal format is fixed length, trailing blanks are NOT allowed.

TIMESTAMP

Timestamp data type. The internal format of a Timestamp is a ten byte composite value. The composite is two numbers: one for date and time respectfully. The date and time numbers have the same encoding as the date and time data types, with an exception to the time number. The time number has an additional 3 bytes for a six packed digit microsecond value. The DDAT number must reference a DDAT with an internal Timestamp format code or be set to zero which implies internal format. The internal format is fixed length, trailing blanks are NOT allowed.

PseudoDATE

Character data type with a DDAT referenced. The DDAT number must reference a DDAT with a valid date format code. The character string format must match one of the supported formats, trailing blanks are allowed. The length is the number of bytes of the character string which can include trailing blanks.

PseudoPackedDATE

Packed data type with a DDAT referenced. The DDAT number must reference a DDAT with a valid packed date format code. The packed value must be positive and match one of the supported packed date formats.

PseudoZonedDATE

Zoned data type with a DDAT referenced. The DDAT number must reference a DDAT with a valid zoned date format code. The zoned decimal value must be positive and match one of the supported zoned date formats.

PseudoTIME

Character data type with a DDAT referenced. The DDAT number must reference a DDAT with a valid time format code. The character string format must match one of the supported formats, trailing blanks are allowed. The length is the number of bytes of the character string which can include trailing blanks.

PseudoTIMESTAMP

Character data type with a DDAT referenced. The DDAT number must reference a DDAT with a valid timestamp format code. The character string format must match one of the supported formats, trailing blanks are allowed. The length is the number of bytes of the character string which can include trailing blanks.

DATE DURATION

Packed decimal data type with a DDAT referenced. The length must be 8,0. The DDAT number is set to reference a DDAT with a valid date duration format code.

TIME DURATION

Packed decimal data type with a DDAT referenced. The length must be 6,0. The DDAT number must reference a DDAT with a valid time duration format code.

TIMESTAMP DURATION

Packed decimal data type with a DDAT referenced. The length must be 20,6. The DDAT number must reference a DDAT with a valid timestamp duration format code.

DATE LABEL DURATIONS

Packed decimal data type with a DDAT referenced. The length must be 15,0. The DDAT number must reference a DDAT with a valid date label duration format code.

TIME LABEL DURATIONS

Packed decimal data type with a DDAT referenced. The length must be 15,0. The DDAT number must reference a DDAT with a valid time label duration format code.

TIMESTAMP LABEL DURATIONS

Packed decimal data type with a DDAT referenced. The length must be 15,0. The DDAT number must reference a DDAT with a valid timestamp label duration format code.

Data Conversion

The following table describes the possible conversions for date/time data.

Arithmetic

For all Date/Time arithmetic, Increment, Decrement, and Compute, there must be at least one non-zero DDAT number specified between operand 1, operand 2, and the result.

DDATs are equivalent when both DDAT numbers are 0 or both DDAT numbers are non-zero and the DDATs they reference are byte for byte identical.

End of month adjustment can only be specified in conjunction with Date and Timestamp arithmetic that involves the incrementing and decrementing of Dates or Timestamps.

When end of month adjustment is specified, there must be at least one non-internal format specified for operand 1, operand 2, or the result. When end of month adjustment is not specified all durations must reference a DDAT that specifies a year and month definition other than zero.

Data Definitional Attribute Template

The following describes the Data Definitional Attribute Template (DDAT).

The following table describes the operand type and DDAT field associations.

The following table describes the format code. The formats are used to define the representation or interpretation of data across the MI. They also imply the length of the data, some of the minimum lengths can only be utilized when a null date or time separator has been specified.

The following table defines which data types are supported by a format code and which formats will be used when trying to determine the format of an unknown date, time, or timestamp value.

The following are the descriptions of the formats, D represents days, M represents months, Y represents years, C represents century, h represents hours, m represents minutes, s represents seconds and u represents microseconds.

• USA

  • Date - MM/DD/YYYY, character string.

  • Time - hh:mm AM or PM, character string. 00:00 AM or 12:00 AM is midnight. 12:00 PM is noon. Between 'hh:mm' and 'AM' or 'PM' only one blank is allowed.

• ISO

  • Date - YYYY-MM-DD, character string.

  • Time - hh.mm.ss, character string.

• EUR

  • Date - DD.MM.YYYY, character string.

  • Time - hh.mm.ss, character string.

• JIS

  • Date - YYYY-MM-DD, character string.

  • Time - hh:mm:ss, character string.

• SAA timestamp

  • Timestamp - YYYY-MM-DD-hh.mm.ss.uuuuuu, character string. Leading zeros may be omitted from the month, day, and hour part of the timestamp, and microseconds may be truncated or entirely omitted, (.10 = .100000).

• System internal format

  • Date - 4 byte binary Scaliger number

  • Time - 3 byte value with each byte containing two packed decimal digits having the encoding of hhmmss.

  • Timestamp - 10 byte composite number, the first part being a 4 byte internal date and the second part being a 3 byte internal time, plus microseconds, a 3 byte value with each byte containing two packed decimal digits having the encoding of uuuuuu.

• Labeled durations

  • They are a 15,0 packed decimal data value. The keywords; YEARS, MONTHS, DAYS, HOURS, MINUTES, SECONDS, and MICROSECONDS, or the singular form of the keywords; YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, and MICROSECOND, are not part of the data.

• Date duration

  • Packed decimal (8,0) number having the encoding of YYYYMMDD, where YYYY has the range 0 - 9999, MM has the range 0 - 99, and DD has the range 0 - 99.

• Time duration

  • Packed decimal (6,0) number having the encoding of hhmmss, where hh has the range 0 - 99, mm has the range 0 - 99, and ss has the range 0 - 99.

• Timestamp duration

  • Packed decimal (20,6) number having the encoding of YYYYMMDDhhmmssuuuuuu, where in addition to date and time, uuuuuu has the range 0 - 999999.

• *MMDDYY

  • Date - MMDDYY, character string

• *DDMMYY

  • Date - DDMMYY, character string

• *YYMMDD

  • Date - YYMMDD, character string

• *YYDDD

  • Date - YYDDD, character string

• *HHMMSS

  • Time - hhmmss, character string

• System clock

  • It is an 8 byte bit clock, which is used as a timestamp.

• *YYYYDDD

  • Date - YYYYDDD, seven character string. Used on the DATE scalar function.

• *YYYYMMDDhhmmss

  • Timestamp - YYYYMMDDhhmmss fourteen character string.

• *Unknown

  • Used to reference unique SAA formats. This format is used only when the format is NOT known. This format tries to find a matching format. The formats that will be scanned for are; USA, ISO, EUR, JIS, dates and times, *YYYYDDD (date), SAA timestamp, and *YYYYMMDDhhmmss timestamp. When a valid match is not found either a data, format or value, exception will be signalled.

• Packed MMDDYY, YYMMDD, DDMMYY

  • Date with MMDDYY, YYMMDD, or DDMMYY format - positive packed value

• Packed MMDDYYYY, YYYYMMDD, DDMMYYYY

  • Date with MMDDYYYY, YYYYMMDD, or DDMMYYYY format - positive packed value

• Packed YYMM or MMYY

  • Date with YYMM or MMYY format - positive packed value. Date is assumed to be the first day of the specified month.

• Packed YYYYMM or MMYYYY

  • Date with MMYYYY or YYYYMM format - positive packed value. Date is assumed to be the first day of the specified month.

• Packed YYDDD, YYYYDDD

  • Date with YYDDD or YYYYDDD format - positive packed value

• Packed CMMDDYY, CYYMMDD, CDDMMYY

  • Date with CMMDDYY, CYYMMDD, or CDDMMYY format - positive packed value. The 'C' represents the century guard digit. The supported century guard digit values are 0 through 9. A century guard digit of 0 will place the specified year in the 1900-1999 range, a guard digit value of 1 will place the specified year in the 2000-2099 range, a guard digit value of 2 will place the specified year in the 2100-2199 range, etc.

• Zoned MMDDYY, YYMMDD, DDMMYY

  • Date with MMDDYY, YYMMDD, or DDMMYY format - positive zoned value

• Zoned MMDDYYYY, YYYYMMDD, DDMMYYYY

  • Date with MMDDYYYY, YYYYMMDD, or DDMMYYYY format - positive zoned value

• Zoned YYMM or MMYY

  • Date with YYMM or MMYY format - positive zoned value. Date is assumed to be the first day of the specified month.

• Zoned YYYYMM or MMYYYY

  • Date with YYYYMM or MMYYYY format - positive zoned value. Date is assumed to be the first day of the specified month.

• Zoned YYDDD, YYYYDDD

  • Date with YYDDD or YYYYDDD format - positive zoned value

• Zoned CMMDDYY, CYYMMDD, CDDMMYY

  • Date with CMMDDYY, CYYMMDD, or CDDMMYY format - positive zoned value. The 'C' represents the century guard digit. The supported century guard digit values are 0 through 9. A century guard digit of 0 will place the specified year in the 1900-1999 range, a guard digit value of 1 will place the specified year in the 2000-2099 range, a guard digit value of 2 will place the specified year in the 2100-2199 range, etc.

The following describes the separator definitions for date and time. The separator type is a one character field that contains the separator value, for example, :, ., /, or -. When the format has an implied separator, the implied separator is used. For example: for an ISO date, the implied separator is '-'. When the format does not have an implied separator, the implied separator is null. For example, the format *YYMMDD has an implied separator of null. A null separator means the units of time, year, month, day, etc..., are concatenated together. A implied separator is specified by using the value of hex 00. A null separator is specified by using the value of hex FF. A null separator is invalid for the SAA formats, USA, ISO, etc... Any other separator value overrides the implied value for the format code.

The following describes the time zone definition. The hour zone value is 0 to 24. GMT zone is 0. The zone to the east of zone 0 is zone 1. The zone to the east of zone 1 is zone 2, and so on. The value 24 specifies that the time is to be stored as local time and that the concept of time zones should be ignored. The value of 24 is required for storage purposes. For example, when inserting a time field into a database data space the hour zone value must be 24. A value other than 24 can be used for retrieval purposes.

The minute zone value is 0 to 60. GMT zone is 0. The zone to the west of zone 0 is zone 1. The zone to the west of zone 1 is zone 2, and so on. The value 60 specifies that the time is to be stored as local time and that the concept of time zones should be ignored. The value of 60 is required for storage purposes. For example, when inserting a time field into a database data space the minute zone value must be 60. A value other than 60 can be used for retrieval purposes.

The month definition is an integer number of days. For example, 30 would specify that each month used in a duration would have a constant value of 30 days. A value of zero specifies that the calendar definition of the month should be used.

The year definition is an integer number of days. For example, 365 would specify that each year used in a duration would have a constant value of 365 days. A value of zero specifies that the calendar definition of the year should be used.

When end of month adjustment arithmetic is being performed, the month definition and year definition must have zero values. When no end of month adjustment arithmetic is being performed, the month definition and year definition must have non-zero values. Otherwise a template value invalid  (hex 3801) exception will be signaled.

The century definition is used to define the century when a two digit year is specified for a date. The definition is two numbers, one for current century and the other is for the century division year. The current century is a century number. The century division is a year number. The valid values for the current century are 00 - 99 followed by a 00 suffix. The valid values for the century division are 00 - 99. The century division is included in the current century. For example, a current century of 1900 and a century division of 50 would result in two digit years 00 - 49 having the values 2000 - 2049 and 50 - 99 having values 1950 - 1999. If the specified format has a century guard digit or a four digit year then the current century field value is ignored.

The calendar table offset is the number of bytes from the start of the DDAT to the start of the calendar table (described below).

Era Table

The era table immediately follows the fixed portion of the DDAT.

The following describes the era table data.

The era table is a list of elements that state what era should be used across the time line. The start of usage of a particular era is specified by the origin date. The end of usage of a particular era is terminated by the next table element. The last table element era is used until the end of the time line. The origin date is specified in the internal format. The era name is a character field. The maximum number of table entries allowed is 256.

The SAA era table has one entry. The SAA origin date is January 1, 0001, Gregorian for the start of the time line. The internal format would be 1721426. The SAA name is AD, anno Domini.

The SAA era table can have only one element and that element must have an effective date that falls in the time line specified in the SAA calendar table.

Calendar Table

The following describes the calendar table data.

The calendar table is a list of elements that state what calendar type/algorithm should be used across the time line. The start of usage of a particular calendar is specified by the effective date. The effective date is specified using the internal format. This first table element represents the beginning of the time line. The end of usage of a particular calendar is terminated by the next table element. The last table element calendar must be null to indicate the end of the time line. The maximum number of table entries allowed is 256.

The SAA calendar table has 2 entries. The first entry has a calendar type of Gregorian. The effective date is January 1, 0001, Gregorian for the start of the time line. The internal format would be 1721426. The second entry has a calendar type of null. The effective date is January 1, 10000, Gregorian for the end of the time line. The internal format would be 5373485.

Multiple calendar table entries are only valid with DDATs specifying the internal date format code. The rest of format codes can only have two entries in the calendar table. The second entry must have a NULL calendar type.

The following describes the encoding of the calendar types.