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.

SoundPool

I was trying to implement the SoundPool class to play some .ogg files in my application, and I noticed there was a lack of documentation regarding this class, so once I got it to work I thought I would post some information in case anyone else was struggling with the same class. 


Just create the variables you need to use and reference the buttons you'll use to start and stop the audio : 

public class SimpleApp extends Activity {
    SoundPool mSoundPool;
    HashMap<Integer, Integer> mSoundPoolMap;
    AudioManager  mAudioManager;
    float streamVolume;
    int streamID;

    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.main);
      
        final Button startButton = (Button)findViewById(R.id.startButton);
        final Button stopButton = (Button)findViewById(R.id.stopButton);

setupSounds();
}

Then setup the sounds. Don't forget to add your .ogg file to a raw resource folder:

public void setupSounds() {
        mSoundPool = new SoundPool(1, AudioManager.STREAM_MUSIC, 0);
        mSoundPoolMap = new HashMap<Integer, Integer>();
        mAudioManager = (AudioManager)this.getSystemService(Context.AUDIO_SERVICE);
        mSoundPoolMap.put(1, mSoundPool.load(this, R.raw.SoundName, 1));
        streamVolume = mAudioManager.getStreamVolume(AudioManager.STREAM_MUSIC);
        streamVolume = streamVolume /  mAudioManager.getStreamMaxVolume(AudioManager.STREAM_MUSIC);
    }

Setup some methods for starting and stopping the audio.

    public void playSoundLoop(){
        streamID = mSoundPool.play(mSoundPoolMap.get(1), streamVolume, streamVolume, 1, -1, 1f);


Note here that this line will continuously loop the audio until the stop button is pressed. If you replace the -1 with a 1, it will play the audio once. If you replace it with any other integer, it will loop the audio that many times. The play method of the soundPool object will return the streamID. Use this integer to stop that particular stream within the stop method we create:

    public void stopSound(){
        mSoundPool.stop(streamID);
    }

Then setup click listeners for the the buttons within the onCreate method.

        startButton.setOnClickListener(new OnClickListener(){

            @Override
            public void onClick(View v) {
                playSound();
            }
    });
}

SQLite LIKE statement

I ran across what turned out to be a simple issue, but it took me a while to finally catch my mistake, so I thought I should post it in case someone else comes across the same issue.

When using SQLite, you can search for words or phrases that are contained within your table. Let's say you have a SQLite database adapter class (which you should in order to keep access to the database limited to one class in you application), you need to create a statement that will query the database using the LIKE statement.

Let's say in your database adapter class that your actual database variable is db. (very clever I know).
 You can create a method to query the database for a record in column 2 that is equal to a given string:

        public Cursor searchTable(String searchPhrase){
            return mDb.query(true, DATABASE_TABLE, new String[]{column1, column2,column3},  column2+ " LIKE '" + searchPhrase+"'", null, null, null, null, null);
        }

This method will return a cursor with every record where column2 is EQUAL to the searchPhrase. This is where I got stuck for a little while, because I want the records that CONTAIN the searchPhrase, regardless of what else is in the field. So you can use (%) as wildcards before and/or after the phrase. Also, don't forget to include a (') around the searchPhrase. SQLite uses the (') to tell the difference between a column name and a value. Since column2 is a column name, we don't need to throw those around it, but the searchPhrase is a value.. so forgetting those will cause some pain as well. So here's the way you search with the wildcards:

        public Cursor searchTable(String searchPhrase){
            return mDb.query(true, DATABASE_TABLE, new String[]{column1, column2,column3},  column2+ " LIKE '%" + searchPhrase+"%'", null, null, null, null, null);
        }

Now this will return a cursor that contains all the records where the searchPhrase is found in column2. So just create an instance of your database adapter, open your database, and use the instance of the adapter to return this cursor. Hope that helps someone else out there! 

Intro

Welcome to my blog. I am currently an Android developer in training. I've written a few apps so far, but nothing spectacular, just enough to get my feet wet. Hopefully by the time you read this, I will have a spectacular app on the market.. but until then, I'll just have to keep learning. I'll use this blog to discuss any issues that I've come across that have been tricky to solve. Even with all the Android resources out there, sometimes it's still tricky to find the information that pertains to your particular issue. Hopefully it will help someone else out that may have come across a similar issue. Happy developing!