ALERT: Some images may not load properly within the Knowledge Base Article. If you see a broken image, please right-click and select 'Open image in a new tab'. We apologize for this inconvenience.

Using the CA Datacom/DB SQL function TIMESTAMPDIFF to compute dates with rounded results

book

Article ID: 212993

calendar_today

Updated On:

Products

Datacom/DB Datacom/AD Datacom

Issue/Introduction

While using the SQL function TIMESTAMPDIFF to compute the difference between two dates, I noticed that the result was not rounded to the unit of time I selected, but appears to have been truncated. For example, a date result of 32 days, 22 hours, 14 minutes was shown as 32 days. 

How can I use this function to get a rounded value? Are there other options or tips to using this?

Environment

Component : CA DATACOM SQL

Resolution

Overview

There are many uses for timestamps - where the date and time values are considered together as one value -  including general reporting, sequencing of records, performing actions based on durations or elapsed times, and others. Calculating the difference between two timestamp values in SQL using the TIMESTAMPDIFF function (called TSD in this article for simplicity) is an easy way to provide results in different levels of granularity between two timestamp values with both positive and negative results for these units:

  • Year
  • Quarter
  • Month
  • Week
  • Day
  • Hour
  • Minute
  • Second
  • Microsecond (6-decimal fractions of a second)

SYNTAX

The general syntax of this function is:

TIMESTAMPDIFF(units, time-date-value1, time-date-value2)

The units parameter is required, and the value returned by the function will have a data type of Decimal(31,0). The value returned is truncated and not rounded, and as noted, there is no fractional (decimal) value, which means that fractional values can be returned for all units except microseconds only by choosing a more granular unit (see the examples below).

The time-date-value used for each of the other parameters can be either a Timestamp or a Date value. If a Date value is used, the time components will default to zero (midnight).

EXAMPLES

For the following examples, please scroll down for more details, or download the attached file for the entire SQL query source.

  1. Display the difference between an entered date and the current date
  2. Display the difference between the date value of a timestamp column and the current date
  3. Display the difference between two timestamp values of entered dates
  4. Display the difference between two entered timestamps, rounded to days
  5. Display the difference between two timestamp columns
  6. Display the difference between two timestamp columns, rounded to days
  7. Display the difference between two timestamp columns, rounded to hours
  8. Multiple ways to calculate the difference between two columns and round to days, using all TSD units with a granularity smaller than days
  9. Different business use cases for TSD on records with a past date (some are repeated from above)

EXAMPLE DETAILS

Below are snippets of query text used for each example. Where a query has statements for multiple TSD units, only one is shown here – the entire query is found in the attachment. As mentioned above, symbolic values are used to ensure the same value is used throughout the query where the TSD function is used multiple times. This is a JCL feature available with z/OS 2.1 and higher, and requires the following:

  1. A JCL statement before the symbolic is used: // EXPORT SYMLIST=(*)
  2. The symbolic value defined in a SET statement: // SET DATE1A='2019-04-10' 
  3. The SYSIN DD statement requests symbol resolution (only for instream data):
    //SYSIN DD   *,SYMBOLS=JCLONLY
  4. The symbolic is used as usual in place of the data literal in the query:
    TimeStampDiff(YEAR, Date('&DATE1A')    , Current Date)   As Year

Most of these examples use the CA Datacom Dynamic System Table MUF_Systems_Stats and this should be available to all customers. The last - business cases - example uses a custom table that is likely similar to many tables found at customer sites. The key columns used in this last example are:

  • FIELD_A01 – a simple key column; here, 9 unique digits
  • FIELD_A03 – an 8-bye character column to hold a date in the format YYYYMMDD
  • FIELD_A04 – a 6-byte character column to hold a time in the format HHMMSS

Display the difference between an entered date and current date

TSD is used to compute the difference between a date entered with DATE(’2019-04-10’) and the current Special Register value of CURRENT DATE. Both values are dates only, with no time values. The TSD calculation will return values as if the time for each date was midnight (00:00:00.000000).

Query: TimeStampDiff(YEAR,       Date('&DATE1A')    , Current Date)   As Year

Result: YEAR,                             <                               2>, DEC(31,0) NOT NULL

Display the difference between the date value of a timestamp column and current date

TSD will compute the difference between the date that the MUF was started (using MUF_SYSTEMS_STATS column Begin_Time) and CURRENT DATE. The MUF start date is obtained through the DATE() function, and as above, all times will be as midnight.

Query: TimeStampDiff(HOUR,       Date(mss.begin_time), Current Date)  As Hour

Result: HOUR,                             <                              48>, DEC(31,0) NOT NULL

Display the difference between two timestamp values of entered dates.

Two timestamps are specified in the JCL – 13 Feb 2021 at 22.44.41, and 10 Apr 2021 at 22.28.03 – using Symbolic references for ease of entry. TSD will compute and return the difference for all possible unit designations.

//    SET DATE3A='2/13/2021 22.44.41'      
//    SET DATE3B='4/10/2021 22.28.03'     
//    SET DTFMT3='MM/DD/YYYY HH24.MI.SS'  

Query: TimeStampDiff(DAY,
    To_Date('&DATE3A','&DTFMT3'),
    To_Date('&DATE3B','&DTFMT3'))                              As Day

Result: DAY,                              <                              55>, DEC(31,0) NOT NULL   

Display the difference between two entered timestamps, rounded to days

The same dates as above are used here, and the result is rounded to the nearest day. This calculation is made by computing the difference in hours and then adding 12 hours. This is then divided by 24 giving the number of days, using a DEC(5,0) datatype. In addition, all TSD units smaller than days are shown with values as above.

Query: DEC((TimeStampDiff(HOUR,
    To_Date('&DATE3A','&DTFMT3'),
    To_Date('&DATE3B','&DTFMT3')) + 12) / 24,5,0)  As DyRound

Result: DYROUND,                          <    56>,             DEC(5,0) NOT NULL

Display the difference between two timestamp columns

TSD will compute the difference between two table columns with a datatype of timestamp, showing the result for all TSD units.

Query: TimeStampDiff(MINUTE,     Mss.Begin_Time,Mss.Current_Datetime) As Minute   

Result: MINUTE,                           <                            3242>, DEC(31,0) NOT NULL

Display the difference between two timestamp columns, rounded to days

The same columns are used here as above, showing rounding to the nearest day. Note that an alternative to adding 12 hours before the division is to add .5 days after the division. Here are both examples:

  • DEC((TimeStampDiff(HOUR,Mss.Begin_Time,Mss.Current_Datetime) + 12) / 24, 5,0)
  • DEC((TimeStampDiff(HOUR,Mss.Begin_Time,Mss.Current_Datetime)  / 24) + .5, 5,0)
Query: DEC((TimeStampDiff(HOUR,Mss.Begin_Time,Mss.Current_Datetime) + 12) / 24, 5,0)                                                      As DyRound 

Result: DYROUND,                          <     2>,             DEC(5,0) NOT NULL          

Display the difference between two timestamp columns, rounded to hours

Using the same columns as above, the TSD result will be rounded to the nearest hour.

Query: DEC((TimeStampDiff(MINUTE,Mss.Begin_Time,Mss.Current_Datetime) + 30) / 60, 5,0)                                                      As HrRound

Result: HRROUND,                          <    54>,             DEC(5,0) NOT NULL

Multiple ways to calculate the difference between two columns and round to days, using all TSD units with a granularity less than days

Using the same columns as above, showing the result rounded to the nearest day by calculating with the HOUR, MINUTE, SECOND, and MICROSECOND units.

Query: 
DEC((TimeStampDiff(MINUTE     ,Mss.Begin_Time,Mss.Current_Datetime) + 720) / 1440,5,0)       As ByMinute 
DEC((TimeStampDiff (SECOND, Mss. Begin_Time, Mss. Current_Datetime) + 43200) / 86400,5,0)    As BySecond 
Result: 
BYMINUTE,                         <     2>,             DEC(5,0) NOT NULL
BYSECOND,                         <     2>,             DEC(5,0) NOT NULL

Different business use cases for TSD on records with a past date (some are repeated from above)

TSD can be used along with other SQL functions to meet a number of business needs. For this query, only records with date/time values older than the current time are selected. Here are some of the processes done in this query:

Combining a date column and time column into a timestamp

Using the TO_DATE function along with a character date column (YYYYMMDD) concatenated to a character time column (HHMMSS) to produce a Timestamp datatype

Calculating age in days using only a date as a truncated integer

This is a simple TSD calculation using only the date column from a table, with the result being a DEC(5,0) value instead of the default (31,0) value.

Calculating age in days using date and time as the source with a truncated result.

By concatenating the date and time columns as the source of the TSD date value, the integer truncated TSD DAY calculation could be different from the previous date-only value.

Calculating age in days as a 2-decimal number

Using the concatenated date and time value from above to compute the TSD, the result is now truncated after two decimal positions.

Identifying aging buckets for each record

A common business case is to identify an aging bucket for selected records. Here, the date column for each record is used as input to the TSD function, and the resulting number of days is categorized in buckets: 0 to 90 days, 91 to 180 days, 181 to 365 days, 366 to 730 days, and older than 730 days. Note that the use of the CASE statement starts with the oldest bucket (highest value) and progresses to the newest (lowest value).

For records 7 days old or less, display age in rounded hours.

Using TSD to select only records with dates in the last 7 days, return a result of the age of the record in rounded hours, with an asterisk border to raise visibility.

Please refer to the attachment for this query to see the various columns and functions used. Here are a few lines of the result:

    RecKey RecDate  RecTm  RecTmStamp                 DyAge1 DyAge2 DyAge DEC 0-90 91-180 181-365 366-730 731+  7-Day Age Hrs   
__________ ________ ______ __________________________ ______ ______ _________ _________________________________ ________________
 252000114 20190508 124248 2019-05-08-12.42.48.000000    709    709    709.04                         4                         
 252000118 20210201 015233 2021-02-01-01.52.33.000000     74     74     74.50   1                                               
 252000120 20190410 214926 2019-04-10-21.49.26.000000    737    736    736.66                               5                   
 252000121 20200505 202718 2020-05-05-20.27.18.000000    346    345    345.70                 3                                 
 252000122 20200511 210803 2020-05-11-21.08.03.000000    340    339    339.70                 3                                 
 252000124 20210409 233347 2021-04-09-23.33.47.000000      7      6      6.58   1                                **** 00159 ****
 252000125 20190808 192806 2019-08-08-19.28.06.000000    617    616    616.75                         4                         
 252000127 20200712 233203 2020-07-12-23.32.03.000000    278    277    277.58                 3                                 
 252000137 20210102 213037 2021-01-02-21.30.37.000000    104    103    103.66         2                                         
 252000138 20190511 140714 2019-05-11-14.07.14.000000    706    706    706.00                         4                         
 252000139 20190305 205236 2019-03-05-20.52.36.000000    773    772    772.70                               5                   
 252000143 20210412 192100 2021-04-12-19.21.00.000000      4      3      3.79   1                                **** 00091 ****
 252000144 20210105 164320 2021-01-05-16.43.20.000000    101    100    100.87         2                                         

Additional Information

TSD can be used to produce many different kinds of results where differences between dates and times are needed. These have been only a few examples, and we would welcome more from the reader to add to this article.

For more information about the TIMESTAMPDIFF function, please refer to the CA Datacom/DB SQL Scalar Functions Documentation.

As always, please contact Broadcom support for CA Datacom if you have further questions.

Attachments

1618607477147__SQL-TIMESTAMPDIFF.txt get_app