Using the Datacom/DB SQL function TIMESTAMPDIFF to compute dates with rounded results
Article ID: 212993
Updated On:
Products
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 : DATACOM SQL
Component : Datacom/DB
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.
- Display the difference between an entered date and the current date
- Display the difference between the date value of a timestamp column and the current date
- Display the difference between two timestamp values of entered dates
- Display the difference between two entered timestamps, rounded to days
- Display the difference between two timestamp columns
- Display the difference between two timestamp columns, rounded to days
- Display the difference between two timestamp columns, rounded to hours
- Multiple ways to calculate the difference between two columns and round to days, using all TSD units with a granularity smaller than days
- 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:
- A JCL statement before the symbolic is used: // EXPORT SYMLIST=(*)
- The symbolic value defined in a SET statement: // SET DATE1A='2019-04-10'
- The SYSIN DD statement requests symbol resolution (only for instream data):
//SYSIN DD *,SYMBOLS=JCLONLY - 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 Datacom/DB SQL Scalar Functions Documentation.
As always, please contact Broadcom support for Datacom if you have further questions.
Attachments
Feedback
