Re: [GENERAL] Ordering problem with varchar (DESC)
am Wed, dem 31.01.2007, um 10:46:17 -0500 mailte Alexandre Leclerc folgendes: Hi all, We have a column (varchar) that has plain text time and it is indexed. How can I fix that so that the result is exactly like the first one but perfectly reversed in it's order? Use the right data-typ for your data, in this case TIMESTAMP. Then you can order this data likewise reverse and get a performance boost by the way... Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Ordering problem with varchar (DESC)
Alexandre Leclerc wrote: SELECT * from t1 ORDER BY date, time DESC; date (date type) time (varchar) data 2007-01-30 9h30 d2 2007-01-3017h20 d5 2007-01-3013h45 d4 2007-01-3012h00 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? I believe ORDER BY date, replace(time,'h',':')::time DESC would work. Or just use directly a time datatype instead of varchar, or only one datetime column instead of the two, and order by that column. Or use a leading '0' instead of a leading space when the hour is less than 10... Regards, -- Daniel PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] 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-3012h00 d3 2007-01-3013h45 d4 2007-01-3017h20 d5 SELECT * from t1 ORDER BY date, time DESC; date (date type) time (varchar) data 2007-01-30 9h30 d2 2007-01-3017h20 d5 2007-01-3013h45 2007-01-3012h00 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; ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Ordering problem with varchar (DESC)
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-3012h00 d3 2007-01-3013h45 d4 2007-01-3017h20 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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Alexandre Leclerc Sent: Wednesday, January 31, 2007 10:46 AM To: pgsql-general@postgresql.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-3012h00 d3 2007-01-3013h45 d4 2007-01-3017h20 d5 SELECT * from t1 ORDER BY date, time DESC; date (date type) time (varchar) data 2007-01-30 9h30 d2 2007-01-3017h20 d5 2007-01-3013h45 d4 2007-01-3012h00 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/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Ordering problem with varchar (DESC)
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-3012h00 d3 2007-01-3013h45 d4 2007-01-3017h20 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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Ordering problem with varchar (DESC)
Daniel Verite a écrit : Alexandre Leclerc wrote: SELECT * from t1 ORDER BY date, time DESC; date (date type) time (varchar) data 2007-01-30 9h30 d2 2007-01-3017h20 d5 2007-01-3013h45 d4 2007-01-3012h00 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? I believe ORDER BY date, replace(time,'h',':')::time DESC would work. That worked perfectly. Unfortunately I can't control the sql query in the situation I am in. But... I know this is the white space that does the issue. Or just use directly a time datatype instead of varchar, or only one datetime column instead of the two, and order by that column. Or use a leading '0' instead of a leading space when the hour is less than 10... Yep, this is the only solution that will work for that situation right now: inserting a leading '0' instead of a white space. Thank you for your help. Best regards. -- Alexandre Leclerc ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
