Can’t Believe DB2 does not have this function – Convert seconds to hours, minutes and seconds

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 (IN IN_SECONDS INTEGER)
DETERMINISTIC
NO EXTERNAL ACTION
RETURNS VARCHAR(10)
LANGUAGE SQL
BEGIN 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 vars
    DECLARE OUT_TIME VARCHAR(10);
    DECLARE HOUR_PART INTEGER;
    DECLARE MIN_PART INTEGER;
    DECLARE SEC_PART INTEGER;
    DECLARE HOUR_PART_V VARCHAR(5);
    DECLARE MIN_PART_V VARCHAR(3);
    DECLARE SEC_PART_V VARCHAR(2);

    ----------------------------------------------------------------------------
    ----------------------------------------------------------------------------
    --Set some vars
    SET HOUR_PART = (IN_SECONDS / 3600);
    SET MIN_PART = (MOD(IN_SECONDS,3600) / 60);
    SET SEC_PART = (MOD(MOD(IN_SECONDS,3600 / 60), 60));

    ----------------------------------------------------------------------------
    ----------------------------------------------------------------------------
    --Fattern the values out
    IF (HOUR_PART < 10) THEN
        SET HOUR_PART_V = '0' || TRIM(VARCHAR(HOUR_PART)) || ':';
    ELSE
        SET HOUR_PART_V = TRIM(VARCHAR(HOUR_PART)) || ':';
    END IF;

    IF (MIN_PART < 10) THEN
        SET MIN_PART_V = '0' || TRIM(VARCHAR(MIN_PART)) || ':';
    ELSE
        SET MIN_PART_V = TRIM(VARCHAR(MIN_PART)) || ':';
    END IF;

    IF (SEC_PART < 10) THEN
        SET SEC_PART_V = '0' || TRIM(VARCHAR(SEC_PART));
    ELSE
        SET SEC_PART_V = TRIM(VARCHAR(SEC_PART));
    END IF;

    ----------------------------------------------------------------------------
    ----------------------------------------------------------------------------
    --Create the end value
    SET OUT_TIME = (HOUR_PART_V || MIN_PART_V || SEC_PART_V);

    ----------------------------------------------------------------------------
    ----------------------------------------------------------------------------
    --Return the value
    RETURN OUT_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

Can’t Believe DB2 does not have this function – Friendly Dates

So our marketing gurus at where I work needed a english friendlier format (thanks @_PeUR on the description of my last post via twitter) for the text in the emails that they send out, personally I would be happy with a dd/mm/yyyy or yyyy-mm-dd but suppose it is what you are used too. Our place is a little unusual as the marketers are allowed access to the data to design their own SQL sets to create the mailing lists. I have seen all sorts of SQL to do this some good some bad, but mostly it makes the SQL harder to read for no added functionality that i sometimes have to debug. Therefore I decided to create a function so that it at least can now just be an inline function. This function takes in an ISO or compatible with the DATE data type and returns a long data ddth MMM YYYY so 13/06/2012 becomes 20th June 2012.

CREATE FUNCTION GLOBAL.CONVERT_DATE_LONG (IN_DATE DATE)
DETERMINISTIC
NO EXTERNAL ACTION
RETURNS VARCHAR(30)
LANGUAGE SQL
BEGIN ATOMIC
    ----------------------------------------------------------------------------
    ----------------------------------------------------------------------------
    --Takes Date in creates a long date
    ----------------------------------------------------------------------------
    ----------------------------------------------------------------------------

    ----------------------------------------------------------------------------
    ----------------------------------------------------------------------------
    --Declare Vars
    DECLARE OUT_DATE VARCHAR(30);
    DECLARE DATE_PART VARCHAR(4);
    DECLARE MONTH_PART VARCHAR(20);
    DECLARE YEAR_PART  VARCHAR(4);

    IF(IN_DATE IS NOT NULL) THEN
        ----------------------------------------------------------------------------
        ----------------------------------------------------------------------------
        --Set some vars
            --Get the Base Parts
            SET DATE_PART = DAY(IN_DATE, 2);
            SET MONTH_PART = MONTHNAME(IN_DATE);
            SET YEAR_PART = YEAR(IN_DATE,4);

            --Remove the 0 from the front of the date part
            IF(DATE_PART IN ('01','02','03','04','05','06','07','08','09')) THEN
                SET DATE_PART = RIGHT(DATE_PART,1);
            END IF;

            --Put the th nd or rd in the DATE_PART
            IF(DATE_PART IN ('3','23')) THEN 
                SET DATE_PART = DATE_PART || 'rd';
            ELSEIF (DATE_PART IN ('2','22')) THEN
                SET DATE_PART = DATE_PART || 'nd';
            ELSEIF (DATE_PART IN ('1','21')) THEN
                SET DATE_PART = DATE_PART || 'st';
            ELSE 
                SET DATE_PART = DATE_PART || 'th';
            END IF;

        ----------------------------------------------------------------------------
        ----------------------------------------------------------------------------
        --Set and return the result    
        SET OUT_DATE = DATE_PART || ' ' || MONTH_PART || ' ' || YEAR_PART;

        RETURN OUT_DATE;
   ELSE
        RETURN NULL;
   END IF;

END


Is it that I cant believe that there is not the function or does everyone have something similar for the audience that they deal with most often expect to see their date or time formats in?

Can’t Believe DB2 does not have this function – Convert Times

Seen as though DB2 LUW has had a major update 10.1, and I have not got round to blogging about it thought there was no point as everyone else has done it to death by now and maybe I can at later date if I see a hole someone has missed. Over the last few day I have been asked to some interesting things with “my” data like converting times to a more “friendly” format

So I needed to convert my nicely cleansed times (19:37:45 or 04:23:32) into a time format that is more compatible with your average email reader (2:30pm or 5:20am) as opposed to  a techie that might be all right with the nice 24 hour times. I did a lot of searching around and I could not find any inbuilt functions of DB2 LUW that would do this and so I had to create my own user defined function. Please if you know of anything feel free to correct me, but searching the info center then there was nothing.

CREATE FUNCTION GLOBAL.CONVERT_TIME_12H(IN_TIME TIME)
DETERMINISTIC
NO EXTERNAL ACTION
RETURNS VARCHAR(10)
LANGUAGE SQL
BEGIN ATOMIC
    ----------------------------------------------------------------------------
    ----------------------------------------------------------------------------
    --Takes 24hrs time in gives back 12hrs time suffixed with AM or PM
    ----------------------------------------------------------------------------
    ----------------------------------------------------------------------------
    DECLARE OUT_TIME VARCHAR(10);

    IF(INT(LEFT(IN_TIME,2)) <= 12) THEN
        SET OUT_TIME = LEFT(IN_TIME,5) || 'am';

        IF(INT(LEFT(IN_TIME,2)) IN (10,11)) THEN
            RETURN REPLACE(OUT_TIME,'.',':');
        ELSE
            RETURN REPLACE(SUBSTR(OUT_TIME,1),'.',':');
        END IF;

    ELSE
        IF(INT(LEFT(IN_TIME,2)) = 24) THEN
            SET OUT_TIME = '00:00am';
        ELSE
            SET OUT_TIME = LEFT((IN_TIME - 12 HOURS),5) || 'pm';
        END IF;

        RETURN REPLACE(OUT_TIME,'.',':');
    END IF;

END

As you can see it takes in standard DB2 time, and I think managed to get all the gotchas like midnight not being 12 hours less. As I said before if you know of a function to do this in DB2 that is built in I would love to know.