Sort Results by a Textual Date (Not a Timestamp)

[legohalflife2ma]

I have (stupidly) made a website where, when people update their accounts, it inputs the date that they last updated their account into the database as a textual date rather than a Unix timestamp. So, I am trying to sort the lastest updated accounts in a MySQL query by their last updated date (c_last_edit), but it sorts them alphabetically from the first letter of the month rather than how it actually should sort them.

Here is an example of the date format I am trying to sort the queries by: April 13, 2009, 6:08 am and, obviously, it sorts the fields by the first letter of the month rather than by the day, month, and year as it should.

Here is what my query looks like, and (as mentioned before) it sorts the c_last_edit field by the first letter of the month instead of how it normally should.

$sql_updated = "SELECT c_id, c_name, c_author, c_last_edit FROM characters WHERE c_status='1' AND c_last_edit!='none' ORDER BY c_last_edit DESC LIMIT 4"; 

Thank you to anyone who gives me good suggestions. I would switch the site to use timestamps for dates instead of using textual dates, but it would take too long considering how many accounts have been created and how many fields I would have to manually edit.

[bdl]

The `c_last_edit` field is a CHAR / VARCHAR type, correct? There is a way to do this, I'll have to find it for you (unless of course someone jumps in). You don't necessarily have to store dates as a UNIX TIMESTAMP value, but it does work in most cases. I recommend using the DATETIME or TIMESTAMP data type for dates (which can be easily converted to a UNIX TIMESTAMP or any other format you like).

[legohalflife2ma]

Yes, it is a VARCHAR field. If I change the field type to DATETIME, won't it totally erase the textual date in the database instead of actually converting it to a timestamp? Thanks for your reply, by the way.

[Old Pedant]

It depends on whether the text values *CAN* be converted to DATETIME.

I would play it safe and do it in steps, if I were you.

First attempt:

SELECT c_last_edit FROM yourtable ORDER BY CAST(c_last_edit AS DATETIME) LIMIT 40

Look at the results. *ARE* those textual dates in the correct order? If so, then obviously the format of the textual dates *can* be converted by MySQL and you are nearly home free.

*IF SO*, then I would STILL do the "fixup" in at least two more stages:

(1) ALTER TABLE yourtable ADD c_last_edit_dt DATETIME;
(2) UPDATE TABLE SET c_last_edit_dt = CAST(c_last_edit AS DATETIME)
(3) [optional] remove the c_last_edit field and from now on use c_last_edit_dt

*IF THE FIRST TEST FAILS*
That is, if there are values in that text field that can't be just CAST to DATETIME, then you have more work to do.

In that case, can you show us 20 or so sample values from that field, as they now are in your DB table?

[bdl]

As Old Pedant mentioned, I wouldn't try to simply change the field type to DATETIME or TIMESTAMP, I'm saying you should convert the field to that eventually, once you get the formatting straightened out. Using CAST should be a helpful tool to get you started. If I can find that old forum post (on another forum) that spells out exactly how to do this, I'll post back.

[Old Pedant]

I just noticed a phrase that Lego used:

it sorts them alphabetically from the first letter of the month

So there isn't a hope in the world that MySQL will be able to do that CAST as I suggested.

You will *HAVE* to do the conversion using either a pretty complex SQL query or using PHP or other server side code.

Again, we need to see the format that the dates are in, now. That is, are they
January 03, 2009
or
Jan 3, 2009
or
03 Jan 2009
or
3 Jan 2009
or
what???

[legohalflife2ma]

Thanks for your help guys.

As I mentioned before, an example of the date format that is in the c_last_edit field in the DB would be this:
April 13, 2009, 6:08 am

And, as you already know, it's sorting the dates alphabetically because it is a VARCHAR field. Should I find another way to do this?

[Old Pedant]

So I think we could write a MySQL function to convert all those text dates to DATETIME. But it might be easier to do in your server language--PHP or JSP or whatever.

Probably be easiest to write a stored procedure if you opt to do it all in MySQL. Maybe.

In any case, I'd still go with the 3 stage process I suggested: add a new field, do the convert into the new field, optionally get rid of the original field.

You know, if you had access to an ASP web server that could reach this MySQL DB, it would dirt simple to do. That's because VBScript, the language of ASP, is capable of reading that exact textual form and converting it to an internal DATETIME value that could then be spit back out to the MySQL DB.

Take about 2 minutes to write the code.

I don't know PHP much, at all, so I dunno if there is a similarly easy-to-use text to datetime converter in PHP.

[Old Pedant]

And you did mention that format and I'm blind. Sorry.

[Old Pedant]

IYAMADORK! Problem is, I learned MySQL back in 3.23 days. And I tend to forget about a lot of the stuff added in version 4 and 5.

Look here:
http://dev.mysql.com/doc/refman/5.1/...on_str-to-date

Sheesh.

So:

(1) ALTER TABLE yourtable ADD c_last_edit_dt DATETIME;

(2) UPDATE TABLE SET c_last_edit_dt = [B]STR_TO_DATE(c_last_edit, '[COLOR="Red"]%M %e, %Y, %h:%i %p[/COLOR]')[/B]

(3) [optional] remove the c_last_edit field and from now on use c_last_edit_dt

.

*sigh* Give it a shot.

[bdl]

IYAMADORK! Problem is, I learned MySQL back in 3.23 days. And I tend to forget about a lot of the stuff added in version 4 and 5.

Same here. Nice solution, hope it works. I still can't find the custom SQL statement I was talking about, but trust me, it's not a one-liner like that. Hopefully it's been made obsolete.

[legohalflife2ma]

Ah, thanks a lot guys! I'm going to try out that code that Old Pedant posted and see if it works. It looks like exactly what I need. By the way, I have one question about that code Old Pedant posted: there are multiple rows in this database table, so c_last_edit is a column storing values for multiple rows. Will running the MySQL codes that you posted convert the c_last_edit field for every row in the database table, or will it only convert it for one row. I would just like to know whether or not it can convert all of the values of c_last_edit for all of the rows without me having to do it manually for each row. If not, I can certainly do it manually - I'm just wondering.

Thanks again!

[Old Pedant]

Will running the MySQL codes that you posted convert the c_last_edit field for every row in the database table, or will it only convert it for one row.

Of course for all rows. *ANY* UPDATE statement works that same as a SELECT: It chooses all rows...*unless* you limit it with a WHERE clause.

UPDATE *can* be dangerous.

UPDATE table SET name = 'Jones'

You just changed every name field in every record in that table to 'Jones'.

UPDATE table SET name = 'Jones' WHERE name = 'J.O.N.E.S.'

*NOW* you only changed the one record where some smartass entered his name in a weird way.

But you can use WHERE to limit the UPDATE to any number of records--from zero to as many as there are in the table. Just exactly like SELECT.