How do I split a time range into days using stored procedure in informix 11.5?

Question

I am facing an issue with splitting a time range into days in informix 11.5. My requirement is, I will get two dates for e.g 06/05/2015 11:00:00 to 06/07/2015 03:30:00. I have to split these time range into the following in a stored procedure,

1. 06/05/2015 11:00:00 - 06/05/2015 23:59:59
2. 06/06/2015 00:00:00 - 06/06/2015 23:59:59
3. 06/07/2015 00:00:00 - 06/07/2015 03:30:00

I am new to stored procedure in informix. Your help will be much appreciated.

Answer 1

There are multiple ways to do it, depending on how you want to consume the values. Here's one possible way to do it:

CREATE PROCEDURE split_date_range(t0 DATETIME YEAR TO SECOND,
                                  t1 DATETIME YEAR TO SECOND)
    RETURNING DATETIME YEAR TO SECOND AS t_begin,
              DATETIME YEAR TO SECOND AS t_end;

    DEFINE tb DATETIME YEAR TO SECOND;
    DEFINE te DATETIME YEAR TO SECOND;
    DEFINE d1 DATE;
    DEFINE de DATE;
    DEFINE msg VARCHAR(72);

    IF t0 IS NULL OR t1 IS NULL THEN
        RAISE EXCEPTION -746, 0, "NULL value passed where not permitted";
    END IF;
    IF t0 > t1 THEN
        LET msg = "t0 = " || t0 || " should not be bigger than t1 " || t1;
        RAISE EXCEPTION -746, 0, msg;
    END IF;

    IF EXTEND(t0, YEAR TO DAY) = EXTEND(t1, YEAR TO DAY) THEN
        RETURN t0, t1;
    ELSE
        LET te = EXTEND(EXTEND(t0, YEAR TO DAY), YEAR TO SECOND) +
                 INTERVAL(23:59:59) HOUR TO SECOND;
        RETURN t0, te WITH RESUME;
        LET d1 = DATE(EXTEND(t0, YEAR TO DAY)) + 1;
        LET de = DATE(EXTEND(t1, YEAR TO DAY));
        WHILE d1 < de
            LET tb = EXTEND(d1, YEAR TO SECOND);
            LET te = tb + INTERVAL(23:59:59) HOUR TO SECOND;
            RETURN tb, te WITH RESUME;
            LET d1 = d1 + 1;
        END WHILE;
        LET tb = EXTEND(d1, YEAR TO DAY);
        RETURN tb, t1;
    END IF;

END PROCEDURE;

The basic logic shown is:

• Check for valid invocation. (Error messages should probably include function name for better trackability. Error -746 is for user-defined messages. The size of the SQLERRM element of the SQLCA — which is where the error message ends up — is 72, hence that VARCHAR(72).)
• If the start and end time are on the same day, it returns the two values.
• Otherwise, it returns the start time and the end of the same day (and the WITH RESUME means that it will continue after that when called again),
• and then for each day that is after the start time and is before the end day, it returns the start and end of the day,
• and then for the last day, it returns midnight and the end time on the end date.

You could then use something like:

EXECUTE PROCEDURE split_date_range(DATETIME(2015-06-05 11:00:00) YEAR TO SECOND,
                                   DATETIME(2015-06-07 03:30:00) YEAR TO SECOND));

to generate:

t_begin              t_end
2015-06-05 11:00:00  2015-06-05 23:59:59
2015-06-06 00:00:00  2015-06-06 23:59:59
2015-06-07 00:00:00  2015-06-07 03:30:00