Q:

SQLite3 Time Problem 11 getting selected before 2

I have a SQLite database that has a time field set as a text type. And it contains values like 11:30 PM, 2:30 PM, etc and I need to select time and date by date & time ASC. I use the following query SELECT * FROM schedule ORDER BY date ASC, time ASC LIMIT 50
But the problem is I get values such as 11:30PM before 2:30PM
How can I avoid this?
Thanks!

A:

the problem is that you are storing in human readable format a value that should be meaningful for a machine.  Since sqlite doesn't have a native time type, you'll have to make do with the next nearest approximation, which is a numeric type.  You could store both date and time as a single number, for instance as seconds since January 1st, 1970, and then format those values for presentation to users at the last moment.

