Q: Order By character varying time strings? I'm querying a table and I'm trying to Order By a "time" column that has the time formatted as "1 - 2 p.m.", "7 - 8 a.m.", "11 a.m. - 12 p.m.", etc etc.  I'm not allowed to alter this column, but I can't think of a good way to query it so that I can order it properly by asc or desc. I tried looking for the position or "a.m." or "p.m." and using the PostgreSLQ substring method, but I'm still having trouble.

A: Add a sortby column to your query
select blah
, case when timefield = "earlier than 1am" then 1
when timefield = "1-2 am" then 2
etc
else 20 end sortby
etc
order by sortby


A: I think you can do it this way:
order by to_timestamp(time, 'HH a.m.');

Here is an example:
select time, to_timestamp(time, 'HH a.m.')
from (select '1 p.m. - 2 p.m.' as time union all
      select '9 a.m. - 10 a.m.' as time union all
      select '11 a.m. - 12 p.m.' as time
     ) t
order by to_timestamp(time, 'HH a.m.');

