Working with True Dates & True Timestamps
m-Power is proud to announce the new Date/Time enhancement. Now, m-Power is stronger than ever when using true dates and true timestamps within your applications by being able to handle any display format as well as work with any database date/timestamp format. What this means is m-Power users are now able to display their dates and timestamps in a way of their choosing without any worry of compatibility.
Install the latest m-Power update as usual. After applying the update, you can verify that the new functionality is present by navigating to the Admin section, clicking "System Management", then "Edit Dictionary Files," and then choosing Servlet Properties. The end of the servlet properties should look like the screenshot below, with dBDatePattern and dBTimestampPattern added.
Note: These two attributes are automatically added to each data dictionary the first time that a user logs into the interface or switches to the data dictionary from within the interface. If these attributes are not seen, simply sign out and then sign back in to the interface. This action will automatically add these attributes to the servlet properties file.
This file is also where you will set the specific format your database uses for both dates as well as timestamps, as shown in the next section.
After installing the latest update, apps will continue to work the same as before. However, in order to fully take advantage of this new enhancement, there are two places that must be configured properly in order to customize this new functionality for dates and timestamps. The first location is the Servlet Properties file and is required for m-Power to communicate with the database. The second is an xml file named “mpower-date-format.xml” and is where date formats can be added, removed, or customized as desired.
Open the servlet properties file by navigating to the "Admin Menu"–> "System Management" –> "Edit Dictionary Files" –> "Servlet Properties." Click the “Others” tab, and then scroll to the end. There, you should see dBDatePattern as well as dBTimestampPattern. These values are what m-Power uses to interact with the database, and need to be in the same format that your database uses. Please see this document for a list of the values that can be used when building your formats. After modifying the servlet properties, be sure to save. Restart Tomcat in order to utilize the new formats.
The next place where you can configure the new date/time enhancement is in the mpower-date-format.xml file, found in m-power\proddata\conf. This file is the master list of date formats that can be used within m-Power. mrc ships the 7 most common date formats in this file, as well as 1 timestamp format. These formats include:
- ISO — (2011-01-20)
- MDY — (01/20/11)
- DMY — (20/01/11)
- YMD — (11/01/20)
- USA — (01/20/2011)
- EUR — (20.01.2011)
- JPN — (2011020)
- Timestamp — (2011-01-20 03:45:33.000)
However, you are certainly not limited to these default date formats. You can edit existing date formats, remove formats, or even add brand new formats for displaying the date at runtime. To do so, open this file with a text editor. To add the date format of 20-JAN-2012 (dd-MMM-yyyy), for example, add the following code to the end of this file:
<dateFormat pdesc="N" value="dd-MMM-yyyy">
<comment>New Date Format</comment>
So this file now looks like the screenshot below:
The "pdesc" is a unique identifier for this format and can not be duplicated. The "comment" will not be seen, but is a way to identify each format when editing this file. When finished, be sure to save. This xml file is used globally, which means that every dictionary will use the formats found in this file. The "value" is the actual format for displaying and all possible values can be found in this document.
Note: While you can have spaces within your date format, certain features of m-Power may not work as expected, such as “In the range” record selections when there is a space in the value. Instead, mrc recommends to use dashes (-) or underscores (_) when a space is desired.
Note: Java does not support more than 3 decimal places for time values. If your data has more than 3 decimal places of precision, please leave the format blank in order to display the full values.
Once you have set up the servlet properties and modified the mpower-date-format.xml file as desired, the final step is the selection of the appropriate format for each desired date or timestamp field. To do so, navigate to "Manage Data" –> "Manage Tables" and then click the "Managing Fields" icon next to the appropriate table. Finally, click the "Edit Field" icon next to any date or timestamp fields for which you would like to modify their display format. On this page, you can select the correct format from the drop down menu, as shown below.
Once done, click "OK". Any existing retrievals, reports, or summaries that use this field will need to be recompiled to utilize the new format, though you do not need to overwrite anything. However, maintenance applications require the user to overwrite the HTML. Freshly compiled applications will use the new format by default. The date format is saved as a field attribute and as a result, every field with the same name will use this updated date format. At runtime, if a user inputs an incorrect format, m-Power will alert them that the format is incorrect and will also define what the correct format is, as shown below.
- Your servlet properties file has the appropriate attributes of dBDatePattern and dBTimestampPattern, and these attributes have been filled in with the correct formats, as shown below.
- The mpower-date-format.xml file exists within m-power\proddata\conf, and is not blank.
- If all of the previous steps have been followed, but the new date formats are not appearing when trying to update a field in "Manage Data"–> "Manage Tables" –> Manage Fields –> Update Dictionary Field in the "Date Field Type" dropdown, please navigate to the m-power\tomcat folder and delete the work folder. Sign out of m-Power and after signing back in to the interface, the new formats should show up in the Date Field Type dropdown, as referenced in the screenshot below.
- If you are continuing to have problems after following these steps, please call mrc support at 630.916.0662.
Note: Please check with your database administrator for the format that your database utilizes.
- The dBDatePattern and the dBTimestampPattern are set at the data dictionary level. If they are needed to be set at the application level, please follow this document.
- Date formats are also set at the data dictionary level. If you want to change them at the application level, consider creating a date calculation which will allow you to define the format for the current application. mrc recommends hiding the original field as it has now been duplicated. As seen below, "FIRSTDATE" is a field defined as "yyyy-MM-dd" that needs to be in "MM/dd/yyyy" format for this application.
- You are free to use any format you would like in the mpower-date-format.xml file, however bear in mind that certain features of m-Power may behave unexpectedly if you are using a format that includes spaces. If running into problems at runtime, and your current date format includes spaces, try substituting the spaces for dashes (-) or underscores (_). Alternatively, as in the case of trying to use the date in an “In the range” record selection, creating a dropdown over this field in m-Painter will also work.
- Timestamp formats can have up to 3 decimal places of precision. If more are needed, it is required that the dbTimestampPattern is left blank in order to view/filter on the full value.
- In maintainers, when changing the displayed date format, it is required to overwrite the HTML in order for the maintainer to utilize the new format. For other application types, simply recompiling without overwriting is sufficient for the new date format to be displayed.
- When changing the dBTimestampPattern or the dBDatePattern within Servlet Properties, it is necessary to restart Tomcat in order to use the newly changed formats.