Back Issues
The Director’s Chair
Advanced Technique
New Feature
Special Feature
FOCUS 101
By Larry Civelli and Cesare Petrizio
Adapter Advice
Customer Support
OS Feature
User Group Meeting Schedule

Working With Date-Time Data

Date-time data types provide a variety of display formats, which are discussed in detail in the Describing Data manual, along with syntax for using the various functions discussed in this article. As a result, you can now use data types such as the DB2 and Oracle timestamps in calculations, extract specific components, or even convert them into string formats through date-time functions introduced as part of this new functionality.

The new data types provide a variety of display formats that are discussed in detail in the Describing Data manual, along with syntax for using the various functions discussed in this article.

You can use date-time functions within a DEFINE, COMPUTE, or as part of a FOCUS function. The example below uses the date-time function HADD to increment components of a date-time field, in this case, a DB2 timestamp column:

MFD excerpt:

...
FILENAME=TIMESTMP,SUFFIX=SQLDS,$ 
 SEGNAME='TIMESTMP',SEGTYPE=S0,$  
…
FIELD=TIMESTMP    ,TIMESTMP    ,HYYMDm ,HYYMDm,MISSING=ON,$
…

The request: 

TABLE FILE TIMESTMP                                           
PRINT TIMESTMP 
 COMPUTE                             
  ADD_1HR/HYYMDm   = HADD (TIMESTMP,'HOUR',1,10,'HYYMDm');    
  ADD_3DAY/HYYMDm  = HADD (TIMESTMP,'DAY',3,10,'HYYMDm');    
  ADD_2MNTH/HYYMDm = HADD (TIMESTMP,'MONTH',2,10,'HYYMDm'); 
  ADD_5YR/HYYMDm   = HADD (TIMESTMP,'YEAR',5,10,'HYYMDm');    
WHERE ALPHA8 EQ '22222222'                                    
END

The output:

TIMESTMP    
-------- 
2003/10/31 12:26:45.123450 

ADD_1HR           
-------- 	    
2003/10/31 13:26:45.123450     

ADD_3DAY  
-------- 	
2003/11/03 12:26:45.123450

ADD_2MNTH
--------- 
2003/12/31 12:26:45.123450            

ADD_5YR                    
---------                   
2008/10/31 12:26:45.123450

To further illustrate the flexibility and usefulness of the new functionality, the following example includes a DB2 table with a column named TIMESTMP that has a DB2 timestamp data type. We wish to calculate the number of days and months that passed between the value in the DB2 table and the current date.

Prior to release 7.1, such timestamp columns for DB2 were described in the MFD with Usage and Actual of A26. This meant that any calculations on these columns within FOCUS required previous extraction and conversion of the various date-time components into formats that could be used in calculations. Now, you can perform such calculations directly on the date-time values through one of the supplied functions. The next example illustrates the FOCUS date-time functions HGETC and HDIFF:

HGETC_ extracts the current date and time and uses it as a date-time value
HDIFF_ extracts the number of units (i.e. DAYS, MONTHS) between two values

The FUNCTION FOCEXEC below defines two functions, DIFF_DAY and DIFF_MTH, each of which accepts two parameters as input: beginning and ending date-time values. The results of both are the numbers of days or months that passed between the two date-time values.

FUNCTION FOCEXEC:

DEFINE FUNCTION * CLEAR
-RUN        
          
-* CALCULATE THE DIFFERENCE IN MONTHS 
DEFINE FUNCTION
 DIFF_MTH (STDATE/HYYMDm, ENDATE/HYYMDm) 
 DIFF_MTH = HDIFF(ENDATE, STDATE, 'MONTH', DIFF_MTH);
END              
-* CALCULATE THE DIFFERENCE IN DAYS                  
DEFINE FUNCTION                              
 DIFF_DAY (STDATE/HYYMDm, ENDATE/HYYMDm)             
 DIFF_DAY = HDIFF(ENDATE, STDATE, 'DAY', DIFF_DAY);  
END

TABLE request that utilizes above the functions:

-INCLUDE FUNCTION                       
-RUN                       
-* GET CURRENT DATE/TIME             
DEFINE FILE TIMESTMP                    
CURR/HYYMDm = HGETC(10, 'HYYMDm');      
END                                     
                                        
TABLE FILE TIMESTMP                     
PRINT TIMESTMP CURR  COMPUTE            
-* CALL THE FUNCTIONS, PASSING DB2 COLUMN AND DEFINED FLD
DIFF_MONTHS = DIFF_MTH(TIMESTMP, CURR); 
DIFF_DAYS   = DIFF_DAY(TIMESTMP, CURR); 
END

We used ­INCLUDE to execute the FUNCTION FOCEXEC, which defined the functions. We then reused the code, simply providing values for the function parameters. By using a profile, we could give all of our users access to these functions as well. Here is the sample output:

TIMESTMP                    CURR                           DIFF_MONTHS
--------                    ----                           -----------
2003/05/29 05:29:59.000028  2003/12/10 15:38:27.000000     7.00

DIFF_DAYS  
---------  
 195.00

Keep in mind that FOCUS-specific functions like these cannot be understood by the native RDBMS; as a result, any calculations involving these functions will not be included in the SQL. Rather, the SQL generated by such a request will pull back the timestamp data and then apply the functions locally. As such, all rules applicable to DEFINE fields, with regards to RDBMS optimization, apply equally to the use of these date-time functions: do not use them as expressions in performing selection, aggregation, or sorting operations.

On the other hand, it’s worth mentioning that WHERE and IF statements do support testing for date-time values and can be optimized in the SQL.

WHERE supports the DT function: 

DT(date_string [ time_string ]) 
DT(time_string [ date_string ])

IF supports a string:

‘date_string [ time_string ] ‘
‘ time_string [ date_string ] ‘

For example:

WHERE TIMESTMP GT DT(2000/01/01 12:00:59) 
IF    TIMESTMP GT '2000/01/01 12:00:59'

will be included in the SQL as: 

SELECT T1."TIMESTMP" FROM "PMSLCC"."TIMESTMP" T1 WHERE
(T1."TIMESTMP" > '2000-01-01-12.00.59.000000');

Back Next