Writing an Int To Time Function for MySQL

When you have dates stored like "100" and "930", what is one to do?

I ran into an "annoyance" this week with a very weird date format for some real estate data on a client's website. Take a look at the following table:

OpenHouse Data from Matrix RETS server
Openhouseid StartDate StartTime EndTime
1 2018-10-28 100 200
2 2018-10-28 1200 1430
3 2018-10-27 1430 1600

What is not wrong with this awkward time format? It uses military time with no leading zeros and arbitrary use of "100" and "1300" to denote 1PM. I could not conceive of any way to resolve this inconsistent mess of times without resorting to a manually coded SQL function to contain business logic and convert the integers to proper "human readable" time formats.

The Function Signature

My function will take in a string (actually integer, but MySQL didn't care) of up to four digits - see examples above. It will return a string of 6-7 digits, like "12:00pm" or "10:30am".

CREATE FUNCTION intToTimeStr(theTime CHAR(4))
RETURNS CHAR(7)
BEGIN
// convert times here
END;

Oh, Look At the Time!

The bulk of my work consisted of "reading" the time as military time. I first checked the length of the string, knowing that a 3-digit string always meant pre-noon, and that a 4-digit string meant 12pm or later. (This actually was not always the case, so I had to deal with 3-digit afternoon times later.)

DECLARE timeHours CHAR(2);
DECLARE timeMinutes CHAR(2);

-- if it's only a 3-digit time string, then no leading zero.
SET hourDigits=IF(LENGTH(theTime) > 3, 2, 1);
-- if hours more than 12, subtract 12 to get standard time, else leave as pre-noon time.
SET timeHours=IF(LEFT(theTime,hourDigits) > 12, LEFT(theTime,hourDigits) - 12, LEFT(theTime,hourDigits));
-- minutes are always, consistently the last 2 digits.
SET timeMinutes=RIGHT(theTime,2);

Is It Morning Yet?

Next I checked the hours to determine whether the time should be expressed as "am" or "pm". For the purpose of this data - real estate open houses - it became obvious that all of the 100-800 values meant pm, NOT am!

IF hourDigits < 8 OR hourDigits > 12 THEN
    SET amPM = "pm";
ELSE
    SET amPM = "am";
END IF;

Dealing With Stupidity

Finally, there were a few time values stored as a literal zero, which I wasn't sure what to do with. Does "0" mean noon? Does it mean no start time for these open houses? I settled on 12am as the most decent return value, though it is obviously not ideal.

-- handle stupid weird edge cases
IF theTime="0" THEN
    RETURN "12:00am";
ELSE
    RETURN CONCAT( timeHours, ":",timeMinutes, amPM );
END IF;

The Full Code

Here's the whole kit and caboodle. It is not as much code as it looks like!

CREATE FUNCTION intToTimeStr(theTime CHAR(4))
RETURNS CHAR(7)
BEGIN
    DECLARE hourDigits INT(1);
    DECLARE timeHours CHAR(2);
    DECLARE timeMinutes CHAR(2);
    DECLARE amPM CHAR(4);

    -- if it's only a 3-digit time string, then no leading zero.
    SET hourDigits=IF(LENGTH(theTime) > 3, 2, 1);
    -- if hours more than 12, subtract 12 to get standard time, else leave as am time.
    SET timeHours=IF(LEFT(theTime,hourDigits) > 12, LEFT(theTime,hourDigits) - 12, LEFT(theTime,hourDigits));
    -- minutes are always, consistently the last 2 digits.
    SET timeMinutes=RIGHT(theTime,2);

    IF hourDigits < 8 OR hourDigits >= 12 THEN
        SET amPM = "pm";
    ELSE
        SET amPM = "am";
    END IF;

    -- handle stupid weird edge cases
    IF theTime="0" THEN
        RETURN "12:00am";
    ELSE
        RETURN CONCAT( timeHours, ":",timeMinutes, amPM );
    END IF;
END;

The End Result

This function helped me resolve some very annoying data values with no extra fuss in my backend code. I was very happy, not least because this is one of the first real-world use cases I have seen for functions in MySQL.

October 27, 2018

« How to authenticate via JWT in Postman - Four Things That Make Great Developers Great »