SQLite - Left-pad zeros in returned Text field

Question

I have a text field in my SQLite database that stores a Time value, but for unrelated reasons I can't change the data type to TIME.

The values are stored in HH:MM format, and I'm having trouble trying to sort results by time because the values below '10:00' are missing a leading zero. I would prefer not to store the data with leading zero for the same unrelated reasons.

I'd like to add something to the Query that would pad the missing character if necessary, causing the results to read '08:30' when collected. I've been searching through the command and function lexicon though and I'm not finding what I need.

Is there a simple way to do this inside a query?

Thanks

Answer 1

I think this would work:

select your_col, case when length(your_col) < 5 
                      then '0' || your_col else your_col end from your_table

Demo using Python

>>> conn.execute('''select c, case when length(c) < 5 
                                   then '0' || c else c end from t''').fetchall()
[(u'10:00', u'10:00'), (u'8:00', u'08:00')]