Re: Ordering problem with varchar (DESC)

Alexandre Leclerc wrote:
> Hi all,
>
> We have a column (varchar) that has plain text time and it is indexed.
> When I do a query with the index, all the data is in the right order,
> but when I user ORDER BY .. DESC, the order is messed up. Example:
>
> By index 1: (date, time, data)
> SELECT * from t1;
> date (date type) time (varchar) data
> 2007-01-17 8h40 d1
> 2007-01-30 9h30 d2
> 2007-01-30 12h00 d3
> 2007-01-30 13h45 d4
> 2007-01-30 17h20 d5
>
> SELECT * from t1 ORDER BY date, time DESC;
> date (date type) time (varchar) data
> 2007-01-30 9h30 d2
> 2007-01-30 17h20 d5
> 2007-01-30 13h45
> 2007-01-30 12h00 d3
> 2007-01-17 8h40 d1
>
> I don't know why, this is like if the 'time' varchar was trimmed then
> used for the ordering.
>
> How can I fix that so that the result is exactly like the first one but
> perfectly reversed in it's order?
>
The ordering of the result from the first query is incidental of the
order the rows are returned by the index, not of the actual values
returned. As to the second query, it's ordering correctly as the values
in your time field are sorted as strings. In the ideal scenario you'd
change the datatype of your time field. If for some reason that's not
possible try something along these lines might work:

SELECT *
FROM t1
ORDER BY (date || ' ' || replace(time, 'h', ':'))::timestamp;