Timestamp Differences UDF

Database
Senior Apprentice

Timestamp Differences UDF

Hi all,

 

There have been a couple of posts recently asking about finding the difference between two Timestamp values in seconds or minutes etc.

 

The following UDF is base don one of my answers to a post but is now extended to allow the user to specify the units (seconds, hours, minutes etc.)

 

Feel free to use as you wish.

 

The code to create UDF itself:

REPLACE FUNCTION waTSDiff(start_ts TIMESTAMP(6),end_ts TIMESTAMP(6),diff_units VARCHAR(7))
-- Purpose: Return the difference between two supplied TimeStamp values in the requested units.
--
-- Requested units: This is coded for most (all?) of the obvious units and their abbreviations.
--   Accepted units are: S, Sec, Secs, Second, Seconds
--                       M, Min, Mins, Minute, Minutes
--                       H, HR, HRs, Hour, Hours
--                       D, Day, Days
--   The 'units' can be in any case.
--
-- Transaction Semantics/Mode: This can be used in Teradata mode and ANSI mode.
--
-- Donated to the 'Teradata Community' by David Wellman - Ward Analytics.
-- This code is supplied 'as is' without warranty expressed or implied.
--
-- Change Log
-- 2017-10-26  Dave W.  1.0.0  Original coding.
RETURNS DECIMAL(38,6)
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
SPECIFIC waTSDiff
RETURNS NULL ON NULL INPUT
SQL SECURITY DEFINER
COLLATION INVOKER
INLINE TYPE 1
RETURN 
   CASE 
   WHEN diff_units (UPPERCASE) IN ('S','SEC','SECS','SECOND','SECONDS') THEN
     (86400 * (CAST(end_ts AS DATE) - CAST(start_ts AS DATE))
     + 3600 * (EXTRACT(HOUR FROM end_ts) - EXTRACT(HOUR FROM start_ts))
	 + 60 * (EXTRACT(MINUTE FROM end_ts) - EXTRACT(MINUTE FROM start_ts))
	 +  1 * (EXTRACT(SECOND FROM end_ts) - EXTRACT(SECOND FROM start_ts))
	 )
   WHEN diff_units (UPPERCASE) IN ('M','MIN','MINS','MINUTE','MINUTES') THEN
     (86400 * (CAST(end_ts AS DATE) - CAST(start_ts AS DATE))
     + 3600 * (EXTRACT(HOUR FROM end_ts) - EXTRACT(HOUR FROM start_ts))
	 + 60 * (EXTRACT(MINUTE FROM end_ts) - EXTRACT(MINUTE FROM start_ts))
	 +  1 * (EXTRACT(SECOND FROM end_ts) - EXTRACT(SECOND FROM start_ts))
	 ) / 60.00
   WHEN diff_units (UPPERCASE) IN ('H','HR','HRS','HOUR','HOURS') THEN
     (86400 * (CAST(end_ts AS DATE) - CAST(start_ts AS DATE))
     + 3600 * (EXTRACT(HOUR FROM end_ts) - EXTRACT(HOUR FROM start_ts))
	 + 60 * (EXTRACT(MINUTE FROM end_ts) - EXTRACT(MINUTE FROM start_ts))
	 +  1 * (EXTRACT(SECOND FROM end_ts) - EXTRACT(SECOND FROM start_ts))
	 ) / 3600.00
   WHEN diff_units (UPPERCASE) IN ('D','DAY','DAYS') THEN
     (86400 * (CAST(end_ts AS DATE) - CAST(start_ts AS DATE))
     + 3600 * (EXTRACT(HOUR FROM end_ts) - EXTRACT(HOUR FROM start_ts))
	 + 60 * (EXTRACT(MINUTE FROM end_ts) - EXTRACT(MINUTE FROM start_ts))
	 +  1 * (EXTRACT(SECOND FROM end_ts) - EXTRACT(SECOND FROM start_ts))
	 ) / 86400.00
   ELSE NULL
   END
;

COMMENT ON FUNCTION waTSDiff AS 'Returns the difference between two TimeStamp values in the requested units.'

Some examples of using it are:

SELECT  TIMESTAMP '2017-10-01 10:00:00' AS ts_start
          ,TIMESTAMP '2017-10-01 10:30:15' AS ts_end
		  ,waTSDiff(ts_start ,ts_end,'s') AS tsdiff_s1
		  ,waTSDiff(ts_start ,ts_end,'m') AS tsdiff_m1
		  ,waTSDiff(ts_start ,ts_end,'h') AS tsdiff_h1;

For me an obvious use case is when looking at DBQL data:

SELECT TOP 10
   queryid 
  ,starttime
  ,firstresptime
  ,watsdiff(starttime,firstresptime,'s') AS elapsed_time_secs
  ,watsdiff(starttime,firstresptime,'m') AS elapsed_time_mins
FROM dbc.dbqlogtbl
WHERE elapsed_time_secs > 2;

But anywhere you're dealing with TimeStamp values should be a good candidate for using this.

 

Once you're happy with this then place it in the SYSLIB database, grant 'EXECUTE FUNCTION' to it and all users can use it.

 

As usual all comments are welcome.

 

Cheers,

Dave

 

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
2 REPLIES
Junior Contributor

Re: Timestamp Differences UDF

Hi Dave,

this can be simplified to a single CASE :-)

   (  86400 * (Cast(end_ts AS DATE) - Cast(start_ts AS DATE))
     + 3600 * (Extract(HOUR   From end_ts) - Extract(HOUR   From start_ts))
     +   60 * (Extract(MINUTE From end_ts) - Extract(MINUTE From start_ts))
     +    1 * (Extract(SECOND From end_ts) - Extract(SECOND From start_ts))
   ) /
   CASE 
     WHEN diff_units (Uppercase) IN ('S','SEC','SECS','SECOND','SECONDS') THEN     1  
     WHEN diff_units (Uppercase) IN ('M','MIN','MINS','MINUTE','MINUTES') THEN    60
     WHEN diff_units (Uppercase) IN ('H','HR','HRS','HOUR','HOURS')       THEN  3600
     WHEN diff_units (Uppercase) IN ('D','DAY','DAYS')                    THEN 86400
     ELSE NULL
   END

 

Senior Apprentice

Re: Timestamp Differences UDF

Yes it can. Same result, simpler code. You now have:

REPLACE FUNCTION waTSDiff(start_ts TIMESTAMP(6),end_ts TIMESTAMP(6),diff_units VARCHAR(7))
-- Purpose: Return the difference between two supplied TimeStamp values in the requested units.
--
-- Requested units: This is coded for most (all?) of the obvious units and their abbreviations.
--   Accepted units are: S, Sec, Secs, Second, Seconds
--                       M, Min, Mins, Minute, Minutes
--                       H, HR, HRs, Hour, Hours
--                       D, Day, Days
--   The 'units' can be in any case.
--
-- Transaction Semantics/Mode: This can be used in Teradata mode and ANSI mode.
--
-- Donated to the 'Teradata Community' by David Wellman - Ward Analytics.
-- This code is supplied 'as is' without warranty expressed or implied.
--
-- Change Log
-- 2017-10-26  Dave W.  1.0.0  Original coding.
RETURNS DECIMAL(38,6)
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
SPECIFIC waTSDiff
RETURNS NULL ON NULL INPUT
SQL SECURITY DEFINER
COLLATION INVOKER
INLINE TYPE 1
RETURN 
     (86400 * (CAST(end_ts AS DATE) - CAST(start_ts AS DATE))
     + 3600 * (EXTRACT(HOUR FROM end_ts) - EXTRACT(HOUR FROM start_ts))
	 + 60 * (EXTRACT(MINUTE FROM end_ts) - EXTRACT(MINUTE FROM start_ts))
	 +  1 * (EXTRACT(SECOND FROM end_ts) - EXTRACT(SECOND FROM start_ts))
	 ) /
   CASE 
   WHEN diff_units (UPPERCASE) IN ('S','SEC','SECS','SECOND','SECONDS') THEN 1
   WHEN diff_units (UPPERCASE) IN ('M','MIN','MINS','MINUTE','MINUTES') THEN 60
   WHEN diff_units (UPPERCASE) IN ('H','HR','HRS','HOUR','HOURS') THEN 3600
   WHEN diff_units (UPPERCASE) IN ('D','DAY','DAYS') THEN 86400
   ELSE NULL
   END
;

COMMENT ON FUNCTION waTSDiff AS 'Returns the difference between two TimeStamp values in the requested units.';

Enjoy!

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com