Date and Time formats in Content Manager OnDemand
One of the most important field types in IBM Content Manager OnDemand is Date and Time formats. Timestamps appear in various locations in IBM CMOD -- Load IDs, the Segment Table, etc. There has been a slow evolution of date and time stamps, culminating with a switch to database-native formats for the databases that OnDemand supports. This article will explain the historical time formats, describe how to convert them in a variety of ways, and demonstrate how you can use IBM CMOD date formats for special reports.
Configuring CMOD Date Fields
There are three places to configure date fields in Content Manager OnDemand.
Consistency of Date formats
Application Group Dates
When defining an Application Group, you can choose from the following date and time formats:
- The default date format for CMOD v9.0 and higher is the *internal* database date field format.
- The ranges of dates that can be stored in these fields is dependent on the underlying database engine, but basically allow any reasonable date format from the year 0 to the year 9999. This is far better than the default date formats in previous versions of CM OnDemand, since they only allowed a date range of January 1st 1970 to the year 2070.
- Date & Time
- Again, for CMOD v9.0 and higher, this is the default time format, which relies on the underlying date format provided by the database.
- Same as above, the database-native time format has far greater precision than the old format, able to specify to the microsecond.
- Date (old Style)
- Before database engines offered native date and time formats, dates were represented as an integer inside the database.
- A date of zero represented January 1st, 1970 - and 1 for January 2nd, 1970, and so on. August 20th, 2016 is day number 17034.
- The supported date range was from January 1st 1970 to December 31st 2058, due to being stored internally as an integer.
- It was NOT possible to represent dates prior to 1970, or after 2058 with a date field in Content Manager OnDemand before v9.0.
- Date & Time (old Style)
- Similar to the old style date formats, time was represented as the number of seconds since January 1st, 1970, in the universal co-ordinated time zone ("UTC").
- This time format couldn't represent fractions of a second.
- The 'epoch' of January 1st, 1970 at 12:00am is a UNIX convention, for which date conversion functions already existed, which was probably why this format was chosen.
Application Date Formats
In order to load documents into Content Manager OnDemand, an Application definition must be configured, defining the format and locations and formats of metadata found in the documents (or the index file if one exists). In the Application configuration window, in the "Load Information" tab, you can select a date or date / time format from the list called "Application Group Database Name", then enter the date (and/or time) format on the right hand side of the tab in the 'Format' field.
This allows an Application to be configured to parse almost any existing date format, so it can be converted into the date field type defined at the Application Group level, and stored in the database.
The range of available variables for Applications can be found in the online help - search the online help for the phrase "Date and Time Formats".
|%Y||Numeric Year, with leading century||%m-%d-%Y||09-01-2020|
|%y||Numeric Year, without leading century||%m-%d-%y||09-01-20|
|%m||Numeric Month, with leading zero||%m-%d-%Y||09-01-2020|
|%f||Numeric Month, without leading zero||%f-%d-%Y||9-01-2020|
|%b||Text Month, abbreviated||%b-%d-%Y||Sep-01-2020|
|%B||Text Month, complete||%B-%d-%Y||September-01-2020|
|%d||Numeric Day, with leading zero||%m-%d-%Y||09-01-2020|
|%e||Numeric Day, without leading zero||%m-%e-%Y||09-1-2020|
|%H||Numeric Hour, 24-hour format||%H:%M:%S||12:34:56|
|%I||Numeric Hour, 12-hour format||%I:%M:%S %p||12:34:56 PM|
|%p||Ante Meridiem / Post-Meridiem||%I:%M:%S %p||12:34:56 PM|
|Delimiters can be a combination of slashes (/), dashes (-), colon (:), spaces( ) or none. "%b %e, %y" matches Sep 5, 2020 and %Y-%m-%d-%H%M%S matches 2020-09-01-123456 for September 1st, 2020 at 12:34:56.|
Folder Date Formats
Just a quick reminder: Folders are a way to make searching Application Group metadata (with it's cryptic field names and internal data types) easier for end users, with human-readable field names, mapped values, pop-up lists, and date pickers.
When a user submits a query through a folder, they enter the date (and/or time) in the format specified in the Folder definition, which is then converted into the format defined in the Application Group, then the search is performed in the database, and the results are converted back into the Folder format, and displayed for the user.
ODWEK Java API Date Formats
When using the Content Manager OnDemand Web Enablement Kit ("ODWEK") Java API version v9.5 or higher to load reports into IBM CM OnDemand with the ODServer.loadInit(), ODServer.loadAddDoc() and ODServer.loadCommit() calls, date and date & time fields *must* be in ISO format.
The ISO format is supported directly by the database engine, and isn't subject to any conversion or reformatting as per the Application definition. Dates must be provided in the "YYYY-MM-DD" format, or for Date and Time formats, "YYYY-MM-DD HH:MM:SS.FFFFFF".
In the early 1990's when IBM CMOD was developed, two facts necessitated the IBM Content Manager OnDemand developers to use their own date formats: The high cost of data storage, and the lack of built-in date/time formats in the database engines that were supported by OnDemand. The solution was to store dates as integers - representing the number of days since midnight, January 1st, 1970, UTC, and times as the number of seconds since the same date. If you're a UNIX or Linux user, you should recognize this date as the 'epoch' date, used as their internal representation for dates for most operating system functions. In fact, IBM CMOD used UNIX epoch as its internal date-and-time format up until IBM CMOD v9.0.
This relationship means you can fairly easily convert between UNIX epoch date/time format, and the IBM CMOD arsdate format - simply divide by 86400 -- the number of seconds in a day.
129600 / 86400 = 1.5
A UNIX time format of 129600 is January 2nd, 1970 at 12:00pm UTC. Timezone calculations add complexity, but there are functions available in most programming languages to make this adjustment for you, based on your server's configured time zone.
Old IBM CMOD Date Formats
The old formats are simply stored as integers. Searching OnDemand through arsdoc query will perform the conversion to a human readable format, as per the IBM CMOD Folder Definition. But if you need to convert from an OnDemand date format into a human readable format, you can use the arsdate command.
IBM Content Manager OnDemand's arsdate utility will assist in converting date formats from the command line.
IBM CMOD arsdate examples
# Convert from a CMOD internal date format to a human readable date: $ arsdate -a 17034 17034 -> 08/20/16
# Convert from a human readable date to the IBM CMOD internal date format: $ arsdate -a 11/28/16 11/28/16 -> 17134
# Convert from a CMOD internal time format to a human readable date & time format: $ arsdate -i 1489982862 1489982862 -> 03/20/17 04:07:42
# Convert from a human readible date to IBM CMOD time format: $ arsdate -i 11/28/16 11/28/16 -> 1480291200-1480377599
The reason that CMOD provides a range for this result is that the integer number '1480291200' represents midnight of November 28th, 2016, and '1480377599' represents 23:59:59 (the last second before midnight) on November 28th, 2016. By including the time, and surrounding the date & time argument in quotes, you can get a specific time, down to the second:
# Convert from a human readible date *and* time to IBM CMOD internal time format: $ arsdate -i "11/28/16 13:42:19" 11/28/16 13:42:19 -> 1480340539
The quotes are necessary, so that arsdate doesn't try to interpret 13:42:19 as a date. If you forget the quotes, you get odd results:
# Oops, you forgot to put quotes (") around the timestamp. $ arsdate -i 11/28/16 13:42:19 11/28/16 -> 1480291200-1480377599 13:42:19 -> -1
New IBM CMOD Date Formats
The new date/time formats introduced in IBM CMOD v9.0 use the native date/time fields in your chosen database engine. This greatly simplifies date & time calculations and queries, as the database can use highly optimized search functions to complete user requests. Unfortunately, OnDemand Application Group data will need to be migrated to use these new date formats, which is best done at the time of an upgrade or data migration. Another improvement is that native database date formats support a MUCH wider range of dates. Prior to IBM CMOD v9.5, the range of dates that could be stored in CMOD were from 1970 until 2070 for date fields, and from 1970 to 2038 for date/time fields. Most native database date/time fields not only support years from 0000 to 9999, they also have accuracy down to a millionth of a second.
Converting Between Old and New
In order to convert from the old date formats to the new ones, add the -C option to your arsdate command. Depending on your database and your language settings, your results may be formatted differently.
$ arsdate -C -i 1489982862 1489982862 -> 03/20/17 04:07:42
$ arsdate -C -a 17034 17034 -> 08/20/16
IBM CMOD Dates in SQL
If you're performing an SQL query in your OnDemand Database, and you want to convert from the internal date to a human readable date format inside the database engine, it takes a little extra work. You need to add the number of days or seconds to the epoch date of January 1st, 1970.
You can use this function to get a date from a timestamp field:
date('1970-01-01') + RPT_TIMESTAMP SECONDS
And this function to get a date from a date field:
date('1970-01-01') + RPT_DATE days
If your CMOD Application Group is configured to use the new date formats (with their underlying database-native date fields) then you can use built-in database functions to assist with your SQL queries:
select cust_num,count(*) from ABC4 where year(rpt_date) >= 2020 group by cust_num
This query would show you a list of customer numbers, and the number of times they appear, in the database table called 'ABC4' where the date is the year 2020 or greater.
IBM CMOD Dates in Excel
If you have CMOD table data in Excel, and you need to convert from an internal date, just add 25568 (the number of days since January 1st 1900 to January 1st, 1970.) and format it as a date.
=(A1 + 25568)
Where the cell 'A1' contains a date in the CMOD internal date format.
If you have CMOD internal date format instead, you can use this function instead:
=(A1/86400 + 22568)
This should give you an approximate date/time -- but beware that you will likely need to make accommodations for time zone changes between the server that produced the report, the time zone of the CMOD server where the data was loaded, and your local PC, depending on how you obtained the data.
IBM CMOD arsdate command online help
# arsdate ARS4500I Usage: arsdate [-a|-i|-z] [-A|-I|-Z] [-C] [-d] [-D] [-f] [-F] [-g] [-h] [-m] [-n] [-s] [-t] [-y] (value | number) Version: 10.1.0.2 -a Input date type (default) -A Output date type (defaults to input type) -C Convert to/from OD internal type and database type -d <days> Days to add -D Database type (default is OnDemand internal type) -f <input_fmt> Input format string -F <output_fmt> Output format string -g Get today's date -h <hours> Hours to add -i Input date/time type (without timezone) -I Output date/time type (without timezone) -m <months> Months to add -n <mins> Minutes to add -s <secs> Seconds to add -t time format -y <years> Years to add -z Input date/time type (with timezone) -Z Output date/time type (with timezone) -1 <trace_file> Trace file -2 <trace_level> Trace level