Thursday, February 16, 2012

MySQL Convert Time in "Friendly Format" to Time Format

I use Stoneware to interact with my Database and I came across a situation where I needed to have the ability to have people input their time in a "human format" and have it be converted to MySQL's time format. Stoneware is GUI interface for interacting with databases, and I was very limited in what options I had available. There is a Date/Time field that is integrated into Stoneware which allows you to input a date AND a time, but not just a time. So although it would have worked for what I wanted, I would have had to instruct people to put an arbitrary date in, which is poor practice. So instead I had to find a way to allow people to write their time into a textbox and then convert the value into a time. It sounds easy enough, but there were a few caveats.

Although MySQL has a time( ) function for formatting numbers, it won't format the 'am' or 'pm' that will be typed in the box. So we need to first figure out if it is an "AM" or "PM", then if there is a PM, we need to add 12 hours to the time format. Since we want people to store their information as a string, we need to create two new fields to our table. So I have a startTime field that is a TIME field, and I have a rawStartTime field that is a string. I have the same setup for endTime. So here's what we have to do for MySQL. In stoneware, I have the person type their time in as 00:00 AM format, and I save that as rawStartTime then I have this run after the add (which I can do in Stoneware, but it could also be done via a trigger in MySQL):

update database.table set set startTime = if(left(‘rawStartTime’,4) like '%12%' and ‘rawStartTime’ like '%a%', addTime(time(left(‘rawStartTime’, (locate('m', ‘rawStartTime’)-2))), + '12:00'), if(‘rawStartTime’ LIKE '%P%' and left(‘rawEndTime’,4) not like '%12%', addTime(time(left(‘rawStartTime’, (locate('m', ‘rawStartTime’)-2))), + '12:00'), time(left(‘rawStartTime’,(locate('m', ‘rawStartTime’)-2))))), endTime = if(left(‘rawEndTime’,4) like '%12%' and ‘rawEndTime’ like '%a%', addTime(time(left(‘rawEndTime’, (locate('m', ‘rawEndTime’)-2))), + '12:00'), if(‘rawEndTime’ LIKE '%P%' and left(‘rawEndTime’,4) not like '%12%', addTime(time(left(‘rawEndTime’, (locate('m', ‘rawEndTime’)-2))), + '12:00'), time(left(‘rawEndTime’,(locate('m', ‘rawEndTime’)-2))))) ,  duration = timeDiff(endTime, startTime) where primaryID = 'primaryID';

I used the locate function to locate the A or P, then I subtracted one to read the numbers before those string values since the time value will still calculate with a space on the end. This means people can type times in various different formats, and it will still calculate. If you used a set number of 5 in your locate function for example, it won't calculate correctly if they type their time in as 1:00pm instead of 01:00pm or 1:00 pm. This allows the user to type it however they like (assuming they use an AM or PM after the time) and it will still calculate correctly. This will prevent many trouble calls on formatting issues.