Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 13 of 13
  1. #1
    New Coder
    Join Date
    Mar 2008
    Location
    USA
    Posts
    40
    Thanks
    3
    Thanked 1 Time in 1 Post

    Sort Results by a Textual Date (Not a Timestamp)

    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.

    PHP Code:
    $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.

  • #2
    bdl
    bdl is offline
    Regular Coder
    Join Date
    Apr 2007
    Location
    Camarillo, CA US
    Posts
    590
    Thanks
    4
    Thanked 83 Times in 82 Posts
    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).

  • #3
    New Coder
    Join Date
    Mar 2008
    Location
    USA
    Posts
    40
    Thanks
    3
    Thanked 1 Time in 1 Post
    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.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    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:
    Code:
    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:
    Code:
    (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?

  • Users who have thanked Old Pedant for this post:

    bdl (04-13-2009)

  • #5
    bdl
    bdl is offline
    Regular Coder
    Join Date
    Apr 2007
    Location
    Camarillo, CA US
    Posts
    590
    Thanks
    4
    Thanked 83 Times in 82 Posts
    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.

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    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???

  • #7
    New Coder
    Join Date
    Mar 2008
    Location
    USA
    Posts
    40
    Thanks
    3
    Thanked 1 Time in 1 Post
    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?

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    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.

  • #9
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    And you did mention that format and I'm blind. Sorry.

  • #10
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    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:
    Code:
    (1) ALTER TABLE yourtable ADD c_last_edit_dt DATETIME;
    
    (2) UPDATE TABLE SET c_last_edit_dt = STR_TO_DATE(c_last_edit, '%M %e, %Y, %h:%i %p')
    
    (3) [optional] remove the c_last_edit field and from now on use c_last_edit_dt
    
    .
    *sigh* Give it a shot.

  • Users who have thanked Old Pedant for this post:

    legohalflife2ma (04-20-2009)

  • #11
    bdl
    bdl is offline
    Regular Coder
    Join Date
    Apr 2007
    Location
    Camarillo, CA US
    Posts
    590
    Thanks
    4
    Thanked 83 Times in 82 Posts
    Quote Originally Posted by Old Pedant View Post
    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.

  • #12
    New Coder
    Join Date
    Mar 2008
    Location
    USA
    Posts
    40
    Thanks
    3
    Thanked 1 Time in 1 Post
    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!

  • #13
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    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.
    Code:
    UPDATE table SET name = 'Jones'
    You just changed every name field in every record in that table to 'Jones'.
    Code:
    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.


  •  

    Tags for this Thread

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •