Question 215083 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Order By not ordering correctly by dates in my query

I’m trying to order this results by date in descending order with this query

SELECT DISTINCT d.id,
to_char(d.creation_date::date, 'dd/mm/yyyy') AS delivered_date
FROM delivery_kits_equipment_of_visit d
WHERE d.deleted IS NOT TRUE
AND d.deleted = 'FALSE'
ORDER BY to_char(d.creation_date::date, 'dd/mm/yyyy') DESC
LIMIT 10

However tho this is my result:

id delivered_date
193 "31/01/2023"
196 "31/01/2023"
195 "31/01/2023"
194 "31/01/2023"
58 "30/12/2022"
44 "30/12/2022"
70 "30/12/2022"
45 "30/12/2022"
192 "30/01/2023"
197 "30/01/2023"

You can see some dates from 2022 in between and then then back to 2023. Anyway I can fix that?

Tags:

2

Answers


  1. 0

    Well assuming the creation_date column already be a bona-fide date column, you should be able to just sort directly using it:

    ORDER BY d.creation_date DESC

    If creation_date happens to be a text column, then it gets more tricky. Most likely, the easiest solution would be to convert the text to a date, and then sort on it. But as a matter practice, it is best to not store dates as text in the first place.

    Login or Signup to reply.
  2. 0

    It is ordered correctly. The order results you are getting is from ordering by a character string (result of to_char() function). What is the data type of creation_date? It should be date or a timestamp. If that is the case then just order by d.creation_date, but if the type is text, which I suspect, then you need something like order by d.creation_date::date.

    Regarding your comment that you really don’t have a say in how tables and database structures are build. I do not doubt that, but you can and should propose a better way especially when be able to factually back that contention up. You maynot get it now, but it is likely to pay off in the long run.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search