Go to Home Page
Questions?
Call 1-800-572-5517
 
  Go to Home Page  
  See all products
  See price schedules
  See manuals, tutorials, articles
  Download a free 30-day trial
  See user testimonials
  About Pacific Systems Group
 
  Welcome Rocket Software MXI Users - Learn about Spectrum SMF Writer for MXI
  Choose Spectrum Writer to add 4GL to your product
  Report Writer Speedup Tips Article

Return to list of articles

This excerpt from the Spectrum Writer Reference Manual provides some tips that you may find helpful when working with time fields.

Working with Times

Spectrum Writer supports two dozen different types of time fields commonly found in mainframe files. These are listed in "Time Data Types" on page 613. For information on defining the time fields in your input files, see "How to Define a Time Field" on page 344.

Time fields, regardless of how they are stored in the input file, are normally formatted in your reports like this:

HH:MM:SS

However, time fields defined as containing only hours and minutes (the HHMM data type, for example) will be formatted without seconds, like this:

HH:MM

A number of other time display formats are available if you want to format your time fields differently. These are listed in "Time Display Formats" on page 622. For example, you can specify the HH–MM display format if you want a time field (that has seconds) to be displayed without showing the seconds. Spectrum Writer will round the time to the nearest minute.

You may also specify a "time picture" to change the formatting of time fields in your report. A time picture is similar to a regular numeric picture, except that it begins with TPIC or TP (rather than PIC or P). For example, to format a time field so that leading zeros in the hours are suppressed, you could use a time picture like this: COLUMNS: START–TIME(TPIC'Z9:99:99')

Time pictures can also specify decimal digits if needed for the time field:

COLUMNS: JOB–END(TP'ZZ:ZZ:Z9.99999')

By default, time fields are not totalled in reports. If you want to total a time field, you may specify the ACCUM parm in either the FIELD, COMPUTE or COLUMNS statement (just as with numeric fields). If you do print totals for a time field, you may also need to specify additional display digits for the hour portion of the total (in case the total is more than 99 hours):

COLUMNS: DURATION(ACCUM,TP'ZZZ9:99:99')

You may also choose to format time fields in your report as hours and decimal portions of an hour. That is, the time 04:15:00 would be displayed as 4.25 (4 and one–fourth hours).

The HOURS display format does this. There are also MINS and SECS formats to display time fields as a number of minutes or a number of seconds. The number of decimal digits printed with such display formats is the number of decimal digits specified in the FIELD or COMPUTE statement used to define the field (usually zero). To force a certain number of decimal digits to print with these display formats, use a COMPUTE statement to change a field's decimal precision. For example, to print START–TIME in hours, with three decimal digits, do this:

FIELD: START–TIME COL(10) TYPE(HHMMSS)
COMPUTE: X–START–TIME(3) = START–TIME
COLUMNS: X–START–TIME(HOURS)

You may use time fields in conditional expressions. They can be compared with other time fields or with time literals. Time literals must be expressed either as HH:MM or HH:MM:SS (with optional decimal parts of seconds also allowed: HH:MM:SS.NNN). Here are some examples of using time fields and time literals in INCLUDEIF statements:

INCLUDEIF: START–TIME > END–TIME
INCLUDEIF: START–TIME > 12:00
INCLUDEIF: LOG–TIME > 13:01:00.0 AND < 13:01:00.5

You may also use time fields in computational expressions. For example:

COMPUTE: DURATION = END–TIME – START–TIME

The above statement computes a time field called DURATION, whose value is the difference between END–TIME and START–TIME. For example, if END–TIME had a value of 17:30:45 and START–TIME was 17:25:35, then DURATION would have a value of 00:05:10.

If the start and end times might occur on different days, you should also convert the start and end dates into seconds and use those in the computation as well:

COMPUTE: DURATION = ((#MAKENUM(END–DATE) * 86400) + END–TIME)

– ((#MAKENUM(START–DATE) * 86400) + START–TIME)

Note: There are 86,400 seconds in one day.

When computing time fields, you are allowed to mix time fields and numeric fields in the computational expression. Any numeric fields (or numeric literals) in the expression are considered to represent a number of seconds. For example:

COMPUTE: NEXT–MINUTE = START–TIME + 60

The above statement creates a new time field call NEXT–MINUTE whose value is equal to START–TIME plus 60 seconds.

Two built–in functions are provided to allow you to convert time fields to numeric fields and vice verse. Use the #MAKENUM function to convert a time field into a numeric field. For example:

COMPUTE: START–SECONDS = #MAKENUM(START–TIME)

The above statement creates a new numeric field named START–SECONDS. If START–TIME contained 02:30:05, START–SECONDS' value would be 9005. (Two hours is 7200 seconds, 30 minutes is another 1800 seconds, plus the 5 seconds.)

To convert numeric fields (which are considered a number of seconds) into a time field, use the #MAKETIME function:

COMPUTE: END–TIME = #MAKETIME(END–SECONDS)

If END–SECONDS contained 3600, then END–TIME would be 01:00:00 (since 3600 seconds is one hour).

You can also use the #MAKETIME function to convert a character value (in HHMMSS format) into a time field. For example:

COMPUTE: END–TIME = #MAKETIME(CHAR–TOD)

If CHAR–TOD was a 6–byte character field containing 191059, then END–TIME would be a time field with a value of 19:10:59. Spectrum Writer has a built–in field named #HHMMSS which contains the system time that Spectrum Writer began running. You can use this field like any other time field in creating reports or PC files.

Spectrum Writer supports time fields based on the "Store Clock" (STCK) machine instruction. (Use the STCKTIME data type — in the FIELD statement — to define such a field.) STCK values contain the date and time in GMT. Spectrum Writer automatically converts STCKTIME times from GMT to local time. The hours added or subtracted to the GMT time are determined by your installation's system parm. To change this default, use the STCKADJ option to specify the number of hours that should be added to the STCKTIME time. For example, to suppress conversion and leave STCKTIME times in GMT, you could specify the following:

OPTIONS: STCKADJ(0)

Computing Times Like "30 Minutes Ago," "Last Hour", etc.

You can use Spectrum Writer’s powerful time-manipulation functions to compute times or time ranges based on the system time. For example, to select all of the sales made "today, in the last 30 minutes" we could use these statements:

COMPUTE: THIRTY-MINUTES-AGO = #INCTIME(–30, MINUTES)
INCLUDEIF: SALES-DATE = #TODAY AND SALES-TIME >= THIRTY-MINUTES-AGO

Similarly, to report on all sales made "last hour," you could use these statements:

COMPUTE: START-TIME = #BEGHOUR(#INCTIME(–1, HOUR))
COMPUTE: END-TIME = #ENDHOUR(#INCTIME(–1, HOUR))
INCLUDEIF: SALES–DATE = #TODAY AND SALES-TIME >= START-TIME AND <= END-TIME

However, if the "last hour" could have occured in a different day (as would be the case if you ran the job shortly after midnight), then you need an additional COMPUTE statement. It determines what the date was 30 minutes ago:

COMPUTE: START-TIME = #BEGHOUR(#INCTIME(–1, HOUR))
COMPUTE: END-TIME = #ENDHOUR(#INCTIME(–1, HOUR))
COMPUTE: START-DATE = #INCDATETIME(–1, HOUR)
INCLUDEIF: SALES–DATE = START-DATE AND SALES-TIME >= START-TIME AND <= END-TIME

You can find the complete syntax for all of these built-in functions, along with other time manipulation functions, in Appendix D, "Built-In Functions" on page 628.

Copyright 2017.
Pacific Systems Group.
All rights reserved.


Spectrum Writer 4GL - the economical alternative to SAS, Easytrieve, DYL-280...

Home | Products | Prices | Documentation | 30-Day Trials | Customer Reviews | Company
Send Your Comments or Questions