Skip site navigation (1) Skip section navigation (2)

Re: Ordering problem with varchar (DESC)

From: "Brandon Aiken" <BAiken(at)winemantech(dot)com>
To: "Alexandre Leclerc" <aleclerc(at)ipso(dot)ca>,<pgsql-general(at)postgresql(dot)org>
Subject: Re: Ordering problem with varchar (DESC)
Date: 2007-01-31 18:33:00
Message-ID: F8E84F0F56445B4CB39E019EF67DACBA44F8DA@exchsrvr.winemantech.com (view raw or flat)
Thread:
Lists: pgsql-general
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.

"SELECT * from t1;" is not an ordered query and any consistency of order
is coincidental (typically it comes out in the same order it went in,
but there's no guarantee of 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.


--
Brandon Aiken
CS/IT Systems Engineer
-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Alexandre
Leclerc
Sent: Wednesday, January 31, 2007 10:46 AM
To: pgsql-general(at)postgresql(dot)org
Subject: [GENERAL] Ordering problem with varchar (DESC)

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           d4
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?

Best regards.

-- 
Alexandre Leclerc

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org/

In response to

Responses

pgsql-general by date

Next:From: Jeff DavisDate: 2007-01-31 18:37:34
Subject: Re: Any Plans for cross database queries on the sameserver?
Previous:From: Erik JonesDate: 2007-01-31 18:06:21
Subject: Re: Ordering problem with varchar (DESC)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group