I had a question about category blog layout ordening, I have a lot of articles from november, but last I added a lot of articles trough a API from august. The import was all good and nothing was wrong with it, but when I set my blog layout to "Newest first" It takes te latest date of november and august and sorts them

I get:

30 november 30 august 23 november 22 august

But I want to get:

30 november 23 november 30 august 22 august

Does anyone know how I will get this?

  • Most likely, you might be sorting them based on another date, or maybe they are more sorting properties involved (could be categories sorting). Can you test what's the case when you sort the articles in the backend? Test it with all the available dates options. – FFrewin Nov 30 '17 at 19:55

I cannot be too specific without knowing the exact database table structure and data, but it seems that your date values are being stored and processed as:

dd [delimiter] mm [delimiter] yyyy (delimiters may logically be / or -)

For instance, if you are storing the date as a VARCHAR and the string looks like 22/08/17, 30/08/17,23/11/17,30/11/17 then it is easy to see that mysql is sorting your rows by reading the dates as "strings" (from left to right) versus "dates" (y, m, then d logic).

There will be many ways to solve this issue, but the best advice that I can give is to correct the data storaged (because date values should be stored as date type) and adjust the codes that access it. This would mean setting the date column to DATE type and feeding it values in the format of yyyy-mm-dd. Then ordering will be simplified permanently, but you will need to adjust the date values in the SELECT clause to your desired format.

If you cannot or do not wish to correct the data storage, you can put a bandage on the problem by modifying all of the queries that need to order by date.

If your date column is named date and your values use / as delimiters, then you can use this ORDER BY clause to sort by year, then month, then day from newest to oldest:

ORDER BY STR_TO_DATE(`date`, '%d/%m/%Y') DESC

Here is an sqlfiddle to demonstrate what is happening with the STR_TO_DATE() call.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.