For the third in the series and possibly the last I am going to look at the little doosie that was presented to me the other day where the seconds of time passed from a system are presented as an integer and needed converting to a time, when I say a time I mean a time that will add up in MS Excel where the hour part could be over 24, so not strictly a valid DB2 time.

We have a new VOIP phone system here at work and apart from all the times (call start, call end, logged on, logged off) being in the number of seconds from 01/01/1970 (Unix time) so DB2 can handle this very well adding the timestamp of 01/01/1970 with the seconds and you get the correct timestamp of the call. The other interesting thing it does is it records all call times in seconds and this needs converting to hh:mm:ss for reporting purposes. To do this I was reminded by a colleague of the MOD function that DB2 has. MOD returns the remainder as an integer, opposed to the number of times the value will dived.

So you can do something like this to get the hours, minuits and seconds as a VALUES statement where <INTEGER> is the total seconds:

VALUES TRIM(VARCHAR((<INTEGER>/3600))) || 'h' ||TRIM(VARCHAR((MOD(<INTEGER>,3600))/60)) || 'm' ||TRIM(VARCHAR(MOD(MOD(<INTEGER>,3600/60), 60))) || 's'

If you substituted <INTEGER> for 3681 it returns the result:

1 ------- 1h1m21s

Which as far as I can work out is correct. There are 3600 seconds in an hour, so to work out hours then you divide seconds by 3600, to get minuits you use MOD to get the remainder of the passed in value and then divide by sixty (the result of MOD will still be in seconds) the result of the division because it is an integer will be returned as an integer so there are some second s remaining. To work out the seconds remaining then you need to MOD the value from the working out the number of minuits to get the seconds.

So then the next step is too produce a function that returns a time:

CREATE FUNCTION GLOBAL.GET_TIME (ININ_SECONDSINTEGER)DETERMINISTICNO EXTERNAL ACTIONRETURNSVARCHAR(10)LANGUAGE SQLBEGIN ATOMIC----------------------------------------------------------------------------------------------------------------------------------------------------------Phil C - 14/06/2012--Takes in an integer and returns a varchar representation of a time for-- use in excel as opposed to anything beging a valid DB2 time type------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Create some varsDECLAREOUT_TIMEVARCHAR(10);DECLAREHOUR_PARTINTEGER;DECLAREMIN_PARTINTEGER;DECLARESEC_PARTINTEGER;DECLAREHOUR_PART_VVARCHAR(5);DECLAREMIN_PART_VVARCHAR(3);DECLARESEC_PART_VVARCHAR(2);----------------------------------------------------------------------------------------------------------------------------------------------------------Set some varsSETHOUR_PART = (IN_SECONDS/3600);SETMIN_PART = (MOD(IN_SECONDS,3600)/60);SETSEC_PART = (MOD(MOD(IN_SECONDS,3600/60), 60));----------------------------------------------------------------------------------------------------------------------------------------------------------Fattern the values outIF(HOUR_PART < 10)THENSETHOUR_PART_V = '0' ||TRIM(VARCHAR(HOUR_PART)) || ':';ELSESETHOUR_PART_V =TRIM(VARCHAR(HOUR_PART)) || ':';END IF;IF(MIN_PART < 10)THENSETMIN_PART_V = '0' ||TRIM(VARCHAR(MIN_PART)) || ':';ELSESETMIN_PART_V =TRIM(VARCHAR(MIN_PART)) || ':';END IF;IF(SEC_PART < 10)THENSETSEC_PART_V = '0' ||TRIM(VARCHAR(SEC_PART));ELSESETSEC_PART_V =TRIM(VARCHAR(SEC_PART));END IF;----------------------------------------------------------------------------------------------------------------------------------------------------------Create the end valueSETOUT_TIME = (HOUR_PART_V || MIN_PART_V || SEC_PART_V);----------------------------------------------------------------------------------------------------------------------------------------------------------Return the valueRETURNOUT_TIME;END

So this will return a time like result up to 9999:59:59 and you will be able to export a result set to Excel or the like and then it can be added up. DB2 will not accept this as a time! So when you run this:

VALUES GLOBAL.GET_TIME(3681)

Returns

1 -------- 01:01:21

Which is the same result as at the start so I think the function is a good one. Till next time happy UDF’ing

Coverting seconds to a time? Too easy in DB2.

TIME(‘00.00’) + () SECONDS

In DB2 10 for z/OS can then use VARCHAR_FORMAT to put out the result in a wide variety of string representations, or can use TRANSLATE trick on HEX function applied to the time value, to insert any characters you like in whatever positions, e.g.

TRANSLATE(‘127348569’,HEX(TIME(‘00.00’) + (3681) SECONDS) CONCAT ‘hms’, ‘123456789’)

To explain first char ‘1’ is looked up in 3rd argument, found in first position and replaced by 1st hex digit of hours. 3rd char ‘7’ is looked up in 3rd argument, found in position 7 and replaced by 7th char of argument 2 which is ‘h’, etc.

Result should be ’01h01m21s’.

Unfortunately I can’t test right this minute so forgive me if I made a slight syntax fault.

Another way which might perform better than TRANSLATE:

INSERT(INSERT(HEX(TIME(‘00.00’) + (3681) SECONDS), 3, 0, ‘h’), 6, 0, ‘m’)!!’s’

If you really don’t want leading zeros then I would change to

TRIM(VARCHAR(HOUR(TIME(‘00.00′) + (3681) SECONDS)))!!’h’

!!TRIM(VARCHAR(MINUTE(TIME(‘00.00′) + (3681) SECONDS)))!!’m’

!!TRIM(VARCHAR(SECOND(TIME(‘00.00′) + (3681) SECONDS)))!!’s’

to use DB2’s internal time manipulation functions

() seconds contained inside the brackets but it disappeared