Re: Ordering problem with varchar (DESC)

[Alexandre Leclerc <aleclerc(at)ipso(dot)ca>]

Brandon Aiken a écrit :
> As others have said, VARCHAR is the incorrect data type to be using
> here.  You should either be using INTERVAL or TIMESTAMP depending on
> what you want.  You can even combine date and time into a single
> TIMESTAMP field.  Only use VARCHAR when no other data type will do.

I dearly would like to do that, but it is impossible (because of the
software/technology that uses the database). I would have use a
TIMESTAMP for that.

> Try "SELECT * from t1 ORDER BY date, time;", and I suspect you will get:
> date (date type)  time (varchar)  data
> 2007-01-17         8h40           d1
> 2007-01-30        12h00           d3
> 2007-01-30        13h45           d4
> 2007-01-30        17h20           d5
> 2007-01-30         9h30           d2
> 
> To use your current schema, you need to zero-fill your hours, so 9h30
> needs to be 09h30 and so forth.

Exactly. This is sorted that way. This is what I'll do, inserting a 0.

Best regards.

-- 
Alexandre Leclerc