1.1.13 DateTime
DateTime is a fixed-length field with a length of 19 (no fractional seconds) or between 21 and 29 (depending on number of positions after decimal point), containing a timestamp in ISO format: YYYY-MM-DD HH:MM:SS[.fffffffff]. If no length is specified, the length will default to 26, which is the most portable maximum length and the ISO default: YYYY-MM-DD HH:MM:SS.ffffff.
Internally, a DateTime field always contains a DateTime in UTC (Universal Coordinated Time). UTC is the modern term for GMT (Greenwich Mean Time). LANSA automatically converts to and from UTC when required. The DUTC and SUTC attributes are used to define whether the DateTime is displayed and stored in the database in UTC or local time.
The *NULL value is 1900-01-01 00:00:00.
DateTime literals may be specified with or without a time zone.
The literal format without a time zone is known as the ISO format (described above). In the ISO format, the DateTime literal is in UTC, so this specifies the time in Greenwich, England, not the local time. Both seconds and fractional seconds portions are optional (will be set to zero if not provided). Note that as this format contains a space, it must be enclosed in single quotes to allow the editor to correctly identify it as a DateTime literal. For example: '2004-02-03 00:10:30'
The literal format with a time zone is known as ISO 8601. In the ISO 8601 format, the time zone is always specified with the data, either as a Z, meaning UTC, or +/-hh:mm, being the difference from UTC. To distinguish ISO 8601 from the standard ISO format, as well as the time zone value, the blank between the date and the time is replaced with a T. Fractional seconds are optional (will be set to zero if not provided). For example: 1900-01-01T00:00:00Z is the *NULL value. Another example is 1994-11-05T08:15:30-05:00, which corresponds to November 5, 1994, 8:15:30 am, US Eastern Standard Time (the time zone is 5 hours behind UTC). 1994-11-05T13:15:30Z corresponds to the same instant.
Please review the general 1.1.1 Field Type Considerations.
Field Definition Rules
Rules for defining DateTime fields in the repository are:
|
Length |
May be 19 or 21 to 29 in length. Default is 26. Decimals are automatically calculated (if 19, Decimals are 0, otherwise Decimals are Length - 20). |
|
Valid Keyboard Shift |
Blanks |
|
Allowed Attributes |
AB, ASQN, CS, DUTC, FE, ISO, ND, RA, RL, RLTB, SUTC, TCYC, TCYU and TCYX. Note: ISO display format must be selected. |
|
Edit Mask |
Not allowed. |
|
Default |
*SQLNULL. ASQN will be enabled by default. |
Field Definition Notes
- By default, DateTimes are ISO format.
- The DUTC (Display in UTC) attribute is disabled by default. If it is enabled, data is input and output in UTC. If it is disabled, data is input and output in the local time zone. Generally, users prefer to see data in their own time zone, but it may be appropriate to display in UTC format for some cross-time zone reports, Web displays, etc.
- DUTC affects the display and entry of DateTime data in reports, components (using PRIM_DTIM or PRIM_MCTL), web functions and web events. Note that if DUTC is disabled, the DateTime will be displayed and entered in the local time zone of the machine where the application code is being executed.
- DUTC is irrelevant for field literals and intrinsic functions, where data is always manipulated in UTC.
- DUTC is used for WAMS. The DateTime is always passed in XML in ISO 8601 UTC format, which is an XML standard. The std_datetime weblet takes care of visualization. The entered value is subsequently also submitted in ISO 8601 UTC format.
- The SUTC (Store in UTC) attribute is enabled by default. This means that when the field is on a file, and the file is written to or read from the database, the field will be stored in UTC rather than local time. This automatically allows applications executing anywhere in the world to process the DateTime with a known time zone. If using tools other than LANSA to access the file, remember that the DateTime field is stored in UTC and not a local time zone.
- SUTC may be disabled if your data will only ever exist in one time zone, and all interfaces to the data will always be executed in the same time zone. If SUTC is disabled, the data will be saved in the local time zone of the machine where the database interface executes. For example, in a SuperServer application where file access is redirected to the server, data will be read and written in the local time zone of the server.
- SUTC is disabled by default for DateTime fields created by Load Other File, as most external applications store data in local time. If the external data is stored in UTC, you should enable SUTC.
Usage Rules
|
Partition Type |
|
|
Files |
DateTime fields may only be used in RDMLX Files. DateTimes may be used as real fields or keys. If used as key fields, take note of the Warning below. |
|
Logical Views |
Use of DateTimes as key fields is not recommended. |
|
Virtual Fields |
DateTimes may be used with Code Fragment Virtual Fields. |
|
Predetermined Join Fields |
Not allowed. |
|
RDML Commands |
DateTimes are classified as their own types and are not valid for numeric or alpha command parameters in RDML commands. |
|
Built-In Functions |
When used in Built-In Functions, Times are classified as their own types and are not valid for numeric or alpha arguments. |
|
Special Values |
*NULL, *HIVAL, *LOVAL, *REMEMBERED_VALUE |
|
Conversion |
Date fields may be converted to alpha, signed, packed, string or char. Refer to Field Type Conversions. |
Usage Notes
- Working fields may be defined as TYPE(*DateTime).
- If the DateTime field is not large enough for the fractional seconds provided, then it will be rounded.
- Application code, including intrinsic functions, always processes DateTime in UTC format.
Warning
- If this field is used as a key field, remove the *SQLNULL and ASQN attributes.
- System Variables like *DATETIME provide values in local time. This is not consistent with the internal storage of DateTime fields which always contains a DateTime in UTC format. Instead, use the Now intrinsic.
Platform Considerations
- Refer to Platform Considerations in 1.1.1 Field Type Considerations.
Tips & Techniques
- When you first enable your partition for RDMLX, new DateTime fields have SUTC on and DUTC off. This automatically allows multi-time zone applications, with data displayed and entered in local time, but saved in the database in UTC. This may initially be confusing, but in the end is what most applications need. If you wish your application to run all in UTC or all in local time, simply modify the default field attributes for the DateTime field type.
- To use a DateTime field, #MyDateTim, as an alpha argument, you simply use #MyDateTim.asString, explicitly stating that you want to use the string representation.
- To initialise a DateTime field to the current time or to set its default to the current time use the Now intrinsic. This ensures that the field is correctly set to UTC format.
Also See