If I could save time in a database…

OK, it’s an awful song reference, but it was storing time has always been tricky. Since this was my first time handling time in an iOS project, I thought I’d discuss how I decided which method to use. This might seem straight forward to many of you, but it was new to me.

The first option is to use the DATETIME timestamp in SQLite. This has the immediate advantage that it does not even require code if you make it a required field. And it’s easy to sort on as part of your SELECT statement. This was my first choice, and I used it in the first version of my current app.

Be aware, the time is essentially what I know as universal time. It’s the actually the time from noon in Greenwich on November 24, 4714 B.C according to the SQLIte website. This is great if you want to remember exactly when something happened and it will change when the phone changes time zone.

This turned out to be an issue for me. My app stores when a student practices a task. And even when the phone changes time zones, my app needs to remember that the student practiced at 2pm last Thursday. So I had to change it.

NSDate is the next logical choice. If I was using CoreData, it looks like a logical choice. But it is not directly supported in SQLite, so I would have to convert it, probably into a double with the number of seconds since the reference date. It would work, but it would have the same problem with changing time zones.

So I finally ended up with storing time as a string, formatted in the default of the locale. This has a huge advantage for display, obviously. And I don’t have to worry about the display time changing when the phone moves time zone. NSDateFormatter makes it very easy to take the current time and convert it into a display string. And by using one of the built in formats, I used NSDateFormatterShortStyle, phones set to anything other than US English will automatically print date and time in the appropriate local format. I don’t even have to think about where the month goes in European dates.

Of course, this is not perfect, because now the sorting problem is much worse. If your phone is set for 12 hour time format, am/pm times do not sort correctly. And US printed dates don’t sort right either. So now to fix this, I have to us NSDateFormatter in reverse to create NSDate objects as I read in the database, and sort by them.

If you get the idea that I never found the perfect format, you are right. If you know of a better way to handle this, let me know. If you hurry, I might even be able to drop it in the next update.


Leave a comment

Filed under Uncategorized

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s