IRI Date & Time Functionality Gets a Boost
IRI software can now perform advanced date/time format conversion and value calculation using the C++ Boost© Date_Time library. It supports input date and/or time data in generic or custom formats, the ability to add or subtract by time unit (i.e., years, hours, etc.), and output to any target/format.
Boost support is in the form of new, field-level library functions available in SortCL programs for users of the IRI Voracity data management platform or standalone IRI CoSort product. It is an alternative to the SortCL /TEMPLATE feature for defining custom date and numeric masks, and building compound data values for formatting. However, multinational date logic was not built in and thus prompted IRI’s interest in Boost.
Boost’s date and time addition, subtraction, and format conversion features can be performed before or after sorting and other data manipulations using the function change_dt() in a /FIELD statement.
Use Cases
- Your source data contains dates, times, or time-stamps in a custom or outdated format and you need to process the data in CoSort, NextForm or FieldShield.
- You need to adjust date / time values, adding or subtracting X number of days, hours, etc.
- Your date / time target columns need to be in a different format than the source.
Summary of change_dt()
There are four variations of the external function change_dt():
- Argument – change_dt(Source, String outFormat)
Changes source data from a generic date / time format to the provided new format. - Argument v.1 – change_dt(Source, String inFormat, String outFormat)
Changes source data from a custom date / time format to the provided new format. - Argument v.2 – change_dt(Source, numAdjust, String timeUnit)
Adds or subtracts a number (numAdjust) of unit type timeUnit (days, months, etc.) from the source data. Input format must be generic, if not, use the change_dt() arguments below. - Argument – change_dt(Source, numAdjust, String timeUnit, String inFormat, String outFormat)
Adds or Subtracts a number (numAdjust) of unit type timeUnit (days, months, etc.) from the source data and changes the date / time format from inFormat to outFormat. Both formats override the /FIELD statements’ ‘TYPE=’ specification. If either argument contains an empty string (i.e., “”), a supported TYPE can be set instead.
Example 1 – Custom Time-stamp Reformatting
The source timestamp column uses an outdated format like ‘1976 30 04..59//59//23’, which equates to ‘year day month..seconds//minutes//hours(24)’. The input file, Ex1In.txt contains 100 timestamps in this custom format.
1. Using format specifiers*, we create a representation of the original timestamp format as a string:
“%Y %d %m..%S//%M//%H”
2. Using format specifiers*, we create a representation of the timestamp format we want to output as:
“%m/%d/%Y %I:%M:%S %p” (AMERICAN_TIMESTAMP format)
3. Specify both format strings in the function in a job script like this:
4. Run the job from the command line, from IRI Workbench, a batch file, or third-party scheduler. The source and target files are:
Ex1In.txt Ex1Out.txt 1976 30 04..59//59//03 04/30/1976 03:59:59 AM 2016 21 03..58//01//13 03/21/2016 01:01:58 PM 1988 30 04..03//59//11 04/30/1988 11:59:03 AM 2009 21 01..38//58//12 01/21/2009 12:58:38 PM 1944 10 02..59//39//14 02/10/1944 02:39:59 PM 2006 08 04..51//59//21 04/08/2006 09:59:51 PM 2007 18 05..59//07//02 05/18/2007 02:07:59 AM 2001 22 08..58//45//03 08/22/2001 03:45:58 AM 2002 20 09..49//59//22 09/20/2002 10:59:49 PM 2000 22 09..21//42//15 09/22/2000 03:42:21 PM
The target file contains all the dates in the new format. Note also that the 24-hour-based time value was also converted to its 12-hour format equivalent.
Example 2 – Reformatting Custom Time-stamps while Changing the Date and Sorting
The source timestamp column uses an outdated format like “19760430 11..59..59..pm”, which equates to ‘yearsmonthsdays hours(12)..minutes..seconds..ampm’. The input file Ex2In.txt contains 100 timestamps in this custom format.
1. Using format specifiers*, we create a representation of the timestamp format as a string:
“%Y%m%d %I..%M..%S..%p”
2. Using format specifiers*, we create a representation of the timestamp format we want to output as:
“%Y-%m-%d %H:%M:%S” (ISO_TIMESTAMP format)
3. Input the format strings into the function inside the script as arguments 4 and 5. Notice, this time we’re executing change_dt() in an /INREC /FIELD because we want to sort the data as well, so we convert and adjust the timestamp before the sort phase.
4. Input the number 5 as the second argument and the time unit “hours” as the third. This will add 5 hours to all dates. Or, use a negative number (-5) to subtract.
4. Run the job from the command line, from IRI Workbench, a batch file, or third-party scheduler. The source and target files are:
Ex2In.txt Ex2Out.txt 19760430 03..59..59..AM 1944-02-10 19:39:59 20160321 01..01..58..PM 1976-04-30 08:59:59 19880430 11..59..03..AM 1988-04-30 16:59:03 20090121 12..58..38..PM 2000-09-22 20:42:21 19440210 02..39..59..PM 2001-08-22 08:45:58 20060408 09..59..51..PM 2002-09-21 03:59:49 20070518 02..07..59..AM 2006-04-09 02:59:51 20010822 03..45..58..AM 2007-05-18 07:07:59 20020920 10..59..49..PM 2009-01-21 17:58:38 20000922 03..42..21..PM 2016-03-21 18:01:58
The target file contains all the dates in the new format, with the 12-hour time changed to 24-hour. Additionally, 5 hours have been added to each timestamp, and they are sorted from low to high.
Example 3 – Reformatting Custom Time-stamps Containing Additional Information
Source data is provided in an outdated custom time-stamp format “Sun Jun 07 09:00:00 UTC+0200 2009”. This equates to ‘abrvDayName abrvMonthName days hours(24):minutes:seconds UTC-Offset year’. Our job requires we drop the written month of year, day of week, and UTC-TIMEZONE and output to a clean ISO_TIMESTAMP format.
File Ex3In.txt contains 100 time-stamps in this outdated custom input format.
1. Using format specifiers*, we create a representation of the time-stamp format as a string:
“%a %b %d %H:%M:%S UTC+0000 %Y”
Note that no specifier is used to represent UTC-TIMEZONE, only the letters UTC as written in the Source data, and +0000 as default values. Currently, Boost does not allow for the input of UTC-TIMEZONE, so this is a way of still being able to input the time-stamp data for conversion and / or adjustment, while ignoring the UTC-TIMEZONE.
2. Using format specifiers*, we create a representation of the time-stamp format we want to output as:
“%Y-%m-%d %H:%M:%S” (ISO_TIMESTAMP format)
3. Input the format strings into the function inside the script as shown below.
4. Run the job from the command line, from IRI Workbench, a batch file, or third-party scheduler. The source and target files are:
Ex3In.txt Ex3Out.txt Sun Jun 07 09:00:00 UTC+0200 2009 2009-06-07 09:00:00 Mon Jun 08 02:00:00 UTC-1000 2009 2009-06-08 02:00:00 Tue Jun 09 13:59:59 UTC+0900 2012 2012-06-09 13:59:59 Wed Jun 10 14:00:00 UTC-0800 2015 2015-06-10 14:00:00 Thu Jun 11 09:00:00 UTC+0200 2009 2009-06-11 09:00:00 Fri Jun 12 02:00:00 UTC-1000 2009 2009-06-12 02:00:00 Sat Jun 13 13:59:59 UTC+0900 2012 2012-06-13 13:59:59 Sun Jun 14 14:00:00 UTC-0800 2015 2015-06-14 14:00:00 Mon Jun 15 09:00:00 UTC+0200 2009 2009-06-15 09:00:00 Tue Jun 16 02:00:00 UTC-1000 2009 2009-06-16 02:00:00
The target contains all the dates in the new format, while disregarding the UTC-TIMEZONE designation from the source.
Oracle SQL Developer dialog
* Boost Exceptions: All basic Boost format specifiers function for input and output. Some of the ‘combination’ specifiers, such as %r, which is equivalent to ‘%I:%M:%S %p’, can only be used as output formats. In a case such as this, simply use ‘%I:%M:%S %p’ in place of %r and input will work as expected. UTC-TIMEZONE data cannot be input for manipulation at this time. Also, specify all months, days, hours, minutes, and seconds in double-digit format (01, not 1) to avoid error messages. See the latest Boost Date_Time format specifiers here: Boost_1.59.0 Format Specifiers *
1 COMMENT
[…] updated in 2017 through Boost library support to improve date calculation and reformatting. See this article for more […]