Solved

Mysql Query Question - ordering by date when date is stored in VARCHAR column

Posted on 2009-07-16
11
432 Views
Last Modified: 2012-05-07
I'm trying to update a MySQL query so that it displays the records in reverse chronological order based on the "entry_date" value -- but the dates are stored in a VARCHAR column, which is making the records display in the wrong order.

How can I update the following MySQL query so that it displays the records in reverse chronological order based on the date in this situation?

SELECT ID,title,location,entry_date, article_text FROM newsevents WHERE entry_type!='EVENT' AND archive='0' AND publish_main='1' ORDER BY entry_date DESC

Thanks,
- Yvan
0
Comment
Question by:egoselfaxis
  • 4
  • 3
  • 3
  • +1
11 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24868719
what is the format of the date value in the varchar column?
why is that a varchar field, anyhow, and not a date field!

the varchar format would need to be yyyy-mm-dd to be sorted correctly.
if it's any other format, use the function str_to_date() to get the varchar into a date value for the order by
ORDER BY str_to_date(entry_date, 'format goes here') DESC

Open in new window

0
 
LVL 14

Expert Comment

by:profya
ID: 24868733
SELECT ID,title,location,entry_date, article_text FROM newsevents WHERE entry_type!='EVENT' AND archive='0' AND publish_main='1' ORDER BY CATS(entry_date AS Date) DESC
0
 
LVL 14

Accepted Solution

by:
profya earned 500 total points
ID: 24868740
SELECT ID,title,location,entry_date, article_text FROM newsevents WHERE entry_type!='EVENT' AND archive='0' AND publish_main='1' ORDER BY CAST(entry_date AS Date) DESC
0
 
LVL 14

Expert Comment

by:profya
ID: 24868746
@angelIII: It took 0 time to respond! :)
0
 
LVL 34

Expert Comment

by:gr8gonzo
ID: 24869322
Just a note on performance - using "ORDER BY" in MySQL in combination with some function is USUALLY not a very optimized query. If the query is only running on a few rows (5, 10, 20, etc), then it's probably not a big deal and you won't notice the difference. Try it on a result with 10,000 rows, and you'll see a noticeable difference. Try it on a result with 500,000 rows, and you'll immediately be looking for a different way to do this.

So if you expect this table and result set to be large at any given time, I'd recommend just fixing the problem now. I personally convert dates like this to UNIX timestamp and store them in an INT field. At that point, you can run a normal ORDER BY on that field without any special functions or parameters, and it'll sort all the way down to the second.

No matter what path you choose, though, try to avoid having large result sets at all costs for maximum performance on your database. (Using a tool like MySQLTuner can also help you determine better ways of optimizing your database settings.)
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:egoselfaxis
ID: 24871418
Thanks!

- yg
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24871529
huh? I am surprised

because CAST(entry_date AS Date)  shall only work if entry_date is formatted yyyy-mm-dd, in which case the order by should work anyhow.
please clarify
0
 

Author Comment

by:egoselfaxis
ID: 24871546
The dates are formatted as yyyy-mm-dd
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24871600
then, I take this from my first comment:
>the varchar format would need to be yyyy-mm-dd to be sorted correctly.

as this is the case, your ORDER BY should work correctly already without the CAST() ...
in other words, you original query is ok as is:

SELECT ID,title,location,entry_date, article_text FROM newsevents WHERE entry_type!='EVENT' AND archive='0' AND publish_main='1' ORDER BY entry_date DESC

0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24871603
aka: prove me wrong with data samples...
0
 

Author Comment

by:egoselfaxis
ID: 24871789
You might be right .. but the problem I was having was actually resulting from some improperly formatted dates in the database.  

- yg
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to count occurrences of each item in an array.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now