Joomla Stack Exchange is a question and answer site for Joomla! administrators, users, developers and designers. It only takes a minute to sign up.
Sign up to join this community
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?
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.
